Sqlserver - How to Find Dependent Tables on My Table

SQLServer - How to find dependent tables on my table?

In SQL server management studio, you can right click your table in the object explorer, and then select 'View Dependencies'. This will open a new window in which you can see all other objects (not just tables) that depend on your table, and on which your table depends.

Query to find Table objects dependent on a Table in SQL Server 2008

Simply we can do this In SQL server management studio Right-click on a table and choose 'View Dependencies'.

By using query

Select
S.[name] as 'Dependent_Tables'
From
sys.objects S inner join sys.sysreferences R
on S.object_id = R.rkeyid
Where
S.[type] = 'U' AND
R.fkeyid = OBJECT_ID('tablename')

Another method

SELECT DISTINCT name, so.type 
FROM sys.objects AS so 
INNER JOIN sys.sql_expression_dependencies AS sed 
ON so.object_id = sed.referencing_id 
WHERE sed.referenced_id = OBJECT_ID('[tablename]');

Second method selects Procedures, views too.

How to find all the dependencies of a table in sql server

The following are the ways we can use to check the dependencies:

Method 1: Using sp_depends

 sp_depends 'dbo.First'
 GO

Method 2: Using information_schema.routines

 SELECT *
 FROM information_schema.routines ISR
 WHERE CHARINDEX('dbo.First', ISR.ROUTINE_DEFINITION) > 0
 GO

Method 3: Using DMV sys.dm_sql_referencing_entities

 SELECT referencing_schema_name, referencing_entity_name,
 referencing_id, referencing_class_desc, is_caller_dependent
 FROM sys.dm_sql_referencing_entities ('dbo.First', 'OBJECT');
 GO

How to determine a table's dependencies?

Try this may be it will helpful for you.

EXEC sp_fkeys 'TableName'

Get list of dependent Tables, SQL Server 2005

The answer is in this URL. A very helpful tutorial..
Great query.

http://www.jasinskionline.com/technicalwiki/Default.aspx?Page=List-Tables-in-Dependency-Order-SQL-Server&NS=&AspxAutoDetectCookieSupport=1

How can I list all foreign keys referencing a given table in SQL Server?

Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table:

EXEC sp_fkeys 'TableName'

You can also specify the schema:

EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'

Without specifying the schema, the docs state the following:

If pktable_owner is not specified, the default table visibility rules
of the underlying DBMS apply.

In SQL Server, if the current user owns a table with the specified
name, that table's columns are returned. If pktable_owner is not
specified and the current user does not own a table with the specified
pktable_name, the procedure looks for a table with the specified
pktable_name owned by the database owner. If one exists, that table's
columns are returned.

Find tables that a particular table depends on in SQL Server

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.

---

Related Topics