主页 / dba / 问题 / 31434
Accepted
Kahn
Asked: 2013-01-05 06:51:40 +0800 CST

SQL Server 2008 - 我将如何创建一个动态查询来查找关系和对主键的引用？

  • 772

由于我是新手而且我从未分享过，但我每天都需要一些有用的脚本，所以我认为我应该这样做。

作为一名新的 DBA，当我有一个模板时，一些数据修复 DML 操作和简单的业务逻辑分析会快得多，我可以使用该模板检查包含数百个表的非常大的数据库中的约束。Pinal Dave 的脚本（来源发布在下面的答案中）在我开始时提供了很大的帮助，但从那时起我对其进行了一些扩展以包括在相关表中搜索引用 ID 的可能性。

我希望这对你们中的一些人有用！

sql-server-2008 relational-theory

1 个回答

  1. Best Answer

    我在上面的场景中使用的查询如下。让我知道您的想法，以及您是否链接到其他此类通用且有用的技巧。

    
/*
Modified from Pinal Dave's original @ SQLAuthority: 
    http://blog.sqlauthority.com/2009/02/26/sql-server-2008-find-relationship-of-foreign-key-and-primary-key-using-t-sql-find-tables-with-foreign-key-constraint-in-database/

Supply the name of the table, and optionally the ID (default = NULL) whose 
references you wish to see from other tables.
*/

DECLARE @SEARCHED_NAME VARCHAR(50), @SEARCHED_ID INT 
SET @SEARCHED_NAME = 'TABLENAME' -- The name of the table whose constraint connections you wish to see.
SET @SEARCHED_ID = NULL -- The parent table ID you want to find in any of the tables referencing the parent table.

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
WHERE OBJECT_NAME(f.parent_object_id) LIKE @SEARCHED_NAME
OR OBJECT_NAME (f.referenced_object_id) LIKE @SEARCHED_NAME
ORDER BY f.name

IF @SEARCHED_ID IS NOT NULL
BEGIN

    CREATE TABLE #SEARCHOBJREF (RowNum INT IDENTITY(1,1), TableName VARCHAR(256)
        , ColumnName VARCHAR(256), MatchCount INT)

    INSERT INTO #SEARCHOBJREF (TableName, ColumnName)
    SELECT DISTINCT OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
    INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
    WHERE OBJECT_NAME (f.referenced_object_id) LIKE @SEARCHED_NAME
    ORDER BY OBJECT_NAME(f.parent_object_id)

    DECLARE @ROW_NOW INT, @ROW_MAX INT, @SQL VARCHAR(MAX), @TNAME VARCHAR(256), @COLNAME VARCHAR(256)
    SELECT @ROW_NOW = MIN(RowNum), @ROW_MAX = MAX(RowNum) FROM #SEARCHOBJREF

    WHILE @ROW_NOW <= @ROW_MAX
    BEGIN
        SELECT @TNAME = TableName, @COLNAME = ColumnName FROM #SEARCHOBJREF WHERE RowNum = @ROW_NOW

        SET @SQL = 'UPDATE #SEARCHOBJREF '
            + ' SET MatchCount = (SELECT COUNT(*) FROM ' + @TNAME + ' WHERE ' 
            + @COLNAME + ' = ' + CAST(@SEARCHED_ID AS VARCHAR(64)) + ') '
            + 'WHERE RowNum = ' + CAST(@ROW_NOW AS VARCHAR(64))
        EXEC(@SQL)

        SET @ROW_NOW = @ROW_NOW + 1
    END

    SELECT TableName, ColumnName, MatchCount as 'Matches_for_ID' FROM #SEARCHOBJREF WHERE MatchCount > 0
    DROP TABLE #SEARCHOBJREF

END
    • 1

相关问题