How do I get the name of a columns default contraint on a SQL Server 2000 installation? As far as I know sys.default_constraints is not available. Its different on every DB, the structure is like DF_table_column__3DE8FB0E for example.
Thanks :)
Asked
Active
Viewed 1,287 times
0
-
2possible duplicate of [How do I find a default constraint using INFORMATION_SCHEMA?](http://stackoverflow.com/questions/141682/how-do-i-find-a-default-constraint-using-information-schema) – Joe Stefanelli Dec 20 '11 at 14:37
-
Now the question is, after knowing the name in some variable, how do I drop that contraint? *alter table table drop constraint @ConstraintName* is not working ... – grady Dec 20 '11 at 14:44
-
That's the correct syntax. Can you elaborate on "not working?" Is there an error message? – Joe Stefanelli Dec 20 '11 at 14:48
-
3You can't parameterise it. You need dynamic SQL. – Martin Smith Dec 20 '11 at 14:49
-
Wrong syntax near '@ContraintName'. Dynamic SQL? – grady Dec 20 '11 at 14:50
-
Ah. Didn't realize you were literally using a `@ConstraintName` variable. Martin Smith's comment is correct. – Joe Stefanelli Dec 20 '11 at 14:50
-
Fount it: exec('alter table table drop constraint ' + @ConstraintName) – grady Dec 20 '11 at 14:56
-
1@grady - You would put the string `'ALTER TABLE T DROP CONSTRAINT ' + QUOTENAME(@ConstraintName)` into a variable `@dynsql` then `EXEC(@dynsql)` – Martin Smith Dec 20 '11 at 14:57
1 Answers
0
Try using INFORMATION_SCHEMA, SQL Server's implementation of the SQL 92 self-describing features.
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Paul Alan Taylor
- 10,474
- 1
- 26
- 42
-
This only gives me those columns for a certain table that are a foreign key. – grady Dec 20 '11 at 14:39