CODEWITHSUNDEEP
sqlsql-server
HamedFathi
HamedFathi

Reputation: 3969

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)

Upvotes: 1

Views: 484

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

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);

To get the IsView you need to add INFORMATION_SCHEMA.TABLES to the query:

SELECT t2.TABLE_CATALOG,
    t2.TABLE_SCHEMA,
    t2.TABLE_NAME,
    t2.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(t2.TABLE_NAME), t2.COLUMN_NAME, 'IsIdentity') = 1 THEN 'YES' ELSE 'NO' END As IS_IDENTITY,
    CASE WHEN TABLE_TYPE = 'VIEW' THEN 'YES' ELSE 'NO' END As IS_VIEW
FROM [INFORMATION_SCHEMA].[TABLES] t1
INNER JOIN [INFORMATION_SCHEMA].[COLUMNS] t2 ON 
(
    t1.TABLE_CATALOG = t2.TABLE_CATALOG
    AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA 
    AND t1.TABLE_NAME = t2.TABLE_NAME
)
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] t3 ON(
    t2.TABLE_CATALOG = t3.TABLE_CATALOG
    AND t2.TABLE_SCHEMA = t3.TABLE_SCHEMA
    AND t2.TABLE_NAME = t3.TABLE_NAME
    AND t2.COLUMN_NAME = t3.COLUMN_NAME
);

Upvotes: 1

Related Questions