主页 / dba / 问题 / 31436
Accepted
Kahn
Asked: 2013-01-05 07:10:15 +0800 CST

SQL Server 2008 - 如何解决快速检查列定义的问题？

  • 772

我为自己开发的另一个工具，关于在包含数百个表的大型数据库中更轻松地创建各种 DML 操作，是一个漂亮的用户定义函数，可以轻松显示有关列的几乎所有相关信息。运行该函数将显示类型、长度、潜在的键约束（FK 或 PK）、潜在的索引以及与该列相关的潜在触发器。

代码发布在下面的答案中。可能会出现功能不起作用的情况，这取决于某些数据库设置或我们项目中不存在的功能。但它应该是相对动态的。

同样，如果您有任何类似的有用提示或建议，请不要犹豫，与我们分享！

sql-server-2008 functions

2 个回答

  1. Best Answer

    运行以下脚本来创建函数：

    
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

    要执行该功能，只需运行以下脚本：

    SELECT * FROM getColDef('TABLENAME.COLUMN_NAME')

    同样，您可以通过执行以下脚本来获取整个表的列定义：

    
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. 我喜欢上面发布的脚本，但我发现非常有用的命令是“Alt + F1”，但在桌子上。

    因此，例如，在新的查询窗口中写入任何表名，突出显示表名并单击 Alt + F1，它会显示表定义，其中包括列定义和索引、表所在的位置等。

    • 0

相关问题