Find Invalid Objects in Your Databases

DBA has a number of duties that are primarily targeted at supporting of database performance capabilities and data consistency. The administrator can use the CHECKDB command to easily verify the data consistency; however, in case they need to find an invalid object in a database schema, some difficulties may occur.

ORACLE for instance, allows you to get a list of invalid objects:

SELECT owner, object_type, object_name
FROM all_objects
WHERE status = ‘INVALID’

SQL Server doesn’t allow to do that directly. In most cases, you need to execute a script to see that an object is invalid. This is very inconvenient…
So let’s create a script that will search invalid objects:

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 — ignore objects from Linked server
AND CASE d.referenced_class — if does not exist
WHEN 1 — object
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 — or user datatype
THEN TYPE_ID(
ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + ‘.’ + d.referenced_entity_name)
WHEN 10 — or XML schema
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

The script is useful for the primary analysis. However, there are some gaps in it. The main problem is that the script does not show objects with invalid columns or parameters.

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

We will get an error while executing the storage procedure:

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

Moreover, the script will not work on SQL Server 2005. So, we can’t use the provided script as the primary one.

However, SQL Server contains the sp_refreshsqlmodule system procedure. The procedure updates the metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects.

Thus, the sp_refreshsqlmodule procedure generates an error if an object contains invalid columns or properties. The procedure can be called inside a cursor for each object. If no invalid objects exist, then the procedure completes with no errors.

It is important to remember, however, that script objects may have no dependencies or can contain no invalid objects initially. There is no expediency to verify such objects. SQL Server takes care of that.

The following script can be used for searching invalid objects:

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(1000)
, err_message NVARCHAR(3000) 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 — ignore objects from Linked server
AND CASE d.referenced_class — if does not exist
WHEN 1 — object
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 — or user datatype
THEN TYPE_ID(
ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + ‘.’ + d.referenced_entity_name)
WHEN 10 — or XML schema
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 — hide valid aliases
DECLARE
@obj_id INT
, @obj_name NVARCHAR(1000)
, @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 — objects without 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’) — OR o.[type] = ‘P’ /* Microsoft Connect #656863 */
OR (
o.[type] = ‘FN’
AND
 — ignore scalar functions, which are used in DEFAULT/CHECK constraints and COMPUTED columns
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

The same script for SQL Server 2005:

SET NOCOUNT ON;
IF OBJECT_ID(‘tempdb.dbo.#objects’) IS NOT NULL
 DROP TABLE #objects
CREATE TABLE #objects (
 obj_name NVARCHAR(1000)
 , err_message NVARCHAR(3000) NOT NULL
 , obj_type CHAR(2) NOT NULL
)
DECLARE
 @obj_name NVARCHAR(1000)
 , @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
 AND OBJECTPROPERTY(sm.[object_id], ‘IsEncrypted’) = 0
 AND (
 o.[type] IN (‘IF’, ‘TF’, ‘V’, ‘TR’)
 OR (
 o.[type] = ‘FN’
 AND
 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

Script execution results are as follows (for a test database):

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

SQL Server doesn’t check an object’s name while creating a synonym. So, a synonym can be created for a non-existing object.

To find all invalid synonyms, the following script can be used:

SELECT QUOTENAME(SCHEMA_NAME(s.[schema_id])) + ‘.’ + QUOTENAME(s.name)
FROM sys.synonyms s
WHERE PARSENAME(s.base_object_name, 4) IS NULL — ignore objects from Linked server
AND OBJECT_ID(s.base_object_name) IS NULL

If there is need to add this check to a current script:

…
SELECT obj_name, err_message, obj_type
FROM #objects
UNION ALL
SELECT
QUOTENAME(SCHEMA_NAME(s.[schema_id])) + ‘.’ + QUOTENAME(s.name) COLLATE DATABASE_DEFAULT
, ‘Invalid object name ‘’’ + s.base_object_name + ‘’’’ COLLATE DATABASE_DEFAULT
, s.[type] COLLATE DATABASE_DEFAULT
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, metadata way you can extend the standard functionality of SSMS to do your day-to-day database tasks.

Originally published at https://blog.devart.com.