再次请教高手sql关于查NULL值字段问题

[xiaozhuge2008]

编写SQL脚本遍历Test数据库的所有数据表查出内容全部为NULL的字段。

是指有的列可能为废列，全部都为NULL，而不是那种有的有值，有的部分为NULL。需要得到的是表名，字段名。此功能是要查出那些没用的废列以及所属的表。

[xiaozhuge2008]

顶~~

[Trance]

select ' select top 1 1 from  ' + obj.name + ' where ' + col.name + ' is not null if @@rowcount <= 0 select ''表名：' + obj.name + ''', ''字段：' + col.name + ''''
from syscolumns col
inner join sysobjects obj on obj.Id = col.Id
where obj.xtype = 'U'

把这个结果拿出来执行下就可以了。如果不判断空表的话自己处理下。

[htl258_Tony]

前几天才写的，参考这里：http://blog.csdn.net/htl258/archive/2010/04/22/5517629.aspx

[htl258_Tony]

IF OBJECT_ID('TEMPDB..#T') IS NOT NULL
    DROP TABLE #T
GO
CREATE TABLE #T(TBNAME NVARCHAR(120),COLNAME NVARCHAR(120))
SET NOCOUNT ON
DECLARE @S NVARCHAR(4000)
DECLARE C CURSOR FOR
    SELECT 'IF EXISTS(SELECT TOP 1 1 FROM ['+B.NAME+'] WHERE ['+A.NAME+'] IS NULL)
                INSERT #T SELECT '''+B.NAME+''','''+A.NAME+''''
    FROM SYSCOLUMNS A JOIN SYSOBJECTS B ON A.ID=B.ID AND B.TYPE='U'
OPEN C
FETCH C INTO @S
WHILE @@FETCH_STATUS=0
BEGIN
    EXEC(@S)
    FETCH C INTO @S
END
CLOSE C
DEALLOCATE C
SET NOCOUNT OFF
SELECT * FROM #T

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/04/22/5517629.aspx

[Trance]

[Quote=引用 2 楼 trance 的回复:]

SQL code

select ' select top 1 1 from ' + obj.name + ' where ' + col.name + ' is not null if @@rowcount <= 0 select ''表名：' + obj.name + ''', ''字段：' + col.name + ''''
from syscolumns col
inner join……
[/Quote]

错了，修改下：

select ' if not exists (select top 1 1 from  ' + obj.name + ' where ' + col.name + ' is not null) select ''表名：' + obj.name + ''', ''字段：' + col.name + ''''
from syscolumns col
inner join sysobjects obj on obj.Id = col.Id
where obj.xtype = 'U'

[htl258_Tony]

哦，明白了，是全部为NULL

[Trance]

[Quote=引用 4 楼 htl258 的回复:]

SQL code
IF OBJECT_ID('TEMPDB..#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T(TBNAME NVARCHAR(120),COLNAME NVARCHAR(120))
SET NOCOUNT ON
DECLARE @S NVARCHAR(4000)
DECLARE C CURSOR FOR
SEL……
[/Quote]

这效率会不会有点。。。。。

[feixianxxx]

TONY哥。。贴的跟需求对不上的吧。。。

[Trance]

select 'select ''表名：' + obj.name + ''', ''字段：' + col.name + ''' where not exists (select top 1 1 from  ' + obj.name + ' where ' + col.name + ' is not null) union all'
from syscolumns col
inner join sysobjects obj on obj.Id = col.Id
where obj.xtype = 'U'

这样写直观点。。。把最后一个union all去掉。。

[htl258_Tony]

use test
go
if object_id('test.dbo.tb1') is not null drop table tb1
-- 创建数据表
create table tb1
(
a1 char(2),
b1 char(2),
c1 int
)
go
--插入测试数据
insert into tb1 select 'A','B',2
union all select 'A','C',3
union all select 'A','D',null
union all select 'B',null,3
union all select 'B','E',8
union all select 'E','F',null
union all select 'E','G',3
go
if object_id('test.dbo.tb2') is not null drop table tb2
-- 创建数据表
create table tb2
(
a2 char(2),
b2 char(2),
c2 int
)
go
--插入测试数据
insert into tb2 select 'A','B',null
union all select 'A','C',null
union all select 'A','D',null
union all select 'B','C',null
union all select null,'E',null
union all select 'E','F',null
union all select 'E','G',null
go
if object_id('test.dbo.tb3') is not null drop table tb3
-- 创建数据表
create table tb3
(
a3 char(2),
b3 char(2),
c3 int
)
go
--插入测试数据
insert into tb3 select 'A','B',2
union all select 'A','C',3
union all select 'A','D',5
union all select 'B','C',3
union all select 'B','E',8
union all select 'E','F',2
union all select 'E','G',3
go
IF OBJECT_ID('TEMPDB..#T') IS NOT NULL
    DROP TABLE #T
GO
CREATE TABLE #T(TBNAME NVARCHAR(120),COLNAME NVARCHAR(120))
SET NOCOUNT ON
DECLARE @S NVARCHAR(4000)
DECLARE C CURSOR FOR
    SELECT 'IF NOT EXISTS(SELECT TOP 1 1 FROM ['+B.NAME+'] WHERE ['+A.NAME+'] IS NOT NULL)
                INSERT #T SELECT '''+B.NAME+''','''+A.NAME+''''
    FROM SYSCOLUMNS A JOIN SYSOBJECTS B ON A.ID=B.ID AND B.TYPE='U'
OPEN C
FETCH C INTO @S
WHILE @@FETCH_STATUS=0
BEGIN
    EXEC(@S)
    FETCH C INTO @S
END
CLOSE C
DEALLOCATE C
SET NOCOUNT OFF
SELECT * FROM #T

/*
TBNAME	COLNAME
tb2	    c2
*/

用这个。

[htl258_Tony]

[Quote=引用 8 楼 feixianxxx 的回复:]
TONY哥。。贴的跟需求对不上的吧。。。
[/Quote]我很了解我在做什么。

[xiaozhuge2008]

[Quote=引用 9 楼 trance 的回复:]
SQL code

select 'select ''表名：' + obj.name + ''', ''字段：' + col.name + ''' where not exists (select top 1 1 from ' + obj.name + ' where ' + col.name + ' is not null) union all'
from syscolumns col
……
[/Quote]这样好像运行出错啊