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

Question

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

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

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

I have this select working in MySQL already, but I can not play it or better mount it in 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

sql sql-server

asked by anonymous 17.06.2015 / 03:12

2 answers

accessing apps on heroku giving error How to validate if there is any file to upload?

score 1 · Answer 1

You have another option, but using INFORMATION_SCHEMA you can do the following:

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

score 0 · Answer 2

To use in a SQL Server database, it is best 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:

link