Exibir o nome da tabela, das colunas e descrição das colunas



   1:  SELECT 
   2:            SO.object_id,
   3:            SO.name AS NomeTabela,
   4:            CAST
   5:            ((SELECT TOP 1
   6:                      VALUE
   7:            FROM sys.fn_listextendedproperty('NomeTabelaTraduzido', 'schema', 'dbo', 'table', SO.name, NULL, NULL) AS fn_listextendedproperty_3)
   8:            AS varchar(MAX))
   9:            AS NomeTabelaTraduzido,
  10:            SC.column_id AS ColunaOrdem,
  11:            SC.name AS NomeColuna,
  12:            CAST
  13:            ((SELECT TOP 1
  14:                      VALUE
  15:            FROM sys.fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', so.name, 'column', sc.name) AS fn_listextendedproperty_2)
  16:            AS varchar(MAX))
  17:            AS DescricaoColuna,
  18:            (SELECT   TOP 1
  19:                      T.name
  20:            FROM sys.foreign_key_columns AS FK
  21:            INNER JOIN sys.tables AS T
  22:                      ON FK.referenced_object_id = T.object_id
  23:            WHERE (FK.parent_object_id = SO.object_id) AND (FK.parent_column_id = SC.column_id))
  24:            AS TabelaRelacionada,
  25:            (SELECT  TOP 1
  26:                      c.name
  27:            FROM sys.foreign_key_columns AS fk
  28:            INNER JOIN sys.columns AS c
  29:                      ON c.column_id = fk.referenced_column_id AND c.object_id = fk.referenced_object_id
  30:            WHERE (fk.parent_object_id = SO.object_id) AND (fk.parent_column_id = SC.column_id))
  31:            AS CampoCondicao,
  32:            CAST
  33:            ((SELECT  TOP 1
  34:                      VALUE
  35:            FROM sys.fn_listextendedproperty('CampoRetorno', 'schema', 'dbo', 'table', so.name, 'column', sc.name) AS fn_listextendedproperty_1)
  36:            AS varchar(MAX))
  37:            AS CampoRetorno
  38:  FROM sys.objects AS SO
  39:  INNER JOIN sys.columns AS SC
  40:            ON SO.object_id = SC.object_id
  41:  WHERE (SO.type = 'U')
  42:  ORDER BY NomeTabela

Comentários

Postagens mais visitadas deste blog

Funcão SQL para abreviar nomes

Função em JavaScript para consulta de CEP com retorno em XML ou Json