Thursday, November 11, 2010

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

No comments:

Post a Comment

All suggestions are welcome