Como fazer um select com um levantamento geral do BANCO-TABELA-ATRIBUTOS

Question

Boa tarde,  estou com dificuldades em montar um select que me tragam algumas informações em apenas um select. Em perguntas anteriores que ja fiz sobre o mesmo assunto, o pessoal respondia e depois desaparecia, apesar da resposta ter contribuído porem não finalizou o meu problema.

preciso juntar as seguintes informacoes:

ID DA BASE;

NOME DA DATABASE;

DESCRICAO DA BASE;

ID DA TABELA;

NOME DA TABELA;

DESCRICAO DA TABELA;

INDEX DA CAMPO;

NOME DA CAMPO;

DESCRICAO DO CAMPO;

TIPO DE DADO(INT, VARCHAR...);

SE ACEITA NULL;

SE É PK;

SE É UNIQUE;

SE POSSUI CONSTRAINT;

SE É FK;

SE FOR FK PARA QUAL TABELA REFERENCIA;

SE FOR FK PARA QUAL CAMPO REFERENCIA; 

Agradeço se alguém puder me ajudar a resolver meu problema...

Segue ate onde cheguei 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,
  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

Answer 1

Fabiano,

Existem varias views no SQL Server no schema [INFORMATION_SCHEMA]

Da uma olhada nelas, você irá encontrar o que esta querendo.

Exemplo: select * from [INFORMATION_SCHEMA].[COLUMNS]

---

Tulio Rosa | http://tuliorosa.com.br | Se resolveu seu problema, marque como resposta ou vote

Answer 2

Pois e... Eu estou tentando a dias unir elas mas ta dificil u.u"

Answer 3

Em paralelo ao select postado acima, fiz outro para tentar ir por outro caminho, mas nesse não consigo pegar o id da BASE, da tabela , nem a descricao (propriedade extentida) delas e no caso das que forem FK, para quais tabela e campo fazem referencia. Alguém ajuda porfavor!

select 
col.TABLE_CATALOG, col.TABLE_NAME, col.ORDINAL_POSITION, col.COLUMN_NAME, col.IS_NULLABLE
, ( case (select SUBSTRING(CONSTRAINT_NAME,1,2) from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where col.TABLE_NAME = TABLE_NAME and col.COLUMN_NAME =  COLUMN_NAME and SUBSTRING(CONSTRAINT_NAME,1,2) = 'PK')
 when 'PK' then 'true' else '' end
) as 'Primarykey'
, ( case (select SUBSTRING(CONSTRAINT_NAME,1,2) from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where col.TABLE_NAME = TABLE_NAME and col.COLUMN_NAME =  COLUMN_NAME and SUBSTRING(CONSTRAINT_NAME,1,2) = 'UK')
 when 'UK' then 'true' else '' end
) as 'Unique key'
, ( case (select SUBSTRING(CONSTRAINT_NAME,1,2) from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where col.TABLE_NAME = TABLE_NAME and col.COLUMN_NAME =  COLUMN_NAME and SUBSTRING(CONSTRAINT_NAME,1,2) = 'CT')
 when 'CT' then 'true' else '' end
) as 'Constraint'
, ( case (select SUBSTRING(CONSTRAINT_NAME,1,2) from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where col.TABLE_NAME = TABLE_NAME and col.COLUMN_NAME =  COLUMN_NAME and SUBSTRING(CONSTRAINT_NAME,1,2) = 'FK')
 when 'FK' then 'true' else '' end
) as 'Foreing Key'
from INFORMATION_SCHEMA.COLUMNS as col (nolock)
order by col.TABLE_NAME

Answer 4

Acabei conseguindo, tenho certeza que deve haver algum modo mais fácil que esse mas por hora é esse mesmo.

Segue o T-SQL, para os demais que precisarem:

WITH DICIONARIO_DADOS
AS (SELECT
  USER_NAME(obj.uid) AS UserName,
  (SELECT
    name
  FROM sys.extended_properties
  WHERE class = 0)
  AS 'BASE',
  (SELECT
    value
  FROM sys.extended_properties
  WHERE class = 0)
  AS 'BASEDESCRICAO',
  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 'TabelaDescricao',
  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,
  CONVERT(varchar, pro.value) AS 'ColunaDescricao',
  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),
REFERENCIAS
AS (SELECT
  col.TABLE_CATALOG,
  col.TABLE_NAME,
  col.ORDINAL_POSITION,
  col.COLUMN_NAME,
  col.IS_NULLABLE,
  (CASE (SELECT
    SUBSTRING(CONSTRAINT_NAME, 1, 2)
  FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
  WHERE col.TABLE_NAME = TABLE_NAME
  AND col.COLUMN_NAME = COLUMN_NAME
  AND SUBSTRING(CONSTRAINT_NAME, 1, 2) = 'PK')
    WHEN 'PK' THEN 'true'
    ELSE null
  END
  ) AS 'Primarykey',
  (CASE (SELECT
    SUBSTRING(CONSTRAINT_NAME, 1, 2)
  FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
  WHERE col.TABLE_NAME = TABLE_NAME
  AND col.COLUMN_NAME = COLUMN_NAME
  AND SUBSTRING(CONSTRAINT_NAME, 1, 2) = 'UK')
    WHEN 'UK' THEN 'true'
    ELSE null
  END
  ) AS 'UniqueKey',
  (CASE (SELECT
    SUBSTRING(CONSTRAINT_NAME, 1, 2)
  FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
  WHERE col.TABLE_NAME = TABLE_NAME
  AND col.COLUMN_NAME = COLUMN_NAME
  AND SUBSTRING(CONSTRAINT_NAME, 1, 2) = 'CT')
    WHEN 'CT' THEN 'true'
    ELSE null
  END
  ) AS 'Constrain',
  (CASE (SELECT
    SUBSTRING(CONSTRAINT_NAME, 1, 2)
  FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
  WHERE col.TABLE_NAME = TABLE_NAME
  AND col.COLUMN_NAME = COLUMN_NAME
  AND SUBSTRING(CONSTRAINT_NAME, 1, 2) = 'FK')
    WHEN 'FK' THEN 'true'
    ELSE null
  END
  ) AS 'ForeingKey'
FROM INFORMATION_SCHEMA.COLUMNS AS col (NOLOCK))
SELECT
  TABLE_CATALOG,
  basedescricao,
  TableID,
  TABLE_NAME,
  TabelaDescricao,
  ColumnOrder,
  COLUMN_NAME,
  ColunaDescricao,
  IsNullable,
  Primarykey,
  UniqueKey,
  Constrain,
  ForeingKey,
  (CASE Foreingkey
    WHEN 'true' THEN (SELECT
        objj.name AS TabelaPK
      FROM sys.foreign_key_columns AS fkc
      INNER JOIN sys.objects AS obj
        ON fkc.parent_object_id = obj.object_id
      INNER JOIN sys.objects AS objj
        ON fkc.referenced_object_id = objj.object_id
      INNER JOIN sys.objects AS objcts
        ON fkc.constraint_object_id = objcts.object_id
      WHERE TABLE_NAME = obj.name
      AND COLUMN_NAME = (SELECT
        name
      FROM syscolumns
      WHERE colid = fkc.parent_column_id
      AND id = fkc.parent_object_id))
  END) AS TabelaReferenciaFK,

  (CASE Foreingkey
    WHEN 'true' THEN (SELECT (SELECT
          name
        FROM syscolumns
        WHERE colid = fkc.referenced_column_id
        AND id = fkc.referenced_object_id)
        AS ColunaPKdaFK
      FROM sys.foreign_key_columns AS fkc
      INNER JOIN sys.objects AS obj
        ON fkc.parent_object_id = obj.object_id
      INNER JOIN sys.objects AS objj
        ON fkc.referenced_object_id = objj.object_id
      INNER JOIN sys.objects AS objcts
        ON fkc.constraint_object_id = objcts.object_id
      WHERE TABLE_NAME = obj.name
      AND COLUMN_NAME = (SELECT
        name
      FROM syscolumns
      WHERE colid = fkc.parent_column_id
      AND id = fkc.parent_object_id))
  END) AS TabelaReferenciaFKa


FROM DICIONARIO_DADOS
INNER JOIN REFERENCIAS
  ON BASE = TABLE_CATALOG
  AND TableName = TABLE_NAME
  AND FieldName = COLUMN_NAME
  --where constrain <> NULL
  order by table_name