How to mount a select that brings information of which column is PK, which is not, and which is FK

Question

I need a TSQL bring me some table information data as which column is IS_NULLABLE, COLUMN_NAME, and so on, but I came across a problem, I can’t bring a column in a CASE WHEN which says that such column is PK, which is FK, if it has, and if the column has no key CASE WHEN should bring as NULL in select.

I have this select working on Mysql already, but can’t play or better mount it on SQL Server.

Attempt on SQL Server:

SELECT
     UPPER(AA.COLUMN_NAME) COLUMN_NAME,
     LOWER(AA.IS_NULLABLE) IS_NULLABLE,
     LOWER(AA.DATA_TYPE) DATA_TYPE,
     LOWER(AA.CHARACTER_MAXIMUM_LENGTH) MAXIMUM_LENGTH
     -- SERIA AQUI O 'CASE WHEN' IGUAL AO MYSQL
FROM 
    INFORMATION_SCHEMA.COLUMNS AA
WHERE 
    AA.TABLE_NAME = '<TABLE_NAME>'
ORDER BY 
    ORDINAL_POSITION ASC

Functional version in Mysql:

SELECT
     UPPER(COLUMN_NAME) COLUMN_NAME,
     LOWER(IS_NULLABLE) IS_NULLABLE,
     LOWER(DATA_TYPE) DATA_TYPE,
     LOWER(CHARACTER_MAXIMUM_LENGTH) MAXIMUM_LENGTH,
     CASE" +
        WHEN LOWER(COLUMN_KEY) = 'pri' THEN 'pk'
        WHEN LOWER(COLUMN_KEY) = 'mul' THEN 'fk'
        WHEN LOWER(COLUMN_KEY) = NULL THEN NULL 
     END AS COLUMN_KEY 
FROM 
    INFORMATION_SCHEMA.COLUMNS A 
WHERE 
    A.TABLE_SCHEMA = '<MEU_SCHEMA>' AND A.TABLE_NAME = '<TABLE_NAME>'
ORDER BY 
    A.ORDINAL_POSITION ASC

Answer 1

You have another option, but using INFORMATION_SCHEMA you can do it as follows:

SELECT  UPPER(C.COLUMN_NAME) COLUMN_NAME
       ,LOWER(C.IS_NULLABLE) IS_NULLABLE
       ,LOWER(C.DATA_TYPE) DATA_TYPE
       ,LOWER(C.CHARACTER_MAXIMUM_LENGTH) MAXIMUM_LENGTH
       ,CASE 
          WHEN CCU.CONSTRAINT_NAME IS NOT NULL THEN TC.CONSTRAINT_TYPE 
          ELSE NULL
        END CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C
        ON C.TABLE_NAME = T.TABLE_NAME
       AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
       AND C.TABLE_CATALOG = T.TABLE_CATALOG
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
       ON CCU.TABLE_NAME = C.TABLE_NAME
      AND CCU.TABLE_SCHEMA = C.TABLE_SCHEMA
      AND CCU.TABLE_CATALOG = C.TABLE_CATALOG
      AND CCU.COLUMN_NAME = C.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINT TC
       ON TC.TABLE_NAME = CCU.TABLE_NAME
      AND TC.TABLE_SCHEMA = CCU.TABLE_SCHEMA
      AND TC.TABLE_CATALOG = CCU.TABLE_CATALOG
      AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
WHERE T.TABLE_NAME = 'XPTO'
  AND T.TABLE_SCHEMA = 'YYYY'
  AND T.TABLE_CATALOG = 'ZZZZ'
ORDER BY C.ORDINAL_POSITION ASC

Answer 2

To use in an SQL Server database, the ideal is to use the "Information_schema" but on the tables:

Follow a T-SQL script for your use:

SELECT KCU.table_name,

KCU.column_name field_name,

TC.constraint_type,

CASE TC.is_deferrable WHEN 'NO' THEN 0 ELSE 1 END 'is_deferrable',

CASE TC.initially_deferred WHEN 'NO' THEN 0 ELSE 1 END 'is_deferred',

rc.match_option 'match_type',

rc.update_rule 'on_update',

rc.delete_rule 'on_delete',

ccu.table_name 'references_table',

ccu.column_name 'references_field',

KCU.ordinal_position 'field_position'

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU

LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

ON KCU.table_name =TC.table_name

AND KCU.table_schema =TC.table_schema

AND KCU.table_catalog =TC.table_catalog

AND KCU.constraint_catalog =TC.constraint_catalog

AND KCU.constraint_name =TC.constraint_name

LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc

ON rc.constraint_schema =TC.constraint_schema

AND rc.constraint_catalog =TC.constraint_catalog

AND rc.constraint_name =TC.constraint_name

LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu

ON rc.unique_constraint_schema = ccu.constraint_schema

AND rc.unique_constraint_catalog = ccu.constraint_catalog

AND rc.unique_constraint_name = ccu.constraint_name

WHERE KCU.constraint_catalog = DB_NAME()

ORDER BY KCU.constraint_name,

KCU.ordinal_position;

For more information see:

https://msdn.microsoft.com/pt-br/library/ms186778(v=sql.110). aspx