A short post today.
I use the following script to find (broken) stored procedures or views that refer to sprocs or tables that no longer exist… or that don’t exist yet.
This is a quick sanity check that can help identify broken or obsolete stored procedures.
SELECT OBJECT_SCHEMA_NAME(referencing_id) + '.' + OBJECT_NAME(referencing_id) AS [referencer], referenced_entity_name AS [referenced] FROM sys.sql_expression_dependencies WHERE is_ambiguous = 0 AND OBJECT_ID(ISNULL(referenced_schema_name, 'dbo') + '.' + referenced_entity_name) IS NULL AND OBJECT_ID(ISNULL(referenced_schema_name, OBJECT_SCHEMA_NAME(referencing_id)) + '.' + referenced_entity_name) IS NULL AND referenced_entity_name NOT IN (SELECT Name FROM sys.types WHERE is_user_defined = 1) -- avoid type false positives AND referenced_entity_name not in ('deleted', 'inserted') -- avoid trigger false positives AND referenced_database_name is null ORDER BY OBJECT_NAME(referencing_id), referenced_entity_name |
Howabout something that rebuilds all dependencies when an update occurs. In other words… if I want to change a view… I want to automatically rebuild all stored procedures and dependent views that are associated to this? ( mainly because I hate that sql server loses its dependency tracking if you commit something out of order )
I NEED A REBUILD DEPENDENCY LIST! ( and I don’t want to buy another app to do it for me )
Comment by Richard Wallace — December 18, 2009 @ 2:18 pm
Well,
In this script I check for OBJECT_ID(referenced_entity_name) rather than referenced_id which kind of avoids that whole issue.
In the meantime, in 2008, You might be able to do something by combining
sp_refreshsqlmodule with the results of sys.dm_sql_referencing_entities.
Aaron Bertrand has the definitive blog post about this (which you may have already read):
http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx
Comment by Michael J. Swart — December 18, 2009 @ 2:56 pm
[…] Find Missing SQL Dependencies If a view, function or stored procedure refers to a nonexistent object (be it a table, view etc…) This query can help you identify those. […]
Pingback by Top 8 Queries to Improve DB Design | Michael J. Swart — December 21, 2009 @ 2:24 pm
[…] Find Missing SQL Dependencies – Nifty little script here, courtesy of Michale J Swart, for assisting with Finding Missing SQL Dependencies. […]
Pingback by SQL Server Links (Xmas Edition) | John Sansom - SQL Server DBA in the UK — December 25, 2009 @ 5:38 am
I’ve just extended the previous script to be aware of databases and servers and also to take care of non-standard names:
SELECT TOP (100) PERCENT
QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) AS [this sproc or VIEW...],
ISNULL(QuoteName(referenced_server_name) + '.', '')
+ ISNULL(QuoteName(referenced_database_name) + '.', '')
+ ISNULL(QuoteName(referenced_schema_name) + '.', '')
+ QuoteName(referenced_entity_name) AS [... depends ON this missing entity name]
FROM sys.sql_expression_dependencies
WHERE (is_ambiguous = 0)
AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + '.', '')
+ ISNULL(QuoteName(referenced_database_name) + '.', '')
+ ISNULL(QuoteName(referenced_schema_name) + '.', '')
+ QuoteName(referenced_entity_name)) IS NULL)
ORDER BY [this sproc or VIEW...],
[... depends ON this missing entity name]
Comment by RaduSun — March 25, 2010 @ 9:26 am
[…] sott’occhio gli elementi non validi presenti nel database. Ho trovato la risposta nel blog di Michael J. Swart e in un thread sul […]
Pingback by Trovare oggetti non validi in SQL Server 2008 | HeDo's Blog — May 7, 2011 @ 7:12 am
Chad Dokmanovich
The above SQLs both incorrectly report Proc1 as having missing dependency {Alias} because it doesn’t recognize that {Alias} is anotehr name for a table that exists rather than n-existing table.
CREATE SUB Proc1
As
UPDATE {Alias}
SET {Col} = {Value}
FROM {Realtable} {Alias}
Are there any fixes?
Comment by Chad — August 28, 2012 @ 11:09 am
I noticed that myself. You have to mentally exclude Aliases. And I haven’t checked in a long time, but I don’t remember hearing about any improvements to SQL Server in this area.
Comment by Michael J. Swart — August 28, 2012 @ 11:12 am
The following tool that we developed does just that:
http://nobhillsoft.com/Diana.aspx
we’re giving it for free… no catches no gimmicks
Comment by Jonathan Scion — December 4, 2013 @ 9:08 pm
@Chad
I have found that if you do a refresh of the SP that contains the UPDATE {alias} it stops showing as a false positive
EXEC sys.sp_refreshsqlmodule ‘[dbo].[spName]’
Comment by Nate VH — July 1, 2014 @ 11:41 am
Magical query, appreciate it. I don’t think it reports invalid DB objects on account of missing columns referenced, does it?
Comment by Ranjit — September 30, 2014 @ 6:40 am
Tweaked the script above to include the object types as columns, and added a column that generates a call to sys.sp_refreshsqlmodule to help eliminate false positives.
SELECT TOP (100) PERCENT
QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + ‘.’ + QuoteName(OBJECT_NAME(referencing_id)) AS [this sproc or VIEW…],
o.type_desc,
ISNULL(QuoteName(referenced_server_name) + ‘.’, ”)
+ ISNULL(QuoteName(referenced_database_name) + ‘.’, ”)
+ ISNULL(QuoteName(referenced_schema_name) + ‘.’, ”)
+ QuoteName(referenced_entity_name) AS [… depends ON this missing entity name]
,sed.referenced_class_desc
,case when o.type_desc = ‘SQL_STORED_PROCEDURE’
then ‘EXEC sys.sp_refreshModule ‘ + QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + ‘.’ + QuoteName(OBJECT_NAME(referencing_id)) + ‘;’
else null
end as [Refresh SQL Module command]
FROM sys.sql_expression_dependencies as sed
LEFT JOIN sys.objects o
ON sed.referencing_id=o.object_id
WHERE (is_ambiguous = 0)
AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + ‘.’, ”)
+ ISNULL(QuoteName(referenced_database_name) + ‘.’, ”)
+ ISNULL(QuoteName(referenced_schema_name) + ‘.’, ”)
+ QuoteName(referenced_entity_name)) IS NULL)
ORDER BY [this sproc or VIEW…],
[… depends ON this missing entity name]
Comment by SQLMonger — July 20, 2015 @ 12:24 pm
Too hasty… Updated to add quotes around the call to sp_refreshsqlmodule… and to correct the proc name in the generated code column.
SELECT TOP (100) PERCENT
QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + ‘.’ + QuoteName(OBJECT_NAME(referencing_id)) AS [this Object…],
o.type_desc,
ISNULL(QuoteName(referenced_server_name) + ‘.’, ”)
+ ISNULL(QuoteName(referenced_database_name) + ‘.’, ”)
+ ISNULL(QuoteName(referenced_schema_name) + ‘.’, ”)
+ QuoteName(referenced_entity_name) AS [… depends ON this missing entity name]
,sed.referenced_class_desc
,case when o.type_desc in( ‘SQL_STORED_PROCEDURE’ ,’SQL_SCALAR_FUNCTION’ ,’SQL_TRIGGER’ ,’VIEW’)
then ‘EXEC sys.sp_refreshsqlmodule ”’ + QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + ‘.’ + QuoteName(OBJECT_NAME(referencing_id)) + ”’;’
else null
end as [Refresh SQL Module command]
FROM sys.sql_expression_dependencies as sed
LEFT JOIN sys.objects o
ON sed.referencing_id=o.object_id
WHERE (is_ambiguous = 0)
AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + ‘.’, ”)
+ ISNULL(QuoteName(referenced_database_name) + ‘.’, ”)
+ ISNULL(QuoteName(referenced_schema_name) + ‘.’, ”)
+ QuoteName(referenced_entity_name)) IS NULL)
ORDER BY [this Object…],
[… depends ON this missing entity name]
Comment by SQLMonger — July 20, 2015 @ 12:41 pm
One further improvement: User-define types were causing false positives. Updated to address that gap…
Comment by SQLMonger — July 20, 2015 @ 2:39 pm
[…] Michael J. Swart: Find Missing SQL Dependencies […]
Pingback by Find broken objects in SQL Server – w3toppers.com — May 18, 2023 @ 7:48 pm