如何在SQL Server数据库架构中找到所有用100%空值填充的列? [英] How can I find all columns that are filled with 100% nulls in my SQL Server database schema?
本文介绍了如何在SQL Server数据库架构中找到所有用100%空值填充的列?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
是否有SQL方法可以找出模式中的哪些列完全充满空值?我知道几个表中的某些字段不会被应用程序使用,并且会被删除,但是我想查看是否存在一种自动方法/脚本来在整个数据库中找到该字段/脚本,以查找代码审查的候选人/ s可能的删除。
Is there a SQL way to find out which columns in my schema are completely full of null values? There are some fields in a couple of tables that I know are not used by the application and will be dropped, but I wanted to see if there was an automated way/script to find this out across the whole database to find candidates for code review/possible removal.
如果需要,可以在x86上运行SQL Server 2005。
Running SQL Server 2005 on x86 if it matters.
谢谢!!
推荐答案
create table #SuspectColumns (
TABLE_SCHEMA sysname,
TABLE_NAME sysname,
COLUMN_NAME sysname
)
declare csrColumns cursor fast_forward for
select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where IS_NULLABLE = 'YES'
declare @TABLE_SCHEMA sysname,
@TABLE_NAME sysname,
@COLUMN_NAME sysname,
@sql nvarchar(max)
open csrColumns
while (1=1) begin
fetch next
from csrColumns
into @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME
if @@FETCH_STATUS<>0 break
set @sql = N'if not exists(select 1 from ' + QUOTENAME(@TABLE_SCHEMA) + N'.' + QUOTENAME(@TABLE_NAME) + N' where ' + QUOTENAME(@COLUMN_NAME) + N'is not null)
insert into #SuspectColumns values (''' + @TABLE_SCHEMA + N''',''' + @TABLE_NAME + N''',''' + @COLUMN_NAME + N''')'
exec sp_executesql @sql
end /* while */
close csrColumns
deallocate csrColumns
select * from #SuspectColumns
drop table #SuspectColumns
这篇关于如何在SQL Server数据库架构中找到所有用100%空值填充的列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文