Início / dba / Perguntas / 31434
Accepted
Kahn
Asked: 2013-01-05 06:51:40 +0800 CST

SQL Server 2008 - Como criar uma consulta dinâmica para localizar relacionamentos e referências à chave primária?

  • 772

Como sou novo e nunca compartilhei, e ainda assim existem alguns scripts úteis de que preciso diariamente, achei que deveria.

Como um novo DBA, algumas operações DML de correção de dados e análises lógicas de negócios simples são muito mais rápidas quando tenho um modelo com o qual posso verificar as restrições em um banco de dados muito grande com centenas de tabelas. O script de Pinal Dave (fonte postada na resposta abaixo) foi uma grande ajuda quando comecei, mas desde então o expandi um pouco para incluir a possibilidade de pesquisar IDs referenciados em tabelas relacionadas também.

Espero que isso seja útil para alguns de vocês!

sql-server-2008 relational-theory

1 respostas

  1. Best Answer

    A consulta que usei para o cenário acima está abaixo. Deixe-me saber o que você pensa, e se você tiver links para outras dicas genéricas e úteis.

    
/*
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

relate perguntas