locked
Query para recuperar estrutura da tabela RRS feed

  • Pergunta

  • Olá pessoal,

    Primeiramente informo que conheço muito pouco de querys para BD.

    Estou precisando de uma query que me retorne o seguinte:
    -Nome da Tabela
    -Nome do campo
    -Tipo do campo
    -Tamanho do campo
    -Se o campo aceita NULL
    -Se o campo é Identity
    -Se o campo faz parte da chave primaria
    -Valor default do campo
    -Descrição do campo
    -E outras informações relacionadas ao campo (não necessário)

    Pesquisando, encontrei duas querys que quase comtempla as minhas
    necessidades, mas precisa unir para que fique em apenas uma query

    ----------------------------------------------------------

    SELECT T.name AS table_name,
    C.name AS column_name,
    S.name AS data_type,
    C.precision AS precision,
    C.scale AS scale,
    CASE WHEN S.name IN ('nvarchar', 'nchar')
    THEN C.max_length/2
    ELSE C.max_length
    END AS column_length,
    CASE WHEN C.is_nullable = 1
    THEN 'Y'
    ELSE 'N'
    END AS allow_null,
    CASE WHEN C.is_identity = 1
    THEN 'Y'
    ELSE 'N'
    END AS identity_column,
    CASE WHEN C.is_computed = 1
    THEN 'Y'
    ELSE 'N'
    END AS computed_column,
    D.definition AS default_value

    FROM sys.tables AS T
    JOIN sys.columns AS C
    ON T.object_id = C.object_id
    JOIN sys.types AS S
    ON C.user_type_id = S.user_type_id
    LEFT OUTER JOIN sys.default_constraints AS D
    ON C.object_id = D.parent_object_id
    AND C.column_id = D.parent_column_id

    WHERE T.type = 'U';

    ----------------------------------------------------------

    SELECT sC.Name
    FROM sysindexkeys sI
    left JOIN syscolumns sC
    on sC.colid = sI.colId
    and sC.Id = (select Id from sysobjects where name = 'nomeDaTabela')
    WHERE sI.id = (select Id from sysobjects where name = 'nomeDaTabela')

    ----------------------------------------------------------

    Se alguem puder dar uma ajuda, ficarei muito grato

    Abs,

    segunda-feira, 12 de maio de 2008 17:49

Respostas

  •  

    Consegui assim, mas daí tem que testar a tabela no Where... se alguem conseguir melhorar o código, beleza

     

    SELECT sys.tables.name AS table_name, sys.columns.name AS column_name, sys.types.name AS data_type,

    sys.columns .precision AS precision, sys.columns.scale AS scale,

    CASE WHEN sys.types.name

    IN ('nvarchar', 'nchar') THEN sys.columns.max_length/2 ELSE sys.columns.max_length END AS column_length,

    CASE WHEN sys.columns.is_nullable = 1 THEN 'Y' ELSE 'N' END AS allow_null,

    CASE WHEN sys.columns.is_identity = 1 THEN 'Y' ELSE 'N' END AS identity_column,

    CASE WHEN sys.columns.is_computed = 1 THEN 'Y' ELSE 'N' END AS computed_column,

    sys.default_constraints.definition AS default_value,

    CASE WHEN sys.columns.name

    IN (SELECT syscolumns.Name FROM sysindexkeys left JOIN syscolumns ON syscolumns.colid =

    sysindexkeys.colId and syscolumns.Id = (SELECT Id FROM sysobjects WHERE name = sys.tables.name) WHERE

    sysindexkeys.id = (SELECT Id FROM sysobjects WHERE name = sys.tables.name)) THEN 'Y' ELSE 'N' END

    AS PrimaryKey

    FROM sys.tables JOIN sys.columns ON sys.tables.object_id = sys.columns.object_id JOIN sys.types

    ON sys.columns.user_type_id = sys.types.user_type_id LEFT OUTER JOIN sys.default_constraints ON

    sys.columns.object_id = sys.default_constraints.parent_object_id

    AND sys.columns.column_id = sys.default_constraints.parent_column_id

    WHERE sys.tables.type = 'U' AND sys.tables.name = '[NOME DA TABELA]'

    ORDER BY sys.tables.name, sys.columns.column_id

    segunda-feira, 12 de maio de 2008 18:36

Todas as Respostas

  •  

    Consegui assim, mas daí tem que testar a tabela no Where... se alguem conseguir melhorar o código, beleza

     

    SELECT sys.tables.name AS table_name, sys.columns.name AS column_name, sys.types.name AS data_type,

    sys.columns .precision AS precision, sys.columns.scale AS scale,

    CASE WHEN sys.types.name

    IN ('nvarchar', 'nchar') THEN sys.columns.max_length/2 ELSE sys.columns.max_length END AS column_length,

    CASE WHEN sys.columns.is_nullable = 1 THEN 'Y' ELSE 'N' END AS allow_null,

    CASE WHEN sys.columns.is_identity = 1 THEN 'Y' ELSE 'N' END AS identity_column,

    CASE WHEN sys.columns.is_computed = 1 THEN 'Y' ELSE 'N' END AS computed_column,

    sys.default_constraints.definition AS default_value,

    CASE WHEN sys.columns.name

    IN (SELECT syscolumns.Name FROM sysindexkeys left JOIN syscolumns ON syscolumns.colid =

    sysindexkeys.colId and syscolumns.Id = (SELECT Id FROM sysobjects WHERE name = sys.tables.name) WHERE

    sysindexkeys.id = (SELECT Id FROM sysobjects WHERE name = sys.tables.name)) THEN 'Y' ELSE 'N' END

    AS PrimaryKey

    FROM sys.tables JOIN sys.columns ON sys.tables.object_id = sys.columns.object_id JOIN sys.types

    ON sys.columns.user_type_id = sys.types.user_type_id LEFT OUTER JOIN sys.default_constraints ON

    sys.columns.object_id = sys.default_constraints.parent_object_id

    AND sys.columns.column_id = sys.default_constraints.parent_column_id

    WHERE sys.tables.type = 'U' AND sys.tables.name = '[NOME DA TABELA]'

    ORDER BY sys.tables.name, sys.columns.column_id

    segunda-feira, 12 de maio de 2008 18:36
  • Olá,

     

    Não acho muito interessante ficar consultando diretamente as views de catálogo. Qualquer Service Pack pode alterar essas views e "quebrar" o comportamento delas. Procure pelas views information_schema (no banco MASTER). Ex:

     

    SELECT * FROM Information_Schema.COLUMNS

     

    Você pode combinar algumas poucas views e encontrar essas respostas mais facilmente. Se preferir em forma de SP. Tente:

     

    sp_help 'tabela'

     

    [ ]s,

     

    Gustavo

     

    segunda-feira, 12 de maio de 2008 19:45
  • Olá,

     

    Quero agradecer a ajuda do Anderson e do Gustavo.

    Segui o modelo do Anderson, implementando a captura de mais uma campo "Description" com a ajuda de um outro amigo "Ulisses".

    Concordo com o posicionamento do Gustavo, mas o exemplo do Anderson veio a contemplar as minhas necessidades.

    Estou utilizando o resultado desta query para gerar classe de persistência de dados, então se as views de catálogo forem alteradas em decorrência de alguma Service Pack, basta eu alterar a string de comando.

     

    A query ficou da seguinte forma:

     

    SELECT sys.tables.name AS table_name,

    sys.columns.name AS column_name,

    sys.types.name AS data_type,

    sys.columns .precision AS precision, sys.columns.scale AS scale,

    CASE WHEN sys.types.name IN ('nvarchar', 'nchar')

    THEN sys.columns.max_length/2

    ELSE sys.columns.max_length

    END AS column_length,

    CASE WHEN sys.columns.is_nullable = 1

    THEN 'Y'

    ELSE 'N'

    END AS allow_null,

    CASE WHEN sys.columns.is_identity = 1

    THEN 'Y'

    ELSE 'N'

    END AS identity_column,

    CASE WHEN sys.columns.is_computed = 1

    THEN 'Y'

    ELSE 'N'

    END AS computed_column,

    sys.default_constraints.definition AS default_value,

    CASE WHEN sys.columns.name IN (SELECT syscolumns.Name FROM sysindexkeys left JOIN syscolumns ON syscolumns.colid = sysindexkeys.colId and syscolumns.Id = (SELECT Id FROM sysobjects WHERE name = sys.tables.name) WHERE sysindexkeys.id = (SELECT Id FROM sysobjects WHERE name = sys.tables.name))

    THEN 'Y'

    ELSE 'N'

    END AS primary_key,

    sys.extended_properties.value as description

    FROM sys.tables JOIN sys.columns

    ON sys.tables.object_id = sys.columns.object_id JOIN sys.extended_properties

    ON sys.extended_properties.major_id = sys.tables.object_id AND sys.extended_properties.minor_id = sys.columns.column_id JOIN sys.types

    ON sys.columns.user_type_id = sys.types.user_type_id LEFT OUTER JOIN sys.default_constraints

    ON sys.columns.object_id = sys.default_constraints.parent_object_id AND sys.columns.column_id = sys.default_constraints.parent_column_id

    WHERE sys.tables.type = 'U'

    ORDER BY sys.tables.name, sys.columns.column_id

     

    ------------------------------------------------------------------------------------------------------

     

    obrigado a todos,

     

     

    terça-feira, 13 de maio de 2008 11:11
  • Pedro,

     

    Concordo com o Gustavo, em relação a utilização das systems views, pois periodicamente a Microsoft disponibiliza novas atualizações e alterações na estrutura dos componentes.

     

    Se pensarmos que todas as vezes que o catálogo de dados existentes no SQL Server for alterado, devemos alterar a nossa estrutura de dados, desta forma, acredito que fique mais complicado para trabalhar.

    terça-feira, 13 de maio de 2008 11:46
  •  

    que bom que foi útil pra ti, apesar de não ser o ideal... mas marca a minha resposta como certa então... Smile
    terça-feira, 13 de maio de 2008 11:49
  • Junior,

     

    Veja eu concordo com vocês em partes.

     

    Acredito que a utilização das system views, pode trazer problemas desde que o o sistema seja todo fundamentado em consultas que acesse estas views para executar suas rotinas.

     

    Mas eu utilizo o SQL desde a versão 6.5, e atualmente estou usando a versão 2005 na produção e testando a versão 2008. Neste período passei por várias alterações na estrutura do SQL.

     

    A Microsoft não muda simplemente a estrutura destas views, ela sinaliza nas versões mais recentes que uma ou outra consulta pode deixar de funcionar, ou que será modificada a forma de tratamento, isso não funciona apenas para system view mas para toda estrutura de seus produtos.

     

    Outro ponto relevante de ser mencionando é que mesmo com todas as alterações sofridas podemos notar que a Microsoft se preocupa em manter a compatibilidade  com as versões anteriores, haja visto a Function sp_Help(sys.sp_help), que se você tiver a curiosidade de olhar sua estrutura, traz na linha 192 o seguinte comentário.

    -- VIEWS DONT HAVE CONSTRAINTS, BUT PRINT THESE MESSAGES BECAUSE 6.5 DID

    Ou seja a parte de baixo desta Function só é executada por na versão 6.5 as Views suportam Constraints.

     

    Uma das mudança do SQL 2000 para SQL 2005 que lembro e que causaram problemas para os nossos sistemas (Hoje sou responsável por 4DB, na somatória são mais de 5000 procedures), foi a forma de escrever os Outer Joins:

    Antes SQL 2000 ou <
    nós utilizavamos a seguinte forma:
    Select *
       from Table001 a

            , Table002 b
     where a.Column *= b.Column


    Atualmente na versão SQL 2005 tivemos que reescrever todas as procedures que utilizavam a forma de Outer Joins descrita acima para forma abaixo:
    Select *

       from Table001 a

              letf outer join Table002 a on a.Column = b.Column

     

    Bom o que pretendo dizer com isso.

    É que nós Analistas/DBA's/Arquitetos de sistemas/Desenvolvedores, etc. estamos utilizando ferramentas desenvolvidas pela Microsoft e que estamos sugeitos as todos os tipos de alterações que eles distribuam em seus services packs, mas que isso não pode inibir a utilização de nenhuma das facilidades que eles nos ofereçam(System Views), mas sim utilizar de forma consiente, e sempre estar atualizado para que se necessário, reestrutar nossos sistemas de forma rápida e eficiente quando alguma destas atulizações mudar o comportamento de qualquer que seja a parte estrutural do SQL.

     

     

    Wander Junior

     

     

    sábado, 17 de maio de 2008 17:36