>Get Tables List With Dependent Tables Name


>Following script help you create all of your database tables list along with their dependent tables name in comma separated format.

DECLARE @mastertable VARCHAR(100)
DECLARE @TableCompleteName VARCHAR(100)
DECLARE @tablesname VARCHAR(1000)
CREATE TABLE #temptable
    (
      tablecompletename VARCHAR(100),
      tablename VARCHAR(1000)
    )
DECLARE tmp_cur CURSOR static
    FOR SELECT  s.name + ‘.’ + o.name,
                o.name
        FROM    sys.objects o
                INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE   type = ‘U’
        ORDER BY s.name,
                o.name
     
OPEN tmp_cur
      –FETCH
     
FETCH FIRST FROM tmp_cur INTO @TableCompleteName, @mastertable
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT  @tablesname = COALESCE(@tablesname + ‘,’, ) + s.name + ‘.’
                + OBJECT_NAME(FKEYID)
        FROM    SYSFOREIGNKEYS
                INNER JOIN sys.objects o ON o.object_id = SYSFOREIGNKEYS.fkeyid
                INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
        WHERE   OBJECT_NAME(RKEYID) = @mastertable
                 
        INSERT  INTO #temptable
                (
                  tablecompletename,
                  tablename
                )
                SELECT  @TableCompleteName,
                        COALESCE(@tablesname, )
        SELECT  @tablesname = NULL
        FETCH NEXT FROM tmp_cur INTO @TableCompleteName, @mastertable
    END
   
SELECT  tablecompletename AS TableName, tablename AS DependentTables
FROM    #temptable
DROP TABLE #temptable
CLOSE tmp_cur
DEALLOCATE tmp_cur
Advertisements

Posted on November 11, 2010, in TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: