Posted by Erland Sommarskog on 01/11/07 22:05

Barry (bgt0990@optonline.net) writes:
> What would a script to look like to do this?
>
> In Oracle I would ALTER TABLE XXX
> Modify ( Column default null)
>
> then write a script to fill in XXX from owner_tab_columns where column
> exists

ALTER TABLE tbl DROP CONSTRAINT <nameofconstraint>

Here is a query that will generate all necessary DROP commands:

SELECT 'ALTER TABLE ' + o.name + ' ALTER COLUMN ' + c.name +
' DROP CONSTRAINT ' + oc.name
FROM sysdepends d
JOIN sysobjects ofn ON d.depid = ofn.id
JOIN sysobjects oc ON d.id = oc.id
JOIN sysobjects o ON o.id = oc.parent_obj
JOIN syscolumns c ON o.id = c.id
AND c.cdefault = oc.id
WHERE ofn.name = '<yourfunction>'

You can also modify it to regenerate the command to restore the default.
Run that modified query, before you execute the result of the above. :-)



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]