Skip to content

Instantly share code, notes, and snippets.

@infogulch infogulch/SysDepends.sql Secret

Created May 27, 2014
Embed
What would you like to do?
-- Refactored from http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx
CREATE VIEW dbo.SysDependsInfo
AS
SELECT ResultType
, CASE ResultType
WHEN 'reference' THEN referencing_qualified + ' references ' + object_name + ' (' + referenced_id + '), which doesn''t exist.'
WHEN 'dependency' THEN referencing_qualified + ' might have a cross-db depenency problem with ' + object_name + '.'
WHEN 'sql' THEN 'EXEC sys.sp_refreshsqlmodule N''' + referencing_qualified + ''''
END AS Result
FROM (
SELECT ed.referencing_id
, ed.referenced_database_name AS ref_db_name
, RTRIM(COALESCE(ed.referenced_database_name + '.', '')
+ COALESCE(ed.referenced_schema_name + '.', '')
+ ed.referenced_entity_name) AS object_name
, RTRIM(COALESCE(ed.referenced_id, 0)) AS referenced_id
FROM
sys.sql_expression_dependencies ed
LEFT OUTER JOIN
sys.sysdepends sd
ON
ed.referencing_id = sd.id
AND ed.referenced_id = sd.depid
WHERE
sd.id IS NULL
AND ed.referenced_server_name IS NULL
) AS Q
OUTER APPLY (
SELECT
OBJECT_ID(object_name) AS object_id,
QUOTENAME(OBJECT_SCHEMA_NAME(referencing_id)) + '.'
+ OBJECT_NAME(referencing_id) AS referencing_qualified
) AS A
OUTER APPLY (
SELECT CASE
WHEN object_id IS NULL AND ref_db_name IS NULL AND referenced_id IS NULL THEN 'reference'
WHEN object_id IS NULL AND ref_db_name IS NOT NULL THEN 'dependency'
ELSE 'sql'
END AS ResultType
) AS B
GO
CREATE PROCEDURE dbo.RefreshSysDepends
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(600);
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT result
FROM dbo.SysDependsInfo
WHERE ResultType = 'sql'
OPEN c;
FETCH NEXT FROM c INTO @sql;
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC sp_executesql @sql;
FETCH NEXT FROM c INTO @sql;
END
CLOSE c;
DEALLOCATE c;
END
GO
CREATE TRIGGER DDL_Catcher ON DATABASE
FOR
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
ALTER_SCHEMA, RENAME
/*
RENAME is new in SQL Server 2008. If you
try this in SQL Server 2005, you will get:
Msg 1084, Level 15, State 1, Procedure DDL_Catcher, Line 8
'RENAME' is an invalid event type.
*/
AS
BEGIN
SET NOCOUNT ON;
-- you can log this somewhere for later review, or you
-- can just comment it out:
SELECT N'Fired for ', EVENTDATA().value
(
'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'NVARCHAR(MAX)'
);
EXEC dbo.RefreshSysDepends;
END
GO
@edcrane

This comment has been minimized.

Copy link

edcrane commented Aug 12, 2014

Joe,

Very nice! And if you're cursor-averse, you can always do something like this:

CREATE PROCEDURE dbo.RefreshSysDepends
AS
BEGIN
SET NOCOUNT ON;

declare @Sql nvarchar(max) = N''
select @Sql = @Sql + result + char(31) + char(10)
from dbo.SysDependsInfo
where ResultType = 'sql'
exec sp_executesql @Sql;

END
GO

Thanks for this,
Ed

@edcrane

This comment has been minimized.

Copy link

edcrane commented Aug 12, 2014

Oh, and add DISTINCT to the view definition:

SELECT distinct ResultType

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.