Post reply

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

  • July 19, 2022 at 4:31 pm

    #4065419

    Attached result for above query.

     

    Attachments:
    You must be logged in to view attached files.

  • July 19, 2022 at 4:32 pm

    #4065421

    Attached result.

    Attachments:
    You must be logged in to view attached files.

  • July 19, 2022 at 4:50 pm

    #4065427

    Okay, that did shed more light on the issue.

    It seems the view and tableB reside in different databases. This is a problems, since the sys.xxx files do not span databases (even if those databases are on the same server instance) and therefore the join on object_id fails.

    I don't know how to overcome that restriction.

  • July 20, 2022 at 1:32 pm

    #4065959

    NO,

    All the tables resides in same database.

  • July 20, 2022 at 3:26 pm

    #4066018

    adisql wrote:

    NO,

    All the tables resides in same database.

    Are you absolutely sure of that?

    Because according to the documentation the columns referenced_server_name, referenced_schema_name and referenced_entity_name are only not-null if an object is on a different server or in a different database.

    sys.sql_expression_dependencies (Transact-SQL) - SQL Server | Microsoft Docs

    The r-1.png image shows these columns as filled out for TableB. This indicates that the table is not in the local database.

    • This reply was modified 2 years, 4 months ago by  kaj. Reason: Typo: shure --> sure

  • July 20, 2022 at 8:49 pm

    #4066165

    All the objects are resides in same database.

  • July 21, 2022 at 7:57 am

    #4066321

    Yes, but do you have a copy of the database on a different server, and access the table in the remote copy from the view (using four-part identification and linked server)? That is the only possible reason I can think of.

  • July 21, 2022 at 3:17 pm

    #4066491

    No.

    The table and view are from same database .

    The table is used in view .

    I just want to know if the table is used in view or not.

  • July 27, 2022 at 2:40 pm

    #4068984

    is it possible to  add  Row_Count and HasTrigger to below select list.

    Thank you.

     

    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.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

    FROM sys.tables t

  • July 28, 2022 at 5:26 pm

    #4069598

    Try 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 ('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.indexes i
        JOIN sys.partitions p ON p.object_id=i.object_id AND p.index_id=i.index_id
        WHERE i.index_id IN (0,1)
        AND i.object_id=t.object_id
        ) AS tr

  • July 28, 2022 at 6:08 pm

    #4069605

    You don't really need sys.indexes in the row-count query:

    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

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • July 28, 2022 at 7:04 pm

    #4069647

    ScottPletcher wrote:

    You don't really need sys.indexes in the row-count query:

    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

    Right you are! 🙂

    Thanks for pointing that out. No need to make it more complicated than necessary.

     

  • August 11, 2022 at 1:28 pm

    #4075219

    Thanks Kaj.

    Actually i would like to add  HasTriggerDependency to the select list.

    Can you please suggest .

    Thank you.

  • August 11, 2022 at 3:21 pm

    #4075260

    adisql wrote:

    Thanks Kaj.

    Actually i would like to add  HasTriggerDependency to the select list.

    Can you please suggest .

    Thank you.

    It doesn't work in the supplied query? I mean, I already added the trigger reference along with the rowcount, as requested.

     

  • August 11, 2022 at 10:42 pm

    #4075388

    That selects if the table has trigger or not.but I am looking for the table has trigger dependency ? I mean if the table used in other table's trigger definition.

Viewing 15 posts - 16 through 30 (of 31 total)

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