CREATE PROCEDURE UpdateAllDefault asdeclare cur cursor read_only for select c.表名, c.列名,d.data_type as 类型,d.column_default as 默认值 from (select a.name as 表名, b.name as 列名 from (select * from dbo.sysobjects where type ='u' and name <> 'dtproperties') a inner join (select * from syscolumns) b on a.id = b.id) c inner join information_schema.columns d on c.表名=d.table_name and c.列名=d.column_namedeclare @s varchar(500) declare @表名 varchar(40) declare @列名 varchar(40) declare @类型 varchar(40) declare @默认值 varchar(40)open curfetch next from cur into @表名, @列名, @类型, @默认值 while (@@fetch_status = 0) begin if isnull(@默认值,'')='' begin if @类型 in ('int','money') --数据类型,自己添加 begin set @s = ' alter table '+ @表名+' add default(0) for ' + @列名 print @s exec (@s) end if @类型 in ('datetime','smalldatetime') --日期类型 begin set @s = ' alter table '+ @表名+' add default(getdate()) for ' + @列名 print @s exec (@s) end end fetch next from cur into @表名, @列名, @类型, @默认值 endclose cur deallocate cur GO
select t.name,c.name,systypes.name from sysobjects t,syscolumns c,systypes where t.type='U' and t.id=c.id and systypes.type=c.type然后用游标吧
asdeclare cur cursor
read_only for
select c.表名, c.列名,d.data_type as 类型,d.column_default as 默认值
from
(select a.name as 表名, b.name as 列名
from
(select * from dbo.sysobjects where type ='u' and name <> 'dtproperties') a
inner join
(select * from syscolumns) b on a.id = b.id) c
inner join information_schema.columns d on c.表名=d.table_name and c.列名=d.column_namedeclare @s varchar(500)
declare @表名 varchar(40)
declare @列名 varchar(40)
declare @类型 varchar(40)
declare @默认值 varchar(40)open curfetch next from cur into @表名, @列名, @类型, @默认值
while (@@fetch_status = 0)
begin
if isnull(@默认值,'')=''
begin
if @类型 in ('int','money') --数据类型,自己添加
begin
set @s = ' alter table '+ @表名+' add default(0) for ' + @列名
print @s
exec (@s)
end
if @类型 in ('datetime','smalldatetime') --日期类型
begin
set @s = ' alter table '+ @表名+' add default(getdate()) for ' + @列名
print @s
exec (@s)
end
end fetch next from cur into @表名, @列名, @类型, @默认值
endclose cur
deallocate cur
GO
from sysobjects t,syscolumns c,systypes
where t.type='U' and t.id=c.id and systypes.type=c.type然后用游标吧