如何获取有关SQL Server中表的信息？

debugcn 发表于 Dev

哈米德·法西（HamedFathi）

我有三个查询，但我想与他们一起创建一个结果

谁能帮我？

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

和我需要的结果

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)

佐哈尔·皮莱德（Zohar Peled）

您可以使用LEFT JOIN之间INFORMATION_SCHEMA.COLUMNS以及INFORMATION_SCHEMA.KEY_COLUMN_USAGE和CASE产生这种输出：

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

要获取IsView，您需要添加INFORMATION_SCHEMA.TABLES到查询中：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。