如何删除所有纪录都为NULL的表

   这个需求是今天看到一网友在论坛提出的问题,这里的纪录为NULL,是指的纪录为"NULL"值,而并不是表的纪录数为0,所以我们可以通过CHECKSUM函数来实现这个需求,下面是实现的DEMO:

 

--运行环境:SQL Server 2005
 

USE tempdb
GO

SET NOCOUNT ON

IF OBJECT_ID('T1') IS NOT NULL
   DROP TABLE T1

IF OBJECT_ID('T2') IS NOT NULL
   DROP TABLE T2
   
IF OBJECT_ID('T3') IS NOT NULL
   DROP TABLE T3
   
--建立测试环境
CREATE TABLE T1
(
   col1 INT,
   col2 INT,
   col3 datetime    
)

CREATE TABLE T2
(
   col1 INT,
   col2 VARCHAR(10),
   col3 float
)

CREATE TABLE T3
(
   col1 decimal(10,2),
   col2 NCHAR(10)
)
GO

--插入测试数据
INSERT T1 SELECT NULL,NULL,NULL 
UNION ALL SELECT NULL,NULL,NULL

INSERT T2 SELECT NULL,NULL,NULL

INSERT T3 SELECT NULL,NULL
UNION ALL SELECT NULL,N'Test'
GO


--删除所有纪录为NULL的表

DECLARE   
   @table_name sysname,
   @sql NVARCHAR(1000)

DECLARE curTest CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT name
FROM sys.tables
WHERE name NOT LIKE '#%'
   AND name NOT IN('MSdistributor_access')

OPEN curTest
FETCH NEXT FROM curTest INTO @table_name
WHILE @@FETCH_STATUS=0
BEGIN
   
   SET @sql = N'
IF NOT EXISTS(
SELECT *
FROM ' +  @table_name +  N'
WHERE CHECKSUM(*) <> 2147483511
)

DROP TABLE  ' + @table_name + N'
'
   EXEC sp_EXECUTESQL @sql
   
   FETCH NEXT FROM curTest INTO @table_name
END
CLOSE curTest
DEALLOCATE curTest

GO

 
SELECT * FROM T1    --抛出错误

SELECT * FROM T2    --抛出错误
SELECT * FROM T3
GO


--删除测试环境

IF OBJECT_ID('T1') IS NOT NULL
   DROP TABLE T1

IF OBJECT_ID('T2') IS NOT NULL
   DROP TABLE T2
   
IF OBJECT_ID('T3') IS NOT NULL
   DROP TABLE T3

   
  从这个例子可以看到,T1,T2的表已经被删除,因为它的所有纪录都为NULL.

 

  虽然它实现了这个需求,但可能存在以下隐患:

 

  1:CHECKSUM()函数不能用于一些非可比数据类型:text、ntext、xml、image 和 cursor

  2:如果表上存在一些外键约束,直接删除,会抛出错误