Sqlserver——日常维护——查看数据库中所有表的被引用情况

通过下面这个脚本可以查到当前数据库中所有表的被引用的情况

IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID('tempdb..#Temp_Table'))
    DROP TABLE tempdb..#Temp_Table ;

SELECT sc.name AS SchemaName,
       tb.name AS TableName,
       tb.create_date AS CreateDate,
       tb.modify_date AS UpdateDate
INTO   #Temp_Table
FROM   sys.tables tb
       LEFT JOIN sys.schemas sc ON tb.schema_id = sc.schema_id
WHERE
       tb.type = 'U' ;

DECLARE @TableName NVARCHAR (517) = N'' ;
DECLARE @CreateDate DATETIME ;
DECLARE @UpdateDate DATETIME ;
DECLARE @ReferenceObject NVARCHAR (MAX) = N'' ;

DECLARE @Temp_Table TABLE
(
    SchemaTable     NVARCHAR (300),
    ReferenceObject NVARCHAR (MAX),
    CreateDate      DATETIME,
    UpdateDate      DATETIME
) ;

DECLARE localTable_Cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT SchemaName + '.' + TableName, CreateDate, UpdateDate FROM #Temp_Table ;

OPEN localTable_Cursor ;

FETCH NEXT FROM localTable_Cursor
INTO @TableName,
     @CreateDate,
     @UpdateDate ;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ReferenceObject = (
                               SELECT ( '[' + referencing_schema_name + '.' + referencing_entity_name + ']' ) + ','
                               FROM   sys.dm_sql_referencing_entities(@TableName, 'OBJECT')
                               FOR XML PATH('')
                           ) ;
    SET @ReferenceObject = COALESCE(SUBSTRING(@ReferenceObject, 1, LEN(@ReferenceObject) - 1), '') ;

    INSERT INTO @Temp_Table ( SchemaTable, ReferenceObject, CreateDate, UpdateDate )
    SELECT @TableName, @ReferenceObject, @CreateDate, @UpdateDate ;

    FETCH NEXT FROM localTable_Cursor
    INTO @TableName,
         @CreateDate,
         @UpdateDate ;
END ;

CLOSE localTable_Cursor ;
DEALLOCATE localTable_Cursor ;

SELECT SchemaTable, ReferenceObject, CreateDate, UpdateDate FROM @Temp_Table ;