通过游标遍历数据库中的用户表，找出所有含有NULL值字段的表名和字段名

最新推荐文章于 2020-04-07 11:47:29 发布

htl258_Tony

最新推荐文章于 2020-04-07 11:47:29 发布

阅读量3.3k

点赞数

分类专栏：系统表与表结构游标应用实例文章标签： null 数据库 table insert object microsoft

本文链接：https://blog.youkuaiyun.com/htl258/article/details/5517629

版权

系统表与表结构同时被 2 个专栏收录

27 篇文章

订阅专栏

游标应用实例

4 篇文章

订阅专栏

问题贴：http://topic.youkuaiyun.com/u/20100422/17/af3b725c-d647-4d34-854b-4b6e8b056fae.html?15171

编写SQL脚本，可以遍历Test数据库的所有数据表，针对各表的所有数据记录，分析出内容为NULL的字段

--创建测试数据

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

--查询

--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-04-22 20:28:29
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--          Jul 9 2008 14:43:34
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--------------------------------------------------------------------------

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

/*
TBNAME               COLNAME
-------------------- --------------------
tb1                  b1
tb1                  c1
tb2                  a2
tb2                  c2

(4 行受影响)
*/

--另一种需求：找出整个字段都为NULL值的表名和列名。

use test

if object_id('test.dbo.tb1') is not null drop table tb1

-- 创建数据表

create table tb1

(

a1 char(2),

b1 char(2),

c1 int

)

--插入测试数据

insert into tb1 select 'A','B',2