CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2008
mattruma
mattruma

Reputation: 16677

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!

Upvotes: 3

Views: 1012

Answers (2)

marc_s
marc_s

Reputation: 754778

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

Upvotes: 5

Andomar
Andomar

Reputation: 238136

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'

Upvotes: 3

Related Questions