I am writing a query to get all the tables in the db with their properties but also foreign keys and table that each reference to. I have a following query but it is taking 60 min to complete. Not sure what am I doing wrong?

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 

It is taking time to find the foreign table names. What is the best way to get db table schema

CodePudding user response：

• Firstly, you should stick to the sys schema, as these catalog views are more efficient, and INFORMATION_SCHEMA is only for compatibility
• You are over-complicating it. You don't need to query the foreign key twice, you can just check if the LEFT JOIN succeeded by checking for 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;