none
Dados de uma tabela RRS feed

  • Pergunta

  • Olá a todos,


    Estou tentando criar uma consulta que me retorne algumas informações de uma tabela do BD. As informaçoes que estou tentando retornar são as seguintes:

    • Nome da Coluna;
    • Tipo do dado (int, dateTime,etc...);
    • tamanho;
    • se permite nulo;
    • e se é alguma CONSTRAINT e se for, qual tipo (PRIMARY KEY, FOREIGN KEY, UNIQUE,...).

    Consegui retornar os quatro primeiros tópicos (Nome, tipo, tamanho e se permite nulo) utilizando a query abaixo, mais o último item acima está me complicando....

    SELECT 
        COLUNAS.NAME AS COLUNA,
        TIPOS.NAME AS TIPO,
        COLUNAS.LENGTH AS TAMANHO,
        COLUNAS.ISNULLABLE AS EH_NULO
     
    FROM 
        SYSOBJECTS AS TABELAS,
        SYSCOLUMNS AS COLUNAS,
        SYSTYPES   AS TIPOS
    WHERE 
        TABELAS.ID = COLUNAS.ID
        AND COLUNAS.xtype = TIPOS.xtype
        AND TABELAS.NAME	= 'funcionario'

    Meu problema é apenas para sanar uma curiosidade pessoal, nada de profissional... sem pressa....
    segunda-feira, 20 de agosto de 2012 13:43

Respostas

  • Consegui uma solução à base de programação... segue código.

    SELECT 
        COLUNAS.NAME AS COLUNA,
        TIPOS.NAME AS TIPO,
        COLUNAS.LENGTH AS TAMANHO,
        COLUNAS.ISNULLABLE AS ALLOW_NULLS,
        TABELAS.NAME as TABELA,
    		case (select COUNT(*) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    			where TABLE_NAME = TABELAS.name and COLUMN_NAME = COLUNAS.name)
    				WHEN 2 THEN 'PRIMARY KEY/FOREIGN KEY'
    				else			
    				(
    					SELECT T.CONSTRAINT_TYPE
    					FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
    					INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME 
    					where T.TABLE_NAME = TABELAS.name
    					AND K.COLUMN_NAME = COLUNAS.NAME
    				)
    	 end 'CONTRAINT TYPE'
    FROM 
        SYSOBJECTS AS TABELAS,
        SYSCOLUMNS AS COLUNAS,
        SYSTYPES   AS TIPOS
    WHERE 
        TABELAS.ID = COLUNAS.ID
        AND COLUNAS.xtype = TIPOS.xtype
        AND TABELAS.NAME = 'RECEBIMENTODECONTAS'
    ORDER BY 'CONTRAINT TYPE' DESC

    ATT.

    • Marcado como Resposta galves.rod terça-feira, 21 de agosto de 2012 16:02
    terça-feira, 21 de agosto de 2012 16:01

Todas as Respostas

  • sp_help 'nome_da_tabela'
    segunda-feira, 20 de agosto de 2012 13:51
  • Precisa que a query me retornasse algo do tipo:

    Claro, a coluna tipo poderia vir com outros valores....

    segunda-feira, 20 de agosto de 2012 15:36
  • Mas a função sp_help justamente te da todas informações sobre a tabela, se a coluna recebe nulo ou não, quais as constraints e seus tipos, tipo de dados , nome e etc...

    [ ]'s


    segunda-feira, 20 de agosto de 2012 15:41
  • Sim, mais o sp_help me mostra vários resultados separados, sendo que eu queria tudo junto em um só resultado....
    segunda-feira, 20 de agosto de 2012 15:53
  • Ola Galves,

       De uma olhada neste link... ele ensina como usar a SYSCONSTRAINTS:

       http://www.java2s.com/Tutorial/SQLServer/0540__System-Tables-Views/ConstraintNamesandSystemCatalogEntries.htm


    Microsoft Community Contributor

    segunda-feira, 20 de agosto de 2012 20:50
    Moderador
  • Foi de grande ajuda, conheci uma tabela do sistema que até então eu não conhecia, mais não resolveu completamente meu problema. Pórem, consegui resolver meu problema utilizando uma subquery, algo simples que não estava enxergando. Segue minha solução caso alguem tenha o mesmo problema.

    SELECT 
        COLUNAS.NAME AS COLUNA,
        TIPOS.NAME AS TIPO,
        COLUNAS.LENGTH AS TAMANHO,
        COLUNAS.ISNULLABLE AS EH_NULO,
        TABELAS.NAME as TABELA,
        (	
    		SELECT T.CONSTRAINT_TYPE
    		FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
    		INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME 
    		where T.TABLE_NAME = TABELAS.name
    		AND K.COLUMN_NAME = COLUNAS.NAME
    	) AS CHAVE
    FROM 
        SYSOBJECTS AS TABELAS,
        SYSCOLUMNS AS COLUNAS,
        SYSTYPES   AS TIPOS
    WHERE 
        TABELAS.ID = COLUNAS.ID
        AND COLUNAS.xtype = TIPOS.xtype
        AND TABELAS.NAME = 'FUNCIONARIO'
    ORDER BY CHAVE DESC
    Retorno:
    • Marcado como Resposta galves.rod terça-feira, 21 de agosto de 2012 13:43
    • Não Marcado como Resposta galves.rod terça-feira, 21 de agosto de 2012 14:37
    terça-feira, 21 de agosto de 2012 13:43
  • Encontrei um problema na solução proposta acima e não consegui resolver este novo problema, por isso desmarquei como resposta e estou novamente pedindo ajuda.

    O script acima funcionou bem em algumas tabelas quando não continha nenhuma constraint do tipo PFK na tabela, pórem, na presença desta chave um erro é gerado na subquery e o SQL retorna o seguinte erro:

    Msg 512, Level 16, State 1, Line 1
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Alguem sabe me ajudar a resolver o erro?



    • Editado galves.rod terça-feira, 21 de agosto de 2012 15:11
    terça-feira, 21 de agosto de 2012 14:48
  • Consegui uma solução à base de programação... segue código.

    SELECT 
        COLUNAS.NAME AS COLUNA,
        TIPOS.NAME AS TIPO,
        COLUNAS.LENGTH AS TAMANHO,
        COLUNAS.ISNULLABLE AS ALLOW_NULLS,
        TABELAS.NAME as TABELA,
    		case (select COUNT(*) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    			where TABLE_NAME = TABELAS.name and COLUMN_NAME = COLUNAS.name)
    				WHEN 2 THEN 'PRIMARY KEY/FOREIGN KEY'
    				else			
    				(
    					SELECT T.CONSTRAINT_TYPE
    					FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
    					INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME 
    					where T.TABLE_NAME = TABELAS.name
    					AND K.COLUMN_NAME = COLUNAS.NAME
    				)
    	 end 'CONTRAINT TYPE'
    FROM 
        SYSOBJECTS AS TABELAS,
        SYSCOLUMNS AS COLUNAS,
        SYSTYPES   AS TIPOS
    WHERE 
        TABELAS.ID = COLUNAS.ID
        AND COLUNAS.xtype = TIPOS.xtype
        AND TABELAS.NAME = 'RECEBIMENTODECONTAS'
    ORDER BY 'CONTRAINT TYPE' DESC

    ATT.

    • Marcado como Resposta galves.rod terça-feira, 21 de agosto de 2012 16:02
    terça-feira, 21 de agosto de 2012 16:01