Getting Constraint Names through ADO
-
I have a SQL database column that was orignally declared with a not null constraint. (using alter table add column) Turns out, the column needs to be nullable to keep from breaking legacy apps. Should be easy, use alter statement to change to all not null, right? Not. This throws an error complaining that "The object 'xxxx' is dependent on column 'columnName'". So I looked it up in SSMS and there it is under constraints with what appears to be a generated name. This is a distributed application, so I am thinking that the constraint name is not guaranteed to be the same. OK then, just use ADOX or OpenSchema to get the offending constraint name. After googling and trying different approaches for about an hour, and not finding anything that worked, (and getting pretty disgusted in the process) it ocurred to me that the name of the constraint is reported back in the error description, always between the first two quotes! It was right there the whole time! Extracted the name, ran the alter table drop constraint statement, then was able to alter the column to not null. This approach should work regardless of the locale.
-
I have a SQL database column that was orignally declared with a not null constraint. (using alter table add column) Turns out, the column needs to be nullable to keep from breaking legacy apps. Should be easy, use alter statement to change to all not null, right? Not. This throws an error complaining that "The object 'xxxx' is dependent on column 'columnName'". So I looked it up in SSMS and there it is under constraints with what appears to be a generated name. This is a distributed application, so I am thinking that the constraint name is not guaranteed to be the same. OK then, just use ADOX or OpenSchema to get the offending constraint name. After googling and trying different approaches for about an hour, and not finding anything that worked, (and getting pretty disgusted in the process) it ocurred to me that the name of the constraint is reported back in the error description, always between the first two quotes! It was right there the whole time! Extracted the name, ran the alter table drop constraint statement, then was able to alter the column to not null. This approach should work regardless of the locale.
Sir, I don't know what do you want exactly but i think this would help.
select
parobj.name as 'ParentTable',
concol.name as 'ConstrantColumn',
refobj.name as 'ReferencedTable',
refcol.name as 'ReferencedColumn'
from sys.foreign_key_columns
inner join sys.objects as parobj
on parobj.object_id = sys.foreign_key_columns.parent_object_id
inner join sys.columns as concol
on concol.column_id = sys.foreign_key_columns.constraint_column_id and
concol.object_id = sys.foreign_key_columns.parent_object_id
inner join sys.objects as refobj
on refobj.object_id = sys.foreign_key_columns.referenced_object_id
inner join sys.columns as refcol
on refcol.column_id = sys.foreign_key_columns.referenced_column_id and
refcol.object_id = sys.foreign_key_columns.referenced_object_idor may be this:
SELECT OBJECT_NAME(OBJECT_ID) AS 'NameofConstraint',
SCHEMA_NAME(schema_id) AS 'SchemaName',
OBJECT_NAME(parent_object_id) AS 'TableName',
type_desc AS 'ConstraintType'
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'100 :rose: ;)
Help people,so poeple can help you.