--自动添写当前数据库中值为空的字段;
--SQL Server 2000
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [setdefault] AS
declare @tbName varchar(50); --表名
declare @tbColumn varchar(50); --列名
declare @cType int; --类型
declare @cdefault int; --默认值
declare @isnull int; --是否为空
declare @sql nvarchar(300);
declare @zeroAffectCount int;
declare @blankAffectCount int;
declare @dateAffectCount int;
declare @allCount int;
select @zeroAffectCount=0, @blankAffectCount=0,@dateAffectCount=0,@allCount=0;
DECLARE setdefault_Cursor CURSOR FOR
select SO.name, SC.name,SC.xtype,SC.cdefault, SC.isnullable
from sysobjects SO inner join syscolumns SC on SO.id=SC.id
and SO.xtype = 'U' and SO.name != 'dtproperties';
OPEN setdefault_Cursor;
FETCH NEXT FROM setdefault_Cursor into @tbName, @tbColumn, @cType, @cdefault, @isnull;
WHILE @@FETCH_STATUS = 0
BEGIN
if(@cType in (select xtype from systypes where name in ('bit','tinyint','smallint','int','real','decimal','float','numeric')))
begin
set @sql = 'update '+ @tbName + ' set ' + @tbColumn + '= 0 where ' + @tbColumn + ' is null';
--print @sql;
exec(@sql);
set @zeroAffectCount = @zeroAffectCount + 1;
end
if(@cType in (select xtype from systypes where name in ('nvarchar', 'ntext','varchar')))
begin
set @sql = 'update '+ @tbName + ' set ' + @tbColumn + '= '''' where ' + @tbColumn + ' is null';
--print @sql;
exec(@sql);
set @blankAffectCount = @blankAffectCount + 1;
end
if(@cType in (select xtype from systypes where name='datetime'))
begin
set @sql = 'update '+ @tbName + ' set ' + @tbColumn + '= ''1900-1-1'' where ' + @tbColumn + ' is null';
--print @sql;
exec(@sql);
set @dateAffectCount = @dateAffectCount + 1;
end
set @allCount = @allCount + 1;
FETCH NEXT FROM setdefault_Cursor into @tbName, @tbColumn, @cType, @cdefault, @isnull;
END
select @zeroAffectCount as '填充零的行数', @blankAffectCount as '填充空符串的行数',
@dateAffectCount as '填充1900日期行数', @allCount as '游标游历的行数';
CLOSE setdefault_Cursor
DEALLOCATE setdefault_Cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO