I know the syntax:
ALTER TABLE [TheTable] DROP CONSTRAINT [TheDefaultConstraint]
but how to I drop the default constraint when I don't know its name? (That is, it was autogenerated at CREATE TABLE
time.)
I know the syntax:
ALTER TABLE [TheTable] DROP CONSTRAINT [TheDefaultConstraint]
but how to I drop the default constraint when I don't know its name? (That is, it was autogenerated at CREATE TABLE
time.)
You can find the name of the constraint out by sp_help [table name] and then drop it by name.
Or you can probably do this via Management studio.
If you want to do this manually, you can use Management Studio to find it (under the Constraints node inside the table).
To do it using SQL:
If the constraints are default constraints, you can use sys.default_constraints
to find it:
SELECT OBJECT_NAME(parent_object_id) AS TableName, name AS ConstraintName
FROM sys.default_constraints ORDER BY TableName, ConstraintName
If you are looking for other constraints as well (check, unique, foreign key, default, primary key), you can use sysconstraints
:
SELECT OBJECT_NAME(id) AS TableName, OBJECT_NAME(constid) AS ConstraintName
FROM sysconstraints ORDER BY TableName, ConstraintName
You do not say which version of SQL Server you are using. The above work on both SQL 2005 and SQL 2008.