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

来源：互联网发布：猪八戒淘宝推广有效吗编辑：程序博客网时间：2024/05/08 00:06

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

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