none
Como posso identificar se um campo é PK-FK-UNIQUE junto com sua referencia sua referencia? RRS feed

  • Pergunta

  • Boa noite,

    Estou levantando boa parte das informações de uma base através de um select, informações como:

    Database;

    Descricao da base;

    Id tabela;

    nome tabela;

    descricao da tabela;

    campo da tabela;

    index dele na tabela;

    tipo do atributo;

    descricao do campo;

    aceita nulos;

    Se o campo é PK, FK, UNIQUE;

    Porem estou apanhando na ultima parte, em identificar se cada campo daquela tabela e PK ou FK ou UNIQUE OU a combinacao de algum deles (PK e FK por exemplo) OU mesmo nenhum deles

    Segue o que consegui fazer ate o momento:

    SELECT
      USER_NAME(obj.uid) AS UserName,
      (SELECT
        name
      FROM sys.extended_properties
      WHERE class = 0)
      AS 'DATABASE',
      (SELECT
        value
      FROM sys.extended_properties
      WHERE class = 0)
      AS 'DATABASE',
      obj.id AS TableID,
      obj.name AS TableName,
      (SELECT TOP 1
        value
      FROM sys.extended_properties
      WHERE major_id = obj.id
      AND minor_id = 0)
      AS 'Descricao Tabela',
      col.name AS FieldName,
      col.colorder AS ColumnOrder,
      TYPE_NAME(col.xusertype) +
      CASE
        WHEN TYPE_NAME(col.xusertype) = 'varchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')'
        WHEN TYPE_NAME(col.xusertype) = 'nvarchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')'
        WHEN TYPE_NAME(col.xusertype) = 'char' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')'
        WHEN TYPE_NAME(col.xusertype) = 'nchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')'
        ELSE CONVERT(varchar, '')
      END AS DataType,
      -- CASE WHEN index_col(obj.name, 1, col.colorder) != '' THEN 'Yes' ELSE 'No' END AS IsPK,  -- errado ex: cd32
      CONVERT(varchar, pro.value) AS 'Descricao Atributo',
      CASE col.IsNullable
        WHEN 1 THEN 'Yes'
        ELSE 'No'
      END AS IsNullable
    
    FROM sysobjects obj
    INNER JOIN syscolumns col
      ON obj.id = col.id
    LEFT JOIN sysobjects def
      ON obj.id = def.parent_obj
      AND def.info = col.colorder
      AND def.xtype IN ('D ')
    LEFT JOIN syscomments com
      ON def.id = com.id
    LEFT JOIN sys.extended_properties pro
      ON obj.id = pro.major_id
      AND col.colorder = pro.minor_id
    LEFT JOIN sys.extended_properties prot
      ON prot.major_id = obj.id
      AND prot.minor_id = 0
    WHERE obj.xtype = 'U'
    AND obj.name != 'dtproperties'
    AND obj.id <> 1563152614
    ORDER BY OBJECT_NAME(obj.id), col.colorder

    Desde já, agradecido.

    segunda-feira, 1 de setembro de 2014 21:39

Respostas

  • Fabiano,

    Se você nomeia às Constraints com os prefixos: "PK_" (para Primary Key), "FK_" (para Foreign Key) e "UK_" (para "Unique Key") ou mesmo algo semelhante a isso, então utilize a view "INFORMATION_SCHEMA.KEY_COLUMN_USAGE" relacionando o nome da tabela para obter quais campos são "chaves". Você pode utilizar o nome da Constraint para identificar de qual chave cada campo pertence.

    Veja uma adaptação do seu script T-SQL abaixo com esta view
    (fique à vontade para adaptar às suas necessidades):

    SELECT
      USER_NAME(obj.uid) AS UserName,
      (SELECT
        name
      FROM sys.extended_properties
      WHERE class = 0)
      AS 'DATABASE',
      (SELECT
        value
      FROM sys.extended_properties
      WHERE class = 0)
      AS 'DATABASE',
      obj.id AS TableID,
      obj.name AS TableName,
      (SELECT TOP 1
        value
      FROM sys.extended_properties
      WHERE major_id = obj.id
      AND minor_id = 0)
      AS 'Descricao Tabela',
      col.name AS FieldName,
      col.colorder AS ColumnOrder,
      TYPE_NAME(col.xusertype) +
      CASE
        WHEN TYPE_NAME(col.xusertype) = 'varchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')'
        WHEN TYPE_NAME(col.xusertype) = 'nvarchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')'
        WHEN TYPE_NAME(col.xusertype) = 'char' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')'
        WHEN TYPE_NAME(col.xusertype) = 'nchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')'
        ELSE CONVERT(varchar, '')
      END AS DataType,
      -- CASE WHEN index_col(obj.name, 1, col.colorder) != '' THEN 'Yes' ELSE 'No' END AS IsPK,  -- errado ex: cd32
      CONVERT(varchar, pro.value) AS 'Descricao Atributo',
      CASE col.IsNullable
        WHEN 1 THEN 'Yes'
        ELSE 'No'
      END AS IsNullable 
      ,CHV.CONSTRAINT_NAME
    
    FROM sysobjects obj
    INNER JOIN syscolumns col
      ON obj.id = col.id
    LEFT JOIN sysobjects def
      ON obj.id = def.parent_obj
      AND def.info = col.colorder
      AND def.xtype IN ('D ')
    LEFT JOIN syscomments com
      ON def.id = com.id
    LEFT JOIN sys.extended_properties pro
      ON obj.id = pro.major_id
      AND col.colorder = pro.minor_id
    LEFT JOIN sys.extended_properties prot
      ON prot.major_id = obj.id
      AND prot.minor_id = 0
    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CHV 
    ON CHV.TABLE_NAME = obj.name
    WHERE obj.xtype = 'U'
    AND obj.name != 'dtproperties'
    AND obj.id <> 1563152614
    ORDER BY OBJECT_NAME(obj.id), col.colorder


    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    • Marcado como Resposta Fabianomaso terça-feira, 2 de setembro de 2014 13:29
    terça-feira, 2 de setembro de 2014 12:15
    Moderador
  • Por coincidência eu já estava tentando fazer com a [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] apesar de não ter conseguido desenrolar ate o momento a situação sozinho.

    Agradecido pela resposta de ambos. Agradecido pelo select Durval Ramos, se puder me ajudar com mais este complemento agradeço. As FKS, gostaria de identificar a Tabela e o campo da sua PK

    Fabiano,

    O ajuste na consulta, como eu indiquei não atende sua necessidade?

    Veja no print-screen a execução deste T-SQL. As colunas que não fazem parte de nenhuma chave retornam "NULL" e quando compõe alguma Constraint exibe o nome da Constraint.

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    • Marcado como Resposta Fabianomaso terça-feira, 2 de setembro de 2014 13:29
    terça-feira, 2 de setembro de 2014 13:00
    Moderador

Todas as Respostas

  • Fabiano,

    Essa informação você irá encontrar na views [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]

    Na verdade, acho que para fazer isso que você esta querendo seria melhor usar as views do esquema [INFORMATION_SCHEMA]

    select * from [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]


    Tulio Rosa | http://tuliorosa.com.br

    terça-feira, 2 de setembro de 2014 11:50
  • Fabiano,

    Se você nomeia às Constraints com os prefixos: "PK_" (para Primary Key), "FK_" (para Foreign Key) e "UK_" (para "Unique Key") ou mesmo algo semelhante a isso, então utilize a view "INFORMATION_SCHEMA.KEY_COLUMN_USAGE" relacionando o nome da tabela para obter quais campos são "chaves". Você pode utilizar o nome da Constraint para identificar de qual chave cada campo pertence.

    Veja uma adaptação do seu script T-SQL abaixo com esta view
    (fique à vontade para adaptar às suas necessidades):

    SELECT
      USER_NAME(obj.uid) AS UserName,
      (SELECT
        name
      FROM sys.extended_properties
      WHERE class = 0)
      AS 'DATABASE',
      (SELECT
        value
      FROM sys.extended_properties
      WHERE class = 0)
      AS 'DATABASE',
      obj.id AS TableID,
      obj.name AS TableName,
      (SELECT TOP 1
        value
      FROM sys.extended_properties
      WHERE major_id = obj.id
      AND minor_id = 0)
      AS 'Descricao Tabela',
      col.name AS FieldName,
      col.colorder AS ColumnOrder,
      TYPE_NAME(col.xusertype) +
      CASE
        WHEN TYPE_NAME(col.xusertype) = 'varchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')'
        WHEN TYPE_NAME(col.xusertype) = 'nvarchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')'
        WHEN TYPE_NAME(col.xusertype) = 'char' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')'
        WHEN TYPE_NAME(col.xusertype) = 'nchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')'
        ELSE CONVERT(varchar, '')
      END AS DataType,
      -- CASE WHEN index_col(obj.name, 1, col.colorder) != '' THEN 'Yes' ELSE 'No' END AS IsPK,  -- errado ex: cd32
      CONVERT(varchar, pro.value) AS 'Descricao Atributo',
      CASE col.IsNullable
        WHEN 1 THEN 'Yes'
        ELSE 'No'
      END AS IsNullable 
      ,CHV.CONSTRAINT_NAME
    
    FROM sysobjects obj
    INNER JOIN syscolumns col
      ON obj.id = col.id
    LEFT JOIN sysobjects def
      ON obj.id = def.parent_obj
      AND def.info = col.colorder
      AND def.xtype IN ('D ')
    LEFT JOIN syscomments com
      ON def.id = com.id
    LEFT JOIN sys.extended_properties pro
      ON obj.id = pro.major_id
      AND col.colorder = pro.minor_id
    LEFT JOIN sys.extended_properties prot
      ON prot.major_id = obj.id
      AND prot.minor_id = 0
    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CHV 
    ON CHV.TABLE_NAME = obj.name
    WHERE obj.xtype = 'U'
    AND obj.name != 'dtproperties'
    AND obj.id <> 1563152614
    ORDER BY OBJECT_NAME(obj.id), col.colorder


    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    • Marcado como Resposta Fabianomaso terça-feira, 2 de setembro de 2014 13:29
    terça-feira, 2 de setembro de 2014 12:15
    Moderador
  • Por coincidência eu já estava tentando fazer com a [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] apesar de não ter conseguido desenrolar ate o momento a situação sozinho.

    Agradecido pela resposta de ambos. Agradecido pelo select Durval Ramos, se puder me ajudar com mais este complemento agradeço. As FKS, gostaria de identificar a Tabela e o campo da sua PK

    terça-feira, 2 de setembro de 2014 12:41
  • Por coincidência eu já estava tentando fazer com a [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] apesar de não ter conseguido desenrolar ate o momento a situação sozinho.

    Agradecido pela resposta de ambos. Agradecido pelo select Durval Ramos, se puder me ajudar com mais este complemento agradeço. As FKS, gostaria de identificar a Tabela e o campo da sua PK

    Fabiano,

    O ajuste na consulta, como eu indiquei não atende sua necessidade?

    Veja no print-screen a execução deste T-SQL. As colunas que não fazem parte de nenhuma chave retornam "NULL" e quando compõe alguma Constraint exibe o nome da Constraint.

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    • Marcado como Resposta Fabianomaso terça-feira, 2 de setembro de 2014 13:29
    terça-feira, 2 de setembro de 2014 13:00
    Moderador
  • Atende sim amigo, é porque eu esqueci de mencionar no inicio da pergunta que nos campos que forem FKS, eu precisaria identificar a tabela e o campo ao qual estão relacionados.

    Utilizando o seu print como exemplo, aquele seu 3 registro, ele e uma FK, eu precisaria identificar para qual tabela e qual campo ela esta referenciando. Agradeço se puder ajudar.

    terça-feira, 2 de setembro de 2014 13:06