如何在 SQL Server 2008 数据库中查找未使用的列。

Question

给定一组表，需要查找其中值为空/Null/空格或0的列。

假设我能从中央系统表中提取出表和列的定义，请问如何检查上述条件？

- user2026061

Answer 1

SQL Server没有特殊的程序来进行这种判断。您需要查询每张表。以下是一种暴力解决方案：

If object_id('tempdb..#Results') is not null
    Drop Table #Results;
GO
Create Table #Results
    (
    TableSchema sysname not null
    , TableName sysname not null
    , ColumnName sysname not null
    );
GO

Declare @TableSchema sysname;
Declare @TableName sysname;
Declare @ColumnName sysname;
Declare @DataType sysname;
Declare @Columns Cursor;
Declare @BaseSql nvarchar(max);
Declare @Sql nvarchar(max);
Declare @AdditionalFilter nvarchar(max);

Set @BaseSql = 'Insert #Results( TableSchema, TableName, ColumnName )
                Select ''TABLE_SCHEMA'', ''TABLE_NAME'', ''COLUMN_NAME''
                From ( Select 1 As V ) As Z
                Where Not Exists    (
                                    Select 1
                                    From [TABLE_SCHEMA].[TABLE_NAME]
                                    Where [COLUMN_NAME] Is Not Null
                                        ADDITIONAL_FILTER
                                    )';

Set @Columns = Cursor Fast_Forward For
    Select C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE
    From INFORMATION_SCHEMA.COLUMNS As C
        Left Join INFORMATION_SCHEMA.VIEWS As V
            On V.TABLE_SCHEMA = C.TABLE_SCHEMA
                And V.TABLE_NAME = C.TABLE_NAME
    Where V.TABLE_NAME Is Null;

Open @Columns;
Fetch Next From @Columns Into @TableSchema, @TableName, @ColumnName, @DataType;

While @@Fetch_Status = 0
Begin
    If @DataType In('int','smallint','tinyint','bigint','numeric','bit','decimal','money','smallmoney','float','real')
        Set @AdditionalFilter = 'And [COLUMN_NAME] <> 0';
    Else If @DataType In('char','nchar','varchar','nvarchar','text','ntext')
        Set @AdditionalFilter = 'And Len([COLUMN_NAME]) > 0';
    Else
        Set @AdditionalFilter = '';

    Set @Sql = Replace(@BaseSql, 'ADDITIONAL_FILTER', @AdditionalFilter);
    Set @Sql = Replace(@Sql, 'TABLE_SCHEMA', @TableSchema);
    Set @Sql = Replace(@Sql, 'TABLE_NAME', @TableName);
    Set @Sql = Replace(@Sql, 'COLUMN_NAME', @ColumnName);

    --Print @Sql
    Exec(@Sql)
    Fetch Next From @Columns Into @TableSchema, @TableName, @ColumnName, @DataType;
End

Close @Columns;
Deallocate @Columns;

Select *
From #Results

以上解决方案的一个小问题是，任何一张空表中的列都会被返回。如果你想要排除空表，只需将查询调整为以下内容：

Set @BaseSql = 'Insert #Results( TableSchema, TableName, ColumnName )
                Select ''TABLE_SCHEMA'', ''TABLE_NAME'', ''COLUMN_NAME''
                From ( Select 1 As V ) As Z
                Where Exists    (
                                Select 1
                                From [TABLE_SCHEMA].[TABLE_NAME]
                                )
                And Not Exists  (
                                Select 1
                                From [TABLE_SCHEMA].[TABLE_NAME]
                                Where [COLUMN_NAME] Is Not Null
                                    ADDITIONAL_FILTER
                                )';

- Thomas

---

非常有用的数据库调优技巧（我有很多未使用的列应该删除:S）。我只是在最后一个SELECT语句中添加了数据类型和Order By。谢谢！ - Roimer

当运行上述代码时，会出现“参数数据类型文本对于len函数的第1个参数无效”的错误。是否更好使用DATALENGTH呢？ - adolf garlic

@adolfgarlic - 你也可以使用 DataLength 函数。我怀疑 Len 函数可能会在 text 和 ntext 数据类型上出现问题。 - Thomas