code_junkie

How do I determine if a column is in the primary key of its table? (SQL Server)

Nov 11th, 2011
81
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.89 KB | None | 0 0
  1. select Table_Name, Column_name, data_type, is_Nullable
  2. from information_Schema.Columns
  3.  
  4. SELECT K.TABLE_NAME,
  5. K.COLUMN_NAME,
  6. K.CONSTRAINT_NAME
  7. FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
  8. JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
  9. ON C.TABLE_NAME = K.TABLE_NAME
  10. AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
  11. AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
  12. AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
  13. WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY'
  14. AND K.COLUMN_NAME = 'keycol';
  15.  
  16. SELECT K.TABLE_NAME, C.CONSTRAINT_TYPE, K.COLUMN_NAME, K.CONSTRAINT_NAME
  17. FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
  18. JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
  19. ON C.TABLE_NAME = K.TABLE_NAME
  20. AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
  21. AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
  22. AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
  23. WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY'
  24. ORDER BY K.TABLE_NAME, C.CONSTRAINT_TYPE, K.CONSTRAINT_NAME
Add Comment
Please, Sign In to add comment