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

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!

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

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'