Pastebin is 300% more awesome when you are logged in. Sign Up, it's FREE!
Guest

COLUMNS THAT ARE PRIMARY KEY AS WELL AS FOREIGN KEY - SQL SE

By: geshan on May 17th, 2011  |  syntax: SQL  |  size: 0.57 KB  |  hits: 336  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. --FIND COLUMNS THAT ARE PRIMARY KEY AS WELL AS FOREIGN KEY - SQL SERVER 2008
  2. SELECT
  3.     CU.TABLE_NAME, CU.COLUMN_NAME
  4. FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
  5. WHERE CU.CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  6. WHERE CONSTRAINT_TYPE = 'PRIMARY KEY') AND
  7. COLUMN_NAME IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  8. WHERE TABLE_NAME = CU.TABLE_NAME AND
  9. COLUMN_NAME = CU.COLUMN_NAME AND
  10. CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  11. WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'))