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

Question

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.

Answer 1

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

Answer 2

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"

Answer 3

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

Answer 4

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"

Answer 5

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.