Hamed Hamed - 6 months ago 15
SQL Question

How get information about tables in SQL Server?

I have three queries but I want to join them for creating one result

Can anyone help me ?

1)

SELECT TABLE_CATALOG ,
TABLE_SCHEMA ,
TABLE_NAME ,
COLUMN_NAME ,
DATA_TYPE ,
CHARACTER_MAXIMUM_LENGTH ,
IS_NULLABLE
FROM [INFORMATION_SCHEMA].[COLUMNS] t1;


2)

SELECT t2.COLUMN_NAME, t2.TABLE_NAME ,t2.TABLE_SCHEMA
FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] t2
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME),
'IsPrimaryKey') = 1;


3)

SELECT COLUMN_NAME ,
TABLE_NAME ,
TABLE_SCHEMA
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1


and Result that I need

TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
IsPrimaryKey, -- from query 2 (YES/NO)
IsIdentity -- from query 3 (YES/NO)

Answer

You can use a LEFT JOIN between INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE and CASE to produce this output:

SELECT t1.TABLE_CATALOG ,
    t1.TABLE_SCHEMA ,
    t1.TABLE_NAME ,
    t1.COLUMN_NAME ,
    DATA_TYPE ,
    CHARACTER_MAXIMUM_LENGTH ,
    IS_NULLABLE,
    CASE WHEN OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1 THEN 'YES' ELSE 'NO' END As IS_PRIMARY_KEY,
    CASE WHEN COLUMNPROPERTY(OBJECT_ID(t1.TABLE_NAME), t1.COLUMN_NAME, 'IsIdentity') = 1 THEN 'YES' ELSE 'NO' END As IS_IDENTITY
FROM [INFORMATION_SCHEMA].[COLUMNS] t1
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] t2
ON(t1.TABLE_CATALOG = t2.TABLE_CATALOG
AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA
AND t1.TABLE_NAME = t2.TABLE_NAME
AND t1.COLUMN_NAME = t2.COLUMN_NAME);