I have run into a problem - I am in the process of writing a module that will ensure each table in our database schema has the correct primary key column(s) set. The first iteration I wrote does this under MySQL and now I am set to tackle MS SQL. However, I have run into a problem.
MySQL supports the "DESCRIBE" operator which when executed describes the table structure, giving a result set containing the following columns: "Field", "Type", "Null", "Key", "Default", and "Extra".
Ultimately I was able to use the results of this operator to determine if a column (Field) is defined as a primary key column by looking at the contents of the Key column (when Key = 'PRI'.).
Now, in MS SQL I have yet to devise a way to determine the primary key column(s) for a table. I know about the sysobjects table - and how to determine what the object is that defines the primary key - but I don't know how to get the columns it references. Here is the query I have come up with:
If there is another, more straightforward solution that involves relatively standard SQL, I would appreciate it if someone could point me off in that direction. For now I am browsing the MSDN library trying to find some hints - however my searches thus far have not turned up much!
Thanks in advance for any help or suggestions.
MySQL supports the "DESCRIBE" operator which when executed describes the table structure, giving a result set containing the following columns: "Field", "Type", "Null", "Key", "Default", and "Extra".
Ultimately I was able to use the results of this operator to determine if a column (Field) is defined as a primary key column by looking at the contents of the Key column (when Key = 'PRI'.).
Now, in MS SQL I have yet to devise a way to determine the primary key column(s) for a table. I know about the sysobjects table - and how to determine what the object is that defines the primary key - but I don't know how to get the columns it references. Here is the query I have come up with:
Code:
SELECT * FROM sysobjects WHERE parent_obj = (SELECT id FROM sysobjects WHERE name = 'table_name') AND xtype='PK'
If there is another, more straightforward solution that involves relatively standard SQL, I would appreciate it if someone could point me off in that direction. For now I am browsing the MSDN library trying to find some hints - however my searches thus far have not turned up much!
Thanks in advance for any help or suggestions.