Início / dba / Perguntas / 31436
Accepted
Kahn
Asked: 2013-01-05 07:10:15 +0800 CST

SQL Server 2008 - Como resolvi a necessidade de verificar as definições das colunas rapidamente?

  • 772

Outra ferramenta que desenvolvi para mim, no que diz respeito a facilitar a criação de várias operações DML em um grande banco de dados de centenas de tabelas, é uma função definida pelo usuário bacana para mostrar facilmente quase todas as informações relevantes sobre uma coluna. A execução da função exibirá o tipo, comprimento, restrições de chave em potencial (FK ou PK), índices em potencial, bem como acionadores em potencial vinculados à coluna.

Código postado na resposta abaixo. Pode haver situações em que a função não funcione, dependendo de algumas configurações do banco de dados ou funcionalidades inexistentes em nosso projeto. Mas deve ser relativamente dinâmico.

Novamente, se você tiver alguma dica ou conselho útil semelhante, não hesite em compartilhar!

sql-server-2008 functions

2 respostas

  1. Best Answer

    Execute o seguinte script para criar a função:

    
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'getColDef')
    DROP FUNCTION dbo.getColDef;
GO

CREATE FUNCTION getColDef (@TAB_AND_COL VARCHAR(256))
RETURNS @RESULT TABLE
   ([Table] varchar(256),
    [Column] varchar(256),
    [Type] varchar(64),
    [Length] int,
    [IsNullable] varchar(5),
    [FK_Name] varchar(256),
    [Index_Name] varchar(256),
    [Trigger_Name] varchar(256))
AS
BEGIN
   DECLARE @TNAME VARCHAR(256), @COL VARCHAR(256), @CHARIX INT

    SET @CHARIX = CHARINDEX('.', @TAB_AND_COL)
    SET @TNAME = LEFT(@TAB_AND_COL, CHARINDEX('.',@TAB_AND_COL)-1)
    SET @COL = SUBSTRING(@TAB_AND_COL, @CHARIX + 1, LEN(@TAB_AND_COL) - LEN(@TNAME))

    INSERT INTO @RESULT
        SELECT DISTINCT OBJECT_NAME(c.OBJECT_ID) [Table],c.name [Column],t.name AS [Type],c.max_length AS [Length]
            , CASE C.is_nullable WHEN 1 THEN 'Yes' ELSE 'No' END AS [IsNullable]
            , FK.FK_NAME as FK_Name
            , IDX.IdxName as Index_Name
            , trig.name as Trigger_Name
        FROM sys.columns c
        JOIN sys.types t ON c.user_type_id=t.user_type_id
        LEFT JOIN 
            (SELECT f.name AS FK_NAME
                , OBJECT_NAME(f.parent_object_id) AS FK_TABNAME
            FROM sys.foreign_keys AS f
            INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
            WHERE OBJECT_NAME(f.parent_object_id) = @TNAME
            AND COL_NAME(fc.parent_object_id,fc.parent_column_id) = @COL) FK 
                ON FK.FK_TABNAME = OBJECT_NAME(c.OBJECT_ID)
        LEFT JOIN 
            (SELECT i.Name AS IdxName, OBJECT_NAME(i.object_ID) AS TName, c.Name AS CName
            FROM  sys.indexes i 
            JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
            JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
            WHERE c.name = @COL
            AND OBJECT_NAME(c.object_id) = @TNAME) IDX 
                ON IDX.TName = OBJECT_NAME(c.object_id)
        LEFT JOIN sys.triggers trig ON trig.parent_id = c.object_id
        WHERE C.object_id = object_id(N''+ @TNAME +'') AND c.name = @COL

    RETURN
END

    Para executar a função, basta executar o seguinte script:

    SELECT * FROM getColDef('TABLENAME.COLUMN_NAME')

    Da mesma forma, você pode obter as definições de coluna para uma tabela inteira executando o seguinte script:

    
DECLARE @TABLENAME VARCHAR(256)
SET @TABLENAME = 'TABLENAME' -- Put the name of the table here

DECLARE @RESULTS TABLE([Table] varchar(256),[Column] varchar(256),[Type] varchar(64),[Length] int,[IsNullable] varchar(5)
    ,[FK_Name] varchar(256),[Index_Name] varchar(256),[Trigger_Name] varchar(256))
DECLARE @ROW_NOW INT, @ROW_MAX INT, @COLNAME VARCHAR(256)

SELECT @ROW_NOW = MIN(ROWNUM), @ROW_MAX = MAX(ROWNUM) 
FROM (SELECT ROW_NUMBER() OVER (ORDER BY name) ROWNUM, name FROM sys.columns WHERE OBJECT_NAME(object_id) = @TABLENAME) SRC

WHILE @ROW_NOW <= @ROW_MAX 
BEGIN
    SELECT @COLNAME = name FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY name) ROWNUM, name FROM sys.columns WHERE OBJECT_NAME(object_id) = @TABLENAME) SRC
    WHERE ROWNUM = @ROW_NOW

    INSERT INTO @RESULTS
    SELECT * FROM getColDef(@TABLENAME+'.'+@COLNAME)

    SET @ROW_NOW = @ROW_NOW + 1
END

SELECT * FROM @RESULTS ORDER BY [Column]
    • 1

  2. Eu gosto do script postado acima, mas o comando que achei muito útil é "Alt + F1", mas em uma tabela.

    Por exemplo, escreva qualquer nome de tabela em uma nova janela de consulta, destaque o nome da tabela e clique em Alt + F1 e exibe a definição da tabela que inclui definições de coluna, bem como índices, onde a tabela está localizada, etc.

    • 0

relate perguntas