List of primary key components
I am making a list from a database where I want to show the number of columns that make up the primary key in each particular table in SQL Server.
For example, if Table A has a single column as primary key, Table B has a compound key of 3 parts, and Table C has a compound key of 2 parts, the result would be:
Table Name No. PK Components ---------- ----------------- Table A 1 Table B 3 Table C 2
I have tried many times using
sys but failed.
One Solution collect form web for “List of primary key components”
If you are looking just how many columns are part of PK, this could solve your problem:
SELECT TABLE_NAME , COUNT(COLUMN_NAME) No_PK_Cols FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1 AND TABLE_SCHEMA = 'YourSchema' GROUP BY TABLE_NAME