Pages

Monday, March 1, 2010

Drop all contraints on a table

-- t-sql scriptlet to drop all constraints on a table
SELECT
'ALTER TABLE ' + object_name(parent_obj) + ' DROP CONSTRAINT ' + [name]
AS Script into #temp
from sysobjects where --xtype IN ('F','TR')
object_name(parent_obj) like '%%'
and (name not in (',')
and name not like '%%')
--drop table #temp
Select * from #temp

DECLARE get_sqlstmt CURSOR FOR
select Script
FROM #temp

OPEN get_sqlstmt
FETCH NEXT FROM get_sqlstmt
INTO @vsql
WHILE @@FETCH_STATUS = 0
BEGIN
Print (@vsql)
EXEC(@vsql)

FETCH NEXT FROM get_sqlstmt INTO @vsql
END

CLOSE get_sqlstmt
DEALLOCATE get_sqlstmt

Drop table #temp

No comments:

Post a Comment