How to drop SQL default constraint without knowing its name?

by

Expanding on Mitch Wheat’s code, the following script will generate the command to drop the constraint and dynamically execute it.

declare @schema_name nvarchar(256)
declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
declare @Command  nvarchar(1000)

set @schema_name = N'MySchema'
set @table_name = N'Department'
set @col_name = N'ModifiedDate'

select @Command = 'ALTER TABLE ' + @schema_name + '.[' + @table_name + '] DROP CONSTRAINT ' + d.name
 from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
 where t.name = @table_name
  and t.schema_id = schema_id(@schema_name)
  and c.name = @col_name

--print @Command

execute (@Command)

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. How do I UPDATE from a SELECT in SQL Server?
  3. Get top 1 row of each group
  4. How to calculate age (in years) based on Date of Birth and getDate()
  5. CROSS JOIN vs INNER JOIN in SQL
  6. Convert Month Number to Month Name Function in SQL
  7. Function vs. Stored Procedure in SQL Server
  8. Multiple INSERT statements vs. single INSERT with multiple VALUES
  9. Limit on the WHERE col IN (…) condition
  10. Removing leading zeroes from a field in a SQL statement
  11. Generate a resultset of incrementing dates in TSQL
  12. Getting result of dynamic SQL into a variable for sql-server
  13. How to get difference between two rows for a column field?
  14. Better techniques for trimming leading zeros in SQL Server?
  15. Common Table Expression, why semicolon?
  16. T-SQL split string based on delimiter
  17. SQL Server SELECT into existing table
  18. How to check if a stored procedure exists before creating it
  19. SQL: parse the first, middle and last name from a fullname field
  20. Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
  21. SQL Select Upcoming Birthdays
  22. How can I include null values in a MIN or MAX?
  23. Stored procedure that exports data into csv files only exports to one file
  24. Get Start and End Date from Week Number SQL Server
  25. How to get the identity of an inserted row?
  26. Get the last day of the month in SQL
  27. TSQL – How to use GO inside of a BEGIN .. END block?
  28. SQL take just the numeric values from a varchar
  29. Why is RAND() not producing random numbers?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment