Thursday, July 29, 2010

Drop columns with default constraint in SQL Server 2000

here's a sample I used to drop all the columns with default constraint in a table. it can be further changed to work better.

declare dfName cursor for
SELECT o2.name,c.name
FROM syscolumns c
JOIN sysobjects o ON c.id = o.id
JOIN sysobjects o2 ON c.cdefault = o2.id
WHERE o.name = 'table name'

open dfName
declare @dn varchar(100),@cn varchar(50)
declare @tSQL varchar(300)
fetch next from dfName into @dn,@cn
print @@fetch_status
print @dn+':'+@cn
while (@@fetch_status<>-1)
begin
set @tSQL='alter table tablename drop constraint '+@dn+' alter table tablename drop column '+@cn
exec(@tSQL)
fetch next from dfName into @dn,@cn
print @@fetch_status
print @dn+':'+@cn
end
close dfName
deallocate dfName