0

How to get column key constraints from the table : INFORMATION_SCHEMA in SQL?

I just need to get the columns which has Primary Key, Foreign Key along with these details.

SELECT      COLUMN_NAME AS COLUMNNAME,  
            DATA_TYPE AS DATATYPE,
            CHARACTER_MAXIMUM_LENGTH,
            IS_NULLABLE
FROM        INFORMATION_SCHEMA.COLUMNS  
WHERE       TABLE_NAME = 'My_Table_Name'
goofyui
  • 3,362
  • 20
  • 72
  • 128
  • [How do you list the primary key of a SQL Server table?](https://stackoverflow.com/q/95967/5221149). Found by searching for `INFORMATION_SCHEMA primary key` – Andreas Jul 05 '19 at 21:38
  • 1
    [How do I see all foreign keys to a table or column?](https://stackoverflow.com/q/201621/5221149). Found by searching for `INFORMATION_SCHEMA foreign key` – Andreas Jul 05 '19 at 21:39
  • Define your goal CLEARLY. Do you want all constraints? Or just the subset that can be considered "keys" (primary, foreign, unique)? Do you consider unique constraints and unique indexes the same? – SMor Jul 06 '19 at 02:42
  • @Andreas, earlier posting was helpful – goofyui Jul 06 '19 at 02:50

2 Answers2

1

This may help...

    USE AdventureWorks2012
    GO
    SELECT t.CONSTRAINT_NAME,t.TABLE_NAME,t.CONSTRAINT_TYPE,c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c ON t.CONSTRAINT_NAME = c.CONSTRAINT_NAME
   -- WHERE    t.TABLE_NAME = 'ProductVendor'
        -- AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'

enter image description here

Khairul Alam
  • 1,266
  • 2
  • 11
  • 31
  • I have the query which returns .. index names for all the columns.. just correction required in my script logic SELECT a.COLUMN_NAME AS COLUMNNAME, a.DATA_TYPE AS DATATYPE, a.CHARACTER_MAXIMUM_LENGTH, a.IS_NULLABLE, d.name as IndexName FROM INFORMATION_SCHEMA.COLUMNS a inner join sys.tables b on a.TABLE_NAME = b.name inner join sys.index_columns c on c.object_id = b.object_id inner join sys.indexes d on c.object_id = d.object_id and d.index_id = c.index_id WHERE a.TABLE_NAME = 'My_Table' – goofyui Jul 06 '19 at 18:04
  • posting on https://stackoverflow.com/questions/56926314/how-to-get-primary-key-or-key-constraint-details-for-the-columns-from-informatio helped me a lot – goofyui Jul 09 '19 at 18:35
-1

Try the sys.indexes and sys.index_columns

This has been well answered before : List of all index & index columns in SQL Server DB

Pho
  • 98
  • 1
  • 9
  • It is little tricky .. sys.index returns only the columns which has key on it .. we have columns which does not have index or keys.. above query which i posted returns all the columns available for the table along with their data type and null or not null.. details.. I am trying to get index / key along with those existing details – goofyui Jul 05 '19 at 21:37
  • @goofyui What about primary keys and foreign keys that consist of multiple columns? You cannot just list those *with* the column. – Andreas Jul 05 '19 at 21:42