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!
asked
Is there an easy way to remove all default values for a SQL database?
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)
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()}
