![]() Use the schema view INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS. I need to list all key constraints (primary, foreign - any) on the table in question ("people" in this case). PK_people PRIMARY_KEY_CONSTRAINT peopleIDįK_people_states_stateID FOREIGN_KEY_CONSTRAINT stateID states stateID ![]() So I need a query that will output this: CONSTRAINT_NAME CONSTRAINT_TYPE COLUMN_NAME REFERENCED_TABLE REFERENCED_COLUMN You can guess that I have a Foreign Key constraint on people.stateID. The Primary Key on that is on the stateID column. I also have a table "states" with "stateID" and "state". The Primary Key is on the poepleID column. Say I have a table "people" with columns "poepleID", "name", "city", "stateID". (I'd need a query compatible for SQL Server versions 2008 & later)īased on initial responses, I may not have been clear on the desired output. Where do I get the referenced (foreign key) table and column information from? LEFT JOIN sys.columns AS columns ON columns.object_id = obj_table.object_id AND lumn_id = lid LEFT JOIN sys.sysconstraints AS constraints ON nstid = obj_Constraint.object_id LEFT JOIN sys.objects AS obj_Constraint ON obj_table.object_id = obj_Constraint.parent_object_id ![]() Obj_Constraint.type_desc AS CONSTRAINT_TYPE, I need the output to look like this in the query results:ĬONSTRAINT_NAME CONSTRAINT_TYPE COLUMN_NAME REFERENCED_TABLE REFERENCED_COLUMN I've found lots of closely relevant information here at SO, but I'm not finding a couple of specific pieces of information. I need to output the constraint information for a given table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |