Find Invalid Objects in Your Databases

Jordan Sanders
6 min readMay 27, 2019

--

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_nameFROM all_objectsWHERE 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:

SELECTobj_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_nameFROM sys.sql_expression_dependencies dJOIN sys.objects o ON d.referencing_id = o.[object_id]WHERE d.is_ambiguous = 0AND d.referenced_id IS NULLAND d.referenced_server_name IS NULL — ignore objects from Linked serverAND CASE d.referenced_class — if does not existWHEN 1 — objectTHEN 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 datatypeTHEN TYPE_ID(ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + ‘.’ + d.referenced_entity_name)WHEN 10 — or XML schemaTHEN (SELECT 1 FROM sys.xml_schema_collections xWHERE x.name = d.referenced_entity_nameAND 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_ViewAS SELECT ID = 1GOCREATE PROCEDURE dbo.usp_ProcedureAS BEGINSELECT ID FROM dbo.vw_ViewENDGOALTER VIEW dbo.vw_ViewAS SELECT New_ID = 1GO

We will get an error while executing the storage procedure:

Msg 207, Level 16, State 1, Procedure usp_Procedure, Line 6Invalid 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 NULLDROP TABLE #objectsCREATE 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)SELECTt.referencing_id, obj_name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + ‘.’ + QUOTENAME(o.name), ‘Invalid object name ‘’’ + t.obj_name + ‘’’’, o.[type]FROM (SELECTd.referencing_id, obj_name = MAX(COALESCE(d.referenced_database_name + ‘.’, ‘’)+ COALESCE(d.referenced_schema_name + ‘.’, ‘’)+ d.referenced_entity_name)FROM sys.sql_expression_dependencies dWHERE d.is_ambiguous = 0AND d.referenced_id IS NULLAND d.referenced_server_name IS NULL — ignore objects from Linked serverAND CASE d.referenced_class — if does not existWHEN 1 — objectTHEN 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 datatypeTHEN TYPE_ID(ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + ‘.’ + d.referenced_entity_name)WHEN 10 — or XML schemaTHEN (SELECT 1 FROM sys.xml_schema_collections xWHERE x.name = d.referenced_entity_nameAND x.[schema_id] = ISNULL(SCHEMA_ID(d.referenced_schema_name), SCHEMA_ID()))END IS NULLGROUP BY d.referencing_id) tJOIN sys.objects o ON t.referencing_id = o.[object_id]WHERE LEN(t.obj_name) > 4 — hide valid aliasesDECLARE@obj_id INT, @obj_name NVARCHAR(1000), @obj_type CHAR(2)DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FORSELECTsm.[object_id], QUOTENAME(SCHEMA_NAME(o.[schema_id])) + ‘.’ + QUOTENAME(o.name), o.[type]FROM sys.sql_modules smJOIN sys.objects o ON sm.[object_id] = o.[object_id]LEFT JOIN (SELECT s.referenced_idFROM sys.sql_expression_dependencies sJOIN sys.objects o ON o.object_id = s.referencing_idWHERE s.is_ambiguous = 0AND s.referenced_server_name IS NULLAND 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 SCHEMABINDINGAND sm.[object_id] NOT IN (SELECT o2.obj_id FROM #objects o2)AND OBJECTPROPERTY(sm.[object_id], ‘IsEncrypted’) = 0AND (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 columnssed.referenced_id IS NULL))OPEN curFETCH NEXT FROM cur INTO @obj_id, @obj_name, @obj_typeWHILE @@FETCH_STATUS = 0 BEGINBEGIN TRYBEGIN TRANSACTIONEXEC sys.sp_refreshsqlmodule @name = @obj_name, @namespace = N’OBJECT’COMMIT TRANSACTIONEND TRYBEGIN CATCHIF XACT_STATE() != 0ROLLBACK TRANSACTIONINSERT INTO #objects (obj_id, obj_name, err_message, obj_type)SELECT @obj_id, @obj_name, ERROR_MESSAGE(), @obj_typeEND CATCHFETCH NEXT FROM cur INTO @obj_id, @obj_name, @obj_typeENDCLOSE curDEALLOCATE curSELECT obj_name, err_message, obj_typeFROM #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 curFETCH NEXT FROM cur INTO @obj_name, @obj_typeWHILE @@FETCH_STATUS = 0 BEGINBEGIN TRYBEGIN 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 CATCHFETCH NEXT FROM cur INTO @obj_name, @obj_typeENDCLOSE 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 sWHERE PARSENAME(s.base_object_name, 4) IS NULL — ignore objects from Linked serverAND 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_typeFROM #objectsUNION ALLSELECTQUOTENAME(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_DEFAULTFROM sys.synonyms sWHERE PARSENAME(s.base_object_name, 4) IS NULLAND 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.

--

--

Jordan Sanders
Jordan Sanders

Written by Jordan Sanders

PR Manager at Devart since 2010 | Obsessed with the promotion of database development optimization