Is there an easy way to remove all default values for a SQL database?

Question

I'd like to remove all default values that have been setup in a particular database, is there a script that I can run to do this for all tables in database? Could be a bit time consuming without ... any help would be appreciated!

Answer 1

You could generate a script to drop all default constraints:

SELECT 'alter table ' + object_name(parent_object_id) + 
    ' drop constraint ' + name
FROM sys.objects
WHERE type = 'D'

Answer 2

If you're on SQL Server 2005 and up, you could use this script to create another script which will then drop all your default constraints:

SELECT 
    'ALTER TABLE dbo.' + OBJECT_NAME(parent_object_id) + 
    ' DROP CONSTRAINT ' + Name
FROM
    sys.default_constraints

This will create as output a series of statements such as

ALTER TABLE dbo.YourTable DROP CONSTRAINT DF_T_YourColumn

Copy the output into another SQL Server Management Studio query window and execute it and you should be done!

Marc