code_junkie

How do you list all the primary keys of a table in SQL Server?

Nov 11th, 2011
112
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.13 KB | None | 0 0
  1. SELECT Col.Column_Name from
  2. INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
  3. INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
  4. WHERE
  5. Col.Constraint_Name = Tab.Constraint_Name
  6. AND Col.Table_Name = Tab.Table_Name
  7. AND Constraint_Type = 'PRIMARY KEY '
  8. AND Col.Table_Name = '<your table name>'
  9.  
  10. select *
  11. from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  12. where CONSTRAINT_TYPE = 'Primary Key' and Table_Name = 'whatever'
  13.  
  14. SELECT A.Name,Col.Column_Name from
  15. INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
  16. INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col ,
  17. (select NAME from dbo.sysobjects where xtype='u') AS A
  18. WHERE
  19. Col.Constraint_Name = Tab.Constraint_Name
  20. AND Col.Table_Name = Tab.Table_Name
  21. AND Constraint_Type = 'PRIMARY KEY '
  22. AND Col.Table_Name = A.Name
  23.  
  24. SELECT TC.TABLE_NAME as [Table_name], TC.CONSTRAINT_NAME as [Primary_Key]
  25. FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
  26. INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
  27. ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
  28. WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
  29. TC.TABLE_NAME IN
  30. (SELECT [NAME] AS [TABLE_NAME] FROM SYS.OBJECTS
  31. WHERE TYPE = 'U')
Add Comment
Please, Sign In to add comment