where is the information about Primary Key of a Table?
-
INFORMATION_SCHEMA.COLUMNS can be used to list all the columns of a table. Where is the information about the Primary Key of a table?
Thanks,
Data Sheet
-
Look at sys.indexes.
-
Or you can use sp_help 'tablename'.
-
sys.indexes has object_id column, my stored procedure is accepting TableName as parameter.
How can I get the Primary Key column when I have TableName in parameter but in the column I have its object_id?
Thanks,
Data Sheet
-
select
table_name = object_name(parent_object_id),
PK_name = name
from sys.objects
where type='PK'
-
You can get everything you need from the INFORMATION_SCHEMA views, if want to avoid using system tables that may be version-dependant.
SELECT k.*
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c ON k.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA
AND k.CONSTRAINT_NAME = c.CONSTRAINT_NAME
WHERE (c.CONSTRAINT_TYPE = 'PRIMARY KEY')
ORDER BY k.TABLE_SCHEMA, k.TABLE_NAME, k.ORDINAL_POSITION
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply