29 de dez. de 2009

Descobrindo os Relacionamentos das Colunas de um Banco de Dados

image

Perguntei-me, algum tempo atrás, como descobrir o relacionamento entre colunas de um banco de dados no SQL Server. Eis o SQL resultante:

Select
    KeyColumnUsage.Table_Name As Tabela1,
    KeyColumnUsage.Column_Name As Coluna1,
    ConstraintColumnUsage.Table_Name As Tabela2,
    ConstraintColumnUsage.Column_Name As Coluna2,
    KeyColumnUsage.Constraint_Name As NomeFK
From
    Information_Schema.Key_Column_Usage As KeyColumnUsage
    Inner Join Information_Schema.Table_Constraints As TableConstraints On
        KeyColumnUsage.Table_Name = TableConstraints.Table_Name
        And KeyColumnUsage.Constraint_Name = TableConstraints.Constraint_Name
    Inner Join Information_Schema.Referential_Constraints As ReferentialConstraints On
        TableConstraints.Constraint_Name = ReferentialConstraints.Constraint_Name
    Inner Join Information_Schema.Constraint_Column_Usage As ConstraintColumnUsage On
        ReferentialConstraints.Unique_Constraint_Name = ConstraintColumnUsage.Constraint_Name
Where
    TableConstraints.Constraint_Type = 'FOREIGN KEY'

...agora estou trabalhando para mostrar como a relação é feita (1..1; 1..n; n..n). Aceito Sugestões ;)

0 comentários: