優化長查詢以查找帶有外鍵和表的資料庫模式

我正在撰寫一個查詢來獲取資料庫中的所有表及其屬性以及每個參考的外鍵和表。我有以下查詢，但需要 60 分鐘才能完成。不確定我做錯了什么？

SELECT 
    ist.table_name,
    isc.column_name,
    data_type,
    character_maximum_length,
    Columnproperty(Object_id(isc.table_name), 
    isc.column_name, 'IsIdentity') AS identityFlag,
    is_nullable,
    character_maximum_length,
    numeric_scale,
    (SELECT 1
     FROM information_schema.table_constraints AS C
     JOIN information_schema.key_column_usage AS K ON C.table_name = K.table_name
                                                   AND C.constraint_catalog = K.constraint_catalog
                                                   AND C.constraint_schema = K.constraint_schema
                                                   AND C.constraint_name = K.constraint_name
     WHERE C.constraint_type = 'PRIMARY KEY'
       AND C.table_name = isc.table_name
       AND K.column_name = isc.column_name) AS primarykey,
    (SELECT TOP 1 1
     FROM information_schema.table_constraints AS C
     JOIN information_schema.key_column_usage AS K ON C.table_name = K.table_name
                                                   AND C.constraint_catalog = K.constraint_catalog
                                                   AND C.constraint_schema = K.constraint_schema
                                                   AND C.constraint_name = K.constraint_name
     WHERE C.constraint_type = 'FOREIGN KEY'
       AND C.table_name = isc.table_name
       AND K.column_name = isc.column_name) AS foreignKey,
    a.parenttablename AS foreignTableName,   
    a.colname AS foreignColumnName
FROM   
    information_schema.tables ist
INNER JOIN 
    information_schema.columns isc ON ist.table_name = isc.table_name
LEFT OUTER JOIN 
    (SELECT 
         t.NAME AS parentTableName,
         Object_name(f.parent_object_id) TableName,
         Col_name(fc.parent_object_id, fc.parent_column_id) ColName
     FROM   
         sys.foreign_keys AS f
     INNER JOIN 
         sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
     INNER JOIN 
         sys.tables t ON t.object_id = fc.referenced_object_id) a ON a.tablename = ist.table_name
                                                                  AND a.colname = isc.column_name 

查找外部表名需要時間。獲取資料庫表架構的最佳方法是什么

uj5u.com熱心網友回復：

• 首先，你應該堅持sys模式，因為這些目錄視圖更有效，并且INFORMATION_SCHEMA只是為了兼容性
• 你把它復雜化了。您不需要兩次查詢外鍵，您可以LEFT JOIN通過檢查是否成功來檢查NULL

SELECT 
    t.name AS table_name,
    c.name AS column_name,
    typ.name AS data_type,
    c.max_length,
    c.is_identity AS identityFlag,
    c.is_nullable,
    c.precision,
    c.scale,
    CASE WHEN EXISTS (SELECT 1
        FROM sys.indexes AS i
        JOIN sys.index_columns AS ic
              ON ic.object_id = i.object_id
             AND ic.index_id = i.index_id
        WHERE i.object_id = t.object_id
          AND ic.column_id = c.column_id
        ) THEN 1 ELSE 0 END AS primarykey,
    CASE WHEN t_f.name IS NOT NULL THEN 1 ELSE 0 END AS foreignKey,
    t_f.name AS foreignTableName,   
    c_f.name AS foreignColumnName
FROM   
    sys.tables t
INNER JOIN 
    sys.columns c ON c.object_id = t.object_id
INNER JOIN
    sys.types typ ON typ.user_type_id = c.user_type_id
LEFT OUTER JOIN
    sys.foreign_key_columns AS fc
    INNER JOIN 
        sys.tables t_f ON t_f.object_id = fc.referenced_object_id
    INNER JOIN
        sys.columns c_f ON c_f.object_id = t_f.object_id
                       AND c_f.column_id = fc.referenced_column_id
  ON fc.parent_object_id = t.object_id
  AND fc.parent_column_id = c.column_id;

標籤：sql-server 数据库 查询语句

上一篇：SQLServer中“groupby”子句中的“Case”

下一篇：撰寫TSQL查詢的最佳方式