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

  • Pergunta

  • 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

    quinta-feira, 4 de setembro de 2014 17:09

Respostas

  • 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

    sexta-feira, 5 de setembro de 2014 18:09

Todas as Respostas

  • 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

    quinta-feira, 4 de setembro de 2014 17:35
  • Pois e... Eu estou tentando a dias unir elas mas ta dificil u.u"
    quinta-feira, 4 de setembro de 2014 17:38
  • 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
    

    quinta-feira, 4 de setembro de 2014 19:23
  • 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

    sexta-feira, 5 de setembro de 2014 18:09