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
错了,修改下: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'
TONY哥。。贴的跟需求对不上的吧
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去掉。。
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 */用这个。
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'
把这个结果拿出来执行下就可以了。如果不判断空表的话自己处理下。
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
错了,修改下: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'
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去掉。。
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
*/用这个。