How do you drop a default value or similar constraint in T-SQL?

by

You can use this code to do it automatically:

DECLARE @tableName VARCHAR(MAX) = '<MYTABLENAME>'
DECLARE @columnName VARCHAR(MAX) = '<MYCOLUMNAME>'
DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name 
FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName) 
AND PARENT_COLUMN_ID = (
    SELECT column_id FROM sys.columns
    WHERE NAME = @columnName AND object_id = OBJECT_ID(@tableName))
IF @ConstraintName IS NOT NULL
    EXEC('ALTER TABLE '+@tableName+' DROP CONSTRAINT ' + @ConstraintName)

Just replace <MYTABLENAME> and <MYCOLUMNNAME> as appropriate.

More Related Contents:

  1. SQL query for display one field only once which having multiple record
  2. NULL values inside NOT IN clause
  3. How can I convert bigint (UNIX timestamp) to datetime in SQL Server?
  4. How to calculate percentage with a SQL statement
  5. A table name as a variable
  6. Multi-statement Table Valued Function vs Inline Table Valued Function
  7. How do you return the column names of a table?
  8. Remove trailing zeros from decimal in SQL Server
  9. Dynamically create columns sql
  10. TSQL PIVOT MULTIPLE COLUMNS
  11. INT vs Unique-Identifier for ID field in database
  12. How do I sort a VARCHAR column in SQL server that contains numbers?
  13. TSQL – Cast string to integer or return default value
  14. Why do you create a View in a database?
  15. SQL query to find Nth highest salary from a salary table
  16. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  17. Calculate the number of records for each date between 2 dates
  18. What is causing Foreign Key Mismatch error?
  19. T-SQL: How to use parameters in dynamic SQL?
  20. Get the records of last month in SQL server
  21. Merge overlapping date intervals
  22. Access to Result sets from within Stored procedures Transact-SQL SQL Server
  23. alternatives to REPLACE on a text or ntext datatype
  24. Split words with a capital letter in sql
  25. SQL poor stored procedure execution plan performance – parameter sniffing
  26. scope_identity vs ident_current
  27. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  28. Date serial in SQL?
  29. SQL Server: +(unary) operator on non-numeric Strings
  30. T-SQL calculate moving average

Leave a Comment