Post reply

query that will return the information of every table in a database

  • August 12, 2022 at 6:32 pm

    #4075852

    Hmmm... maybe like this?

    SELECT
        CONCAT(SCHEMA_NAME(t.schema_id), '.', t.name) AS TableName
        , t.create_date AS CreatedDate
        , t.modify_date AS LastModifiedDate
        , CAST(CASE WHEN EXISTS (SELECT * FROM sys.indexes WHERE object_id=t.object_id AND is_primary_key = 1) THEN 1 ELSE 0 END AS BIT) AS HasPkey
        , CAST(CASE WHEN EXISTS (SELECT * FROM sys.indexes WHERE object_id=t.object_id AND type = 2) THEN 1 ELSE 0 END AS BIT) AS HasNonClu
        , CAST(CASE WHEN EXISTS (SELECT * FROM sys.indexes WHERE object_id=t.object_id AND type = 1) THEN 1 ELSE 0 END AS BIT) AS HasClu
        , CAST(CASE WHEN EXISTS (SELECT * FROM sys.Foreign_Keys WHERE parent_object_id=t.object_id) THEN 1 ELSE 0 END AS BIT) AS HasFKey
        , CAST(CASE WHEN EXISTS (SELECT * FROM sys.triggers WHERE parent_id=t.object_id) THEN 1 ELSE 0 END AS BIT) AS HasTrig
        , CAST(CASE WHEN EXISTS (SELECT * FROM sys.sql_expression_dependencies d JOIN sys.objects o ON o.object_id=d.referencing_id AND o.type IN ('TA', 'TR') WHERE d.referenced_id=t.object_id ) THEN 1 ELSE 0 END AS BIT) AS HasTrigDep
        , CAST(CASE WHEN EXISTS (SELECT * FROM sys.sql_expression_dependencies d JOIN sys.objects o ON o.object_id=d.referencing_id AND o.type IN ('P','PC','X') WHERE d.referenced_id=t.object_id ) THEN 1 ELSE 0 END AS BIT) AS HasProcDep
        , CAST(CASE WHEN EXISTS (SELECT * FROM sys.sql_expression_dependencies d JOIN sys.objects o ON o.object_id=d.referencing_id AND o.type IN ('FN','FS','FT','IF','TF') WHERE d.referenced_id=t.object_id ) THEN 1 ELSE 0 END AS BIT) AS HasFuncDep
        , CAST(CASE WHEN EXISTS (SELECT * FROM sys.sql_expression_dependencies d JOIN sys.objects o ON o.object_id=d.referencing_id AND o.type = 'V' WHERE d.referenced_id=t.object_id ) THEN 1 ELSE 0 END AS BIT) AS HasViewDep
        , CAST(CASE WHEN EXISTS (SELECT * FROM sys.columns c JOIN sys.types ty ON ty.system_type_id=c.system_type_id AND ty.user_type_id=c.user_type_id WHERE c.object_id=t.object_id AND ty.name LIKE '%date%') THEN 1 ELSE 0 END AS BIT) AS HasDateField
        , tr.TotalRows
    FROM sys.tables t
    OUTER APPLY (
        SELECT SUM(p.rows) AS TotalRows
        FROM sys.partitions p
        WHERE p.object_id = t.object_id
        AND p.index_id IN (0,1)
        ) AS tr

  • August 14, 2022 at 11:37 am

    #4076209

    Thank you Kaj!

Viewing 2 posts - 31 through 31 (of 31 total)

You must be logged in to reply to this topic. Login to reply