Created
June 26, 2018 13:56
-
-
Save aaronolds/e21bff93dbb9e323edc25f3b41debc05 to your computer and use it in GitHub Desktop.
Find missing MS SQL Object References
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- http://en.gravatar.com/sqlmonger | |
SELECT | |
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) | |
AND NOT EXISTS | |
(SELECT * | |
FROM sys.types | |
WHERE types.name = referenced_entity_name | |
AND types.schema_id = ISNULL(SCHEMA_ID(referenced_schema_name), SCHEMA_ID('dbo')) | |
) | |
ORDER BY [this Object...], | |
[... depends ON this missing entity name] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment