Find invalid objects

The duties of the database administrator include many different tasks, which are mainly aimed at maintaining the health and integrity of the database. And if the integrity of the data can be checked through the command CHECKDB , then with the search for invalid objects in the scheme is not so smooth.

If you draw analogies with Oracle , then in SQL Server you cannot just get a list of invalid objects:

SELECT owner, object_type, object_name FROM all_objects WHERE status = 'INVALID' 

In most situations, it is possible to find out that a scripted object is invalid, only when it is executed. Of course, this situation may not suit everyone, so I suggest writing a script to search for invalid objects in the database.

 SELECT obj_name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name) , obj_type = o.type_desc , d.referenced_database_name , d.referenced_schema_name , d.referenced_entity_name FROM sys.sql_expression_dependencies d JOIN sys.objects o ON d.referencing_id = o.[object_id] WHERE d.is_ambiguous = 0 AND d.referenced_id IS NULL --         AND d.referenced_server_name IS NULL --    Linked server AND CASE d.referenced_class --    WHEN 1 --  THEN OBJECT_ID( ISNULL(QUOTENAME(d.referenced_database_name), DB_NAME()) + '.' + ISNULL(QUOTENAME(d.referenced_schema_name), SCHEMA_NAME()) + '.' + QUOTENAME(d.referenced_entity_name)) WHEN 6 --    THEN TYPE_ID( ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + '.' + d.referenced_entity_name) WHEN 10 --  XML  THEN ( SELECT 1 FROM sys.xml_schema_collections x WHERE x.name = d.referenced_entity_name AND x.[schema_id] = ISNULL(SCHEMA_ID(d.referenced_schema_name), SCHEMA_ID()) ) END IS NULL 

For the initial diagnosis, this request has rescued me more than once. However, it is not without flaws. Perhaps the most important of them is that this query will not show objects where invalid columns or parameters are encountered:
')

 CREATE VIEW dbo.vw_View AS SELECT ID = 1 GO CREATE PROCEDURE dbo.usp_Procedure AS BEGIN SELECT ID FROM dbo.vw_View END GO ALTER VIEW dbo.vw_View AS SELECT New_ID = 1 GO 

When executing the stored procedure, we get an error:

 Msg 207, Level 16, State 1, Procedure usp_Procedure, Line 6 Invalid column name 'ID'. 

In addition, on SQL Server 2005 , the above query will not work. As there, other system views are used to find dependencies, which, moreover, can only show valid dependencies for an object.

For these reasons, as the main working option, the proposed query is not strongly advisable to use. But all is not lost, because in the arsenal of SQL Server there is a system procedure for enforcing the updating of script object dependencies - sp_refreshsqlmodule .

If the scripted object contains any invalid object, this procedure will generate an error. The most obvious option is to call this procedure for each object in the cursor, and if it ends with an error, then mark the object as invalid.

In addition, do not forget that the scripted objects may not have dependencies. Or they may not initially contain non-valid objects, for example, views created with the SCHEMABINDING option or scalar functions that are used in DEFAULT or CHECK constraints and in COMPUTED columns. For such objects, it is impractical to check for validity — this is controlled by SQL Server .

The proposed script to search for invalid objects, taking into account the specifics of SQL Server 2008/2012/2014 :

 SET NOCOUNT ON; IF OBJECT_ID('tempdb.dbo.#objects') IS NOT NULL DROP TABLE #objects CREATE TABLE #objects ( obj_id INT PRIMARY KEY , obj_name NVARCHAR(261) , err_message NVARCHAR(2048) NOT NULL , obj_type CHAR(2) NOT NULL ) INSERT INTO #objects (obj_id, obj_name, err_message, obj_type) SELECT t.referencing_id , obj_name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name) , 'Invalid object name ''' + t.obj_name + '''' , o.[type] FROM ( SELECT d.referencing_id , obj_name = MAX(COALESCE(d.referenced_database_name + '.', '') + COALESCE(d.referenced_schema_name + '.', '') + d.referenced_entity_name) FROM sys.sql_expression_dependencies d WHERE d.is_ambiguous = 0 AND d.referenced_id IS NULL --         AND d.referenced_server_name IS NULL --    Linked server AND CASE d.referenced_class --    WHEN 1 --  THEN OBJECT_ID( ISNULL(QUOTENAME(d.referenced_database_name), DB_NAME()) + '.' + ISNULL(QUOTENAME(d.referenced_schema_name), SCHEMA_NAME()) + '.' + QUOTENAME(d.referenced_entity_name)) WHEN 6 --    THEN TYPE_ID( ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + '.' + d.referenced_entity_name) WHEN 10 --  XML  THEN ( SELECT 1 FROM sys.xml_schema_collections x WHERE x.name = d.referenced_entity_name AND x.[schema_id] = ISNULL(SCHEMA_ID(d.referenced_schema_name), SCHEMA_ID()) ) END IS NULL GROUP BY d.referencing_id ) t JOIN sys.objects o ON t.referencing_id = o.[object_id] WHERE LEN(t.obj_name) > 4 --     ,    DECLARE @obj_id INT , @obj_name NVARCHAR(261) , @obj_type CHAR(2) DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR SELECT sm.[object_id] , QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name) , o.[type] FROM sys.sql_modules sm JOIN sys.objects o ON sm.[object_id] = o.[object_id] LEFT JOIN ( SELECT s.referenced_id FROM sys.sql_expression_dependencies s JOIN sys.objects o ON o.object_id = s.referencing_id WHERE s.is_ambiguous = 0 AND s.referenced_server_name IS NULL AND o.[type] IN ('C', 'D', 'U') GROUP BY s.referenced_id ) sed ON sed.referenced_id = sm.[object_id] WHERE sm.is_schema_bound = 0 --     WITH SCHEMABINDING AND sm.[object_id] NOT IN (SELECT o2.obj_id FROM #objects o2) --       AND OBJECTPROPERTY(sm.[object_id], 'IsEncrypted') = 0 AND ( o.[type] IN ('IF', 'TF', 'V', 'TR') --   , sp_refreshsqlmodule      (Bug #656863) --OR o.[type] = 'P' OR ( o.[type] = 'FN' AND --   ,    DEFAULT/CHECK    COMPUTED  sed.referenced_id IS NULL ) ) OPEN cur FETCH NEXT FROM cur INTO @obj_id, @obj_name, @obj_type WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY BEGIN TRANSACTION EXEC sys.sp_refreshsqlmodule @name = @obj_name, @namespace = N'OBJECT' COMMIT TRANSACTION END TRY BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK TRANSACTION INSERT INTO #objects (obj_id, obj_name, err_message, obj_type) SELECT @obj_id, @obj_name, ERROR_MESSAGE(), @obj_type END CATCH FETCH NEXT FROM cur INTO @obj_id, @obj_name, @obj_type END CLOSE cur DEALLOCATE cur SELECT obj_name, err_message, obj_type FROM #objects 

In SQL Server 2005, the same script would be:

 SET NOCOUNT ON; IF OBJECT_ID('tempdb.dbo.#objects') IS NOT NULL DROP TABLE #objects CREATE TABLE #objects ( obj_name NVARCHAR(261) , err_message NVARCHAR(2048) NOT NULL , obj_type CHAR(2) NOT NULL ) DECLARE @obj_name NVARCHAR(261) , @obj_type CHAR(2) DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name) , o.[type] FROM sys.sql_modules sm JOIN sys.objects o ON sm.[object_id] = o.[object_id] LEFT JOIN ( SELECT s.referenced_major_id FROM sys.sql_dependencies s JOIN sys.objects o ON o.object_id = s.[object_id] WHERE o.[type] IN ('C', 'D', 'U') GROUP BY s.referenced_major_id ) sed ON sed.referenced_major_id = sm.[object_id] WHERE sm.is_schema_bound = 0 --     WITH SCHEMABINDING AND OBJECTPROPERTY(sm.[object_id], 'IsEncrypted') = 0 AND ( o.[type] IN ('IF', 'TF', 'V', 'TR') --   , sp_refreshsqlmodule      (Bug #656863) --OR o.[type] = 'P' OR ( o.[type] = 'FN' AND --   ,    DEFAULT/CHECK    COMPUTED  sed.referenced_major_id IS NULL ) ) OPEN cur FETCH NEXT FROM cur INTO @obj_name, @obj_type WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY BEGIN TRANSACTION EXEC sys.sp_refreshsqlmodule @name = @obj_name, @namespace = N'OBJECT' COMMIT TRANSACTION END TRY BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK TRANSACTION INSERT INTO #objects (obj_name, err_message, obj_type) SELECT @obj_name, ERROR_MESSAGE(), @obj_type END CATCH FETCH NEXT FROM cur INTO @obj_name, @obj_type END CLOSE cur DEALLOCATE cur SELECT obj_name, err_message, obj_type FROM #objects 

For example, I will give the results of running the script on a test base:

 obj_name err_message obj_type --------------------------------- ------------------------------------------------------------------------------- -------- [dbo].[vw_EmployeePersonalInfo] An insufficient number of arguments were supplied for 'dbo.GetEmployee' V [dbo].[udf_GetPercent] Invalid column name 'Code'. FN [dbo].[trg_AIU_Sync] Invalid column name 'DateOut'. P [dbo].[trg_IOU_SalaryEmployee] Invalid object name 'dbo.tbl_SalaryEmployee'. TR [dbo].[trg_IU_ReturnDetail] The object 'dbo.ReturnDetail' does not exist or is invalid for this operation. TR [dbo].[ReportProduct] Invalid object name 'dbo.ProductDetail'. IF 

Now a few words about synonyms. When they are created, SQL Server does not validate the name of the object. In practice, it turns out that a synonym can be created on a non-existent object. To find all invalid synonyms, you can use the following simple query:

 SELECT QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + QUOTENAME(s.name) FROM sys.synonyms s WHERE PARSENAME(s.base_object_name, 4) IS NULL --    Linked server AND OBJECT_ID(s.base_object_name) IS NULL 

If the need arises, you can add a check for invalid synonyms to existing requests as follows:

 ... SELECT obj_name, err_message, obj_type FROM #objects UNION ALL SELECT QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + QUOTENAME(s.name) , 'Invalid object name ''' + s.base_object_name + '''' , s.[type] FROM sys.synonyms s WHERE PARSENAME(s.base_object_name, 4) IS NULL AND OBJECT_ID(s.base_object_name) IS NULL 

As you can see, skillfully using information from metadata you can implement the functionality that is not enough for solving everyday tasks in database maintenance and monitoring.

If you want to share this article with an English-speaking audience:
Find invalid objects in your databases