question

mattruma avatar image
mattruma asked

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!

sql-serverscript
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

Assuming you are on 2005+, and all your defaults are default constraints, and not bound defaults:

DECLARE @SQL [varchar](MAX)
SET @SQL = ''
SELECT @sql = @sql + 'ALTER TABLE [' + SCHEMA_NAME([schema_id]) + '].[' + OBJECT_NAME([parent_object_id]) + '] DROP CONSTRAINT [' + [name] + '];
'  FROM [sys].[default_constraints]
EXEC (@SQL)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

You can also use PowerShell: This script (running at the database level) will script and drop all default constraints.

PS SQLSERVER:\sql\localhost\default\Databases\YourDatabase> dir Tables\*\Columns\*\DefaultConstraint | % {$_.Script(); $_.Drop()}
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.