CODEWITHSUNDEEP
sql-serverdependencies
Carlos
Carlos

Reputation: 6021

How do you find all dependencies of a database table?

In MS SQL 2005, is it possible to find out which tables/columns are being used either as keys in another table, or as part of a stored procedure?

The reason is I'm trying to clean up some old stored procs and tables, some of which can be removed, some of which can have columns pruned. But obviously I don't want to remove stuff which is being used.

Upvotes: 1

Views: 4242

Answers (2)

bjnr
bjnr

Reputation: 3437

In SQL Server 2008 there are two new Dynamic Management Functions introduced to keep track of object dependencies: sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities:

1/ Returning the entities that refer to a given entity:

SELECT
        referencing_schema_name, referencing_entity_name, 
        referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('<TableName>', 'OBJECT')

2/ Returning entities that are referenced by an object:

SELECT
        referenced_schema_name, referenced_entity_name, referenced_minor_name, 
        referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('<StoredProcedureName>', 'OBJECT');

Another option is to use a pretty useful tool called SQL Dependency Tracker from Red Gate.

Upvotes: 1

Jonathan
Jonathan

Reputation: 26619

DECLARE @BaseObject varchar(100)
SET @BaseObject = 'AddEntry'

SET NOCOUNT ON

DECLARE @Objects TABLE (
    id int
)

INSERT @Objects (id)
SELECT id FROM sysobjects
WHERE name like @BaseObject

WHILE (@@ROWCOUNT > 0)
BEGIN
    INSERT @Objects (id)
    SELECT d.depid
    FROM sysdepends d
    WHERE d.id IN (SELECT id FROM @Objects)
    AND d.depid NOT IN (SELECT id FROM @Objects)
END

SET NOCOUNT OFF

SELECT convert(varchar(100),
        '[' + oo.name + '].[' + o.name + ']') AS '--Object--'
FROM sysobjects o 
INNER JOIN sysusers oo ON o.uid = oo.uid
WHERE o.id IN (SELECT id FROM @Objects)
ORDER BY oo.name, o.name

Upvotes: 1

Related Questions