SQL Server 2000自动添写当前库中值为空的字段

--自动添写当前数据库中值为空的字段;

--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