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