declare @tablename varchar(30) DECLARE cur_temp Cursor For select b.name from dbo.syscolumns a,dbo.sysobjects b where a.name = colname --要删的字段 and a.id = b.id and b.type = 'U' OPEN cur_temp_memid FETCH cur_tempd Into @tablename while @@fetch_status = 0 begin exec('alter table '+@tablename+' drop column colname') FETCH cur_temp Into @tablename endClose cur_temp Deallocate cur_temp
---删除数据库所有外键约束 CREATE PROCEDURE sp_drop_all_fk
as
declare @sql varchar(255) declare dropsql_cursor cursor for select 'alter table '+object_name(fkeyid)+' drop constraint '+object_name(constid)+char(10) from sysreferences
sp_MSforeachtable 和sp_MSforeachDB
DECLARE cur_temp Cursor For
select b.name from dbo.syscolumns a,dbo.sysobjects b
where a.name = colname --要删的字段
and a.id = b.id
and b.type = 'U'
OPEN cur_temp_memid
FETCH cur_tempd Into @tablename
while @@fetch_status = 0
begin
exec('alter table '+@tablename+' drop column colname') FETCH cur_temp Into @tablename
endClose cur_temp
Deallocate cur_temp
CREATE PROCEDURE sp_drop_all_fk
as
declare @sql varchar(255)
declare dropsql_cursor cursor for
select 'alter table '+object_name(fkeyid)+' drop constraint '+object_name(constid)+char(10) from sysreferences
open dropsql_cursor
fetch dropsql_cursor into @sql
begin tran
while @@fetch_status=0
begin
execute(@sql)
if @@error <> 0
begin
rollback
return
end
fetch dropsql_cursor into @sql
end
deallocate dropsql_cursor
commit
GO
是不是可以把所有的表、视图等生成.SQL后,再建一个新数据库算了