none
Primary Key e Foreign Key RRS feed

  • Pergunta

  • Aê galera, preciso listar as tabelas do meu banco que contenham o mesmo nome de uma coluna, mas preciso que venham por por ordem de Foreign Key para Primary Key.
    segunda-feira, 18 de fevereiro de 2013 20:16

Respostas

  • WFA.COM, boa tarde.

    Dê uma pesquisada sobre information schema.

    http://msdn.microsoft.com/en-us/library/ms186778(v=sql.100).aspx

    Abs.


    Eduardo Gomes - http://www.h1solucoes.com.br - Twitter: @edugp_sp

    segunda-feira, 18 de fevereiro de 2013 20:25
  • Wfa,

    Segue uma possibilidade de script:

     

    SELECT

    KCU1

    .CONSTRAINT_NAME AS Contraint

    ,KCU1.TABLE_NAME AS TabelaFilho

    ,KCU1.COLUMN_NAME AS ColunaNaTabelaFilho

    ,KCU2.CONSTRAINT_NAME AS ContraintDeReferenciacao

    ,KCU2.TABLE_NAME AS TabelaPai

    ,KCU2.COLUMN_NAME AS NomeNaTabelaPai

    FROM

    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC

    LEFT

    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1

    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG

    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA

    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME

    LEFT

    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2

    ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG

    AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA

    AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME

    AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    terça-feira, 19 de fevereiro de 2013 19:40
    Moderador

Todas as Respostas

  • WFA.COM, boa tarde.

    Dê uma pesquisada sobre information schema.

    http://msdn.microsoft.com/en-us/library/ms186778(v=sql.100).aspx

    Abs.


    Eduardo Gomes - http://www.h1solucoes.com.br - Twitter: @edugp_sp

    segunda-feira, 18 de fevereiro de 2013 20:25
  • Wfa,

    Segue uma possibilidade de script:

     

    SELECT

    KCU1

    .CONSTRAINT_NAME AS Contraint

    ,KCU1.TABLE_NAME AS TabelaFilho

    ,KCU1.COLUMN_NAME AS ColunaNaTabelaFilho

    ,KCU2.CONSTRAINT_NAME AS ContraintDeReferenciacao

    ,KCU2.TABLE_NAME AS TabelaPai

    ,KCU2.COLUMN_NAME AS NomeNaTabelaPai

    FROM

    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC

    LEFT

    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1

    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG

    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA

    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME

    LEFT

    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2

    ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG

    AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA

    AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME

    AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    terça-feira, 19 de fevereiro de 2013 19:40
    Moderador