Find tables that a particular table depends on in SQL Server

Question

Is there a way to get all the tables a table depends on? There must be as SQL Server Management Studio allows you to do this by selecting Find Dependencies and then the option 'Object which [tablename] depends on'

I know about sp_depends but this give me back the object that depend on the table not what the table depends on.

Thanks in advance,

Jon

Answer 1

Have you tried sp_MSdependencies? It's undocumented so you cannot completely rely on it, but it seems to be pretty flexible and to do its job nicely...

Answer 2

Based on the discussion with gbn and the assumption that you only care about the objects the table depends on (rather than anything that depends on the table), I came up with this contrived example:

USE [master];
GO

IF DB_ID('foo') IS NOT NULL
    DROP DATABASE foo;
GO

CREATE DATABASE foo;
GO

USE foo;
GO

CREATE TYPE dbo.Email FROM VARCHAR(320) NOT NULL;
GO

CREATE SCHEMA foo AUTHORIZATION dbo;
GO

CREATE TYPE foo.Email FROM VARCHAR(320) NULL;
GO

CREATE FUNCTION dbo.IsGreaterThanZero1(@i INT)
RETURNS BIT
AS
BEGIN
    RETURN (SELECT CASE WHEN @i>0 THEN 1 ELSE 0 END);
END
GO

CREATE FUNCTION dbo.IsGreaterThanZero2(@i INT)
RETURNS BIT
AS
BEGIN
    RETURN (SELECT CASE WHEN @i>0 THEN 1 ELSE 0 END);
END
GO

CREATE TABLE dbo.bar
(
    id INT PRIMARY KEY
);
GO

CREATE FUNCTION dbo.maxbar()
RETURNS INT
AS
BEGIN
    RETURN (SELECT MAX(id) FROM dbo.bar);
END
GO

CREATE TABLE dbo.foo
(
    id INT FOREIGN KEY REFERENCES dbo.bar(id), 
        -- dependency on foreign key to another table
    Email1 dbo.Email, 
        -- dependency on alias type
    Email2 foo.Email, 
        -- dependency on alias type in different schema
    IsMoreThanZero1 AS CONVERT(BIT, dbo.IsGreaterThanZero1(id)), 
        -- computed column dependency
    IsMoreThanZero1A AS dbo.IsGreaterThanZero1(id), 
        -- computed column dependency
    IsMoreThanZero2 BIT CHECK (dbo.IsGreaterThanZero2(IsMoreThanZero2)=1), 
        -- check constraint dependency
    IsMoreThanZero2A BIT CHECK (CONVERT(BIT,     
            dbo.IsGreaterThanZero2(IsMoreThanZero2A))=1), 
        CHECK(IsMoreThanZero2A LIKE '[,%]'),
        -- check constraint dependency
    maxbar INT NOT NULL DEFAULT (dbo.maxbar()) 
        -- default constraint dependency
);
GO

CREATE TRIGGER dbo.after_insert_foo ON dbo.foo
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @x INT;
    SELECT TOP (1) @x = id FROM dbo.bar;
END
GO

Okay, now that the database is chock full of stuff to find, the following script will identify all of the object references above:

DECLARE @tablename SYSNAME = N'dbo.foo';

DECLARE @object_id INT = OBJECT_ID(@tablename);

-- functions mentioned in check/default constraints
-- and computed columns in @tablename

WITH x AS 
(
    SELECT [type], [obj], [count] = COUNT(*)
    FROM
    (
        SELECT [type], obj = OBJECT_ID( 
            SUBSTRING(d, CHARINDEX('],', d) + 2, 
                CHARINDEX('(', SUBSTRING(d, 
                CHARINDEX('],', d) + 2, LEN(d)))-1))
        FROM
        (
            SELECT [type] = 'default', [object_id], d = [definition]
            FROM sys.default_constraints 
            WHERE parent_object_id = @object_id
            AND CHARINDEX('].[', [definition]) > 0 
            UNION
            SELECT 'check', [object_id], [definition]
            FROM sys.check_constraints
            WHERE parent_object_id = @object_id
            AND CHARINDEX('].[', [definition]) > 0 
            UNION
            SELECT 'computed', NULL, [definition]
            FROM sys.computed_columns
            WHERE [object_id] = @object_id 
            AND CHARINDEX('].[', [definition]) > 0 
        ) AS x
    ) AS y GROUP BY [type], [obj]

    UNION ALL

    -- triggers defined on @tablename
    SELECT 'trigger', obj = [object_id], 1
        FROM sys.triggers
        WHERE parent_id = @object_id

    UNION ALL

    -- objects referenced by triggers on @tablename
    SELECT 'trigger references', [obj] = d.[referenced_major_id], COUNT(*)
        FROM sys.sql_dependencies AS d
        INNER JOIN sys.triggers AS tr
        ON d.[object_id] = tr.[object_id]
        AND tr.parent_id = @object_id
        GROUP BY d.referenced_major_id

    UNION ALL

    -- foreign keys referenced by @tablename
    SELECT 'foreign key', [obj] = referenced_object_id, COUNT(*)
        FROM sys.foreign_keys
        WHERE parent_object_id = @object_id
        GROUP BY referenced_object_id
)
SELECT 
    [obj] = QUOTENAME(OBJECT_SCHEMA_NAME(obj)) + '.'
          + QUOTENAME(OBJECT_NAME(obj)), 
    [type],
    [count]
FROM x
UNION ALL
SELECT
    [obj],
    [type],
    [count] = COUNT(*)
FROM
(
    SELECT
        [obj] = QUOTENAME(SCHEMA_NAME(t.[schema_id])) 
            + '.' + QUOTENAME(t.name),
        [type] = 'alias type'
    FROM
        sys.types AS t
        INNER JOIN sys.columns AS c
        ON t.user_type_id = c.user_type_id
        WHERE t.is_user_defined = 1
        AND c.[object_id] = @object_id
) AS x GROUP BY [obj], [type];

There are more caveats here than I care to mention. One is that the definition parsing in sys.default_constraints, sys.check_constraints and sys.computed_columns assumes that you don't have constants that look amazingly like object names (specifically I parse for ].[ to show a function name, since you can't leave the schema out and square brackets are added for you), function names that don't include special characters like "[", ".", or "]", or have arguments passed to the UDF that contain '[' or ']' because I use those to determine that it is in fact a function (and I also assume that there aren't nested functions). It also assumes that all references are contained within the same database. Yet another is that I only go one layer deep - if you have a trigger on dbo.foo that calls a function that in turn references another table, that won't be included. Free help is only going to be willing to go so far down the rabbit hole. :-)

I still don't trust any of the dependencies views 100%, so if your system is volatile I would say your safest bet is to follow gbn's advice and pursue brute force parsing using sys.sql_modules.definition for the parts of this that are prone to invalidation due to schema changes. There are just so many ways that automating this stuff can go wrong, I don't know if you'll ever have a 100% bullet-proof solution - though with a LOT of work you can get pretty close.

But back to the original question - maybe you could define explicitly exactly which types of dependencies you're looking for.

Answer 3

Tables don't normally depend on tables apart from

• foreign keys. Use sys.foreign_key, object_id and referenced_object_id columns
• triggers. Use sys.sql_modules and sys.objects.
• CHECK constraints that have UDFs. Tricky one this, use sys.sql_modules and sys.objects again

Indirect dependencies, like what view JOINs 2 tables will all be from some clever queries on sys.sql_modules