Menu
I'd like to determine the primary key of a table using TSQL (stored procedure or system table is fine). Is there such a mechanism in SQL Server (2005 or 2008)?
454
There are two kinds of keys: Primary key A table can have only one primary key. A primary key consists of one or more fields that uniquely identify each record that you store in the table. Often, there is a unique identification number, such as an ID number, a serial number, or a code, that serves as a primary key.
If you want to get the primary key for a specific table, then you need to filter on SchemaName and TableName . IMHO, this solution is very generic and does not use any string literals, so it will run on any machine. Show activity on this post. You can also filter on the table_name column if you want a specific table.
To check if a primary key exists on a table uses the system stored procedure named SP_PKEYS or view INFORMATION_SCHEMA.
This should get you started:
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name WHERE tc.TABLE_NAME = 'TableName' AND tc.CONSTRAINT_TYPE = 'Primary Key' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
