1

This SQL query prints table name, column name and column type (Data, varchar, etc).

Table_name Column_name Data_type

SELECT T.TABLE_NAME,C.COLUMN_NAME,C.DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS C
    INNER JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE T.TABLE_TYPE = 'BASE TABLE'

How to change it so that it also prints if this column is a part of compound primary key (see below)?

Table_name Column_Name Is_Primary(y/n) Data_Type

Update: The suggested duplicate is a far cry, because it talks about a single table. The accepted answer resolved my question.

sixtytrees
  • 1,156
  • 1
  • 10
  • 25
  • Possible duplicate of [How do you list the primary key of a SQL Server table?](http://stackoverflow.com/questions/95967/how-do-you-list-the-primary-key-of-a-sql-server-table) – Sean Lange Aug 16 '16 at 20:47
  • Not a duplicate. That answer deals with a single table. My question is about displaying a set of tables in a DB. – sixtytrees Aug 16 '16 at 20:54
  • Yeah I realize that but it is just a join to a system view and that question explains how to do that. Should not have been a far stretch to see that. – Sean Lange Aug 16 '16 at 21:03

1 Answers1

1

Constraint name and types are in INFORMATION_SCHEMA.TABLE_CONSTRAINTS and Column Name and Constraint Name are in INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE. So, basically you end up adding more JOINS.

SELECT
    T.TABLE_NAME,
    C.COLUMN_NAME,
    C.DATA_TYPE,
    CCU.Constraint_Name,
    CASE TC.Constraint_Type
        WHEN 'PRIMARY KEY' THEN 'Yes'
        ELSE 'No'
    END IsPrimaryKey
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.TABLES T
    ON C.TABLE_NAME = T.TABLE_NAME
    AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
    ON CCU.TABLE_SCHEMA = T.TABLE_SCHEMA
    AND CCU.TABLE_NAME = T.TABLE_NAME
    AND CCU.COLUMN_NAME = C.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    ON CCU.Constraint_Name = TC.Constraint_Name
    AND CCU.Table_Name = TC.Table_Name
    AND CCU.TABLE_SCHEMA = TC.TABLE_SCHEMA
WHERE T.TABLE_TYPE = 'BASE TABLE'
ORDER BY T.TABLE_NAME, C.COLUMN_NAME
techspider
  • 3,370
  • 13
  • 37
  • 61