-----------'的确挺有挑战性的!写了好久,终于搞定了'-------------------------------------SQL Server数据格式化工具--------------------- --------------------------------------------------------------- -- DESIGNER :happycell188(喜喜) -- QQ :584738179 -- Development Tool :Microsoft Visual C++ 6.0 C Language -- FUNCTION :CONVERT DATA TO T-SQL --------------------------------------------------------------- -- Microsoft SQL Server 2005 -- Developer Edition on Microsoft Windows XP [版本 5.1.2600] --------------------------------------------------------------- ---------------------------------------------------------------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',2 union all select 'A','C',null union all select 'A','D',5 union all select 'B','C',3 union all select null,'E',null union all select 'E','F',2 union all select 'E','G',3 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('test.dbo.result') is not null drop table result create table result(tbname varchar(30),tbcolumn varchar(30))--存储结果 --获取数据库 test 中的所有表名: if object_id('test.dbo.nametemptable') is not null drop table nametemptable create table nametemptable(tbname varchar(30))--存储表名 insert into nametemptable select name from test..sysobjects where type='u' and name not in('columntemptable','nametemptable','result') --选出 null 字段: declare @tbname varchar(30), @colname varchar(30), @sqlstr varchar(max), @tb_i int, @tb_total int, @col_i int, @col_total int select @tb_total=count(*),@tb_i=1 from nametemptable while(@tb_i<=@tb_total) begin if object_id('test.dbo.columntemptable') is not null drop table columntemptable create table columntemptable(tbcolumn varchar(30)) select top 1 @tbname=tbname from nametemptable where tbname not in (select top (@tb_i-1) tbname from nametemptable) exec('insert into columntemptable Select Name FROM SysColumns Where id=Object_Id('''+@tbname+''')') select @col_total=count(*),@col_i=1 from columntemptable while(@col_i<=@col_total) begin select top 1 @colname=tbcolumn from columntemptable where tbcolumn not in (select top (@col_i-1) tbcolumn from columntemptable) exec('if exists(select * from '+@tbname+' where '+@colname+' is null) insert into result select '''+@tbname+''','''+@colname+'''') set @col_i=@col_i+1 end set @tb_i=@tb_i+1 drop table columntemptable end --测试: select * from result /*测试结果:tbname tbcolumn ----------------------- tb1 b1 tb1 c1 tb2 a2 tb2 c2(4 行受影响) */ drop table nametemptable,result
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',2 union all select 'A','C',null union all select 'A','D',5 union all select 'B','C',3 union all select null,'E',null union all select 'E','F',2 union all select 'E','G',3 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 goIF OBJECT_ID('TEMPDB..#T') IS NOT NULL DROP TABLE #T GO CREATE TABLE #T(TBNAME NVARCHAR(20),COLNAME NVARCHAR(20)) SET NOCOUNT ON DECLARE @S NVARCHAR(4000) DECLARE C CURSOR FOR SELECT 'IF EXISTS(SELECT 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 /* TBNAME COLNAME -------------------- -------------------- tb1 b1 tb1 c1 tb2 a2 tb2 c2(4 行受影响) */游标法
IF OBJECT_ID('TEMPDB..#T') IS NOT NULL DROP TABLE #T GO CREATE TABLE #T(TBNAME NVARCHAR(20),COLNAME NVARCHAR(20)) 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查询处加个TOP 1 性能或许会好点
-----------'的确挺有挑战性的!写了好久,终于搞定了'-------------------------------------SQL Server数据格式化工具---------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------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',2
union all select 'A','C',null
union all select 'A','D',5
union all select 'B','C',3
union all select null,'E',null
union all select 'E','F',2
union all select 'E','G',3
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('test.dbo.result') is not null drop table result
create table result(tbname varchar(30),tbcolumn varchar(30))--存储结果
--获取数据库 test 中的所有表名:
if object_id('test.dbo.nametemptable') is not null drop table nametemptable
create table nametemptable(tbname varchar(30))--存储表名
insert into nametemptable
select name from test..sysobjects where type='u' and name not in('columntemptable','nametemptable','result')
--选出 null 字段:
declare @tbname varchar(30),
@colname varchar(30),
@sqlstr varchar(max),
@tb_i int,
@tb_total int,
@col_i int,
@col_total int
select @tb_total=count(*),@tb_i=1 from nametemptable
while(@tb_i<=@tb_total)
begin
if object_id('test.dbo.columntemptable') is not null drop table columntemptable
create table columntemptable(tbcolumn varchar(30))
select top 1 @tbname=tbname from nametemptable
where tbname not in (select top (@tb_i-1) tbname from nametemptable)
exec('insert into columntemptable
Select Name FROM SysColumns Where id=Object_Id('''+@tbname+''')')
select @col_total=count(*),@col_i=1 from columntemptable
while(@col_i<=@col_total)
begin
select top 1 @colname=tbcolumn from columntemptable
where tbcolumn not in (select top (@col_i-1) tbcolumn from columntemptable)
exec('if exists(select * from '+@tbname+' where '+@colname+' is null)
insert into result select '''+@tbname+''','''+@colname+'''')
set @col_i=@col_i+1
end
set @tb_i=@tb_i+1
drop table columntemptable
end
--测试:
select * from result
/*测试结果:tbname tbcolumn
-----------------------
tb1 b1
tb1 c1
tb2 a2
tb2 c2(4 行受影响)
*/
drop table nametemptable,result
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',2
union all select 'A','C',null
union all select 'A','D',5
union all select 'B','C',3
union all select null,'E',null
union all select 'E','F',2
union all select 'E','G',3
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
goIF OBJECT_ID('TEMPDB..#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T(TBNAME NVARCHAR(20),COLNAME NVARCHAR(20))
SET NOCOUNT ON
DECLARE @S NVARCHAR(4000)
DECLARE C CURSOR FOR
SELECT 'IF EXISTS(SELECT 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
/*
TBNAME COLNAME
-------------------- --------------------
tb1 b1
tb1 c1
tb2 a2
tb2 c2(4 行受影响)
*/游标法
呵呵...锻炼一下!通过编写这样的程序,我能够发现问题,并且自己解决问题...收获:学会了游标的使用、exec()的正确用法、还有提取指定数据库(如:test)中的数据表以及数据表对应的字段等等等等...有点自夸了!呵呵...
DROP TABLE #T
GO
CREATE TABLE #T(TBNAME NVARCHAR(20),COLNAME NVARCHAR(20))
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查询处加个TOP 1 性能或许会好点