Find all system objects that have column name = X

  • I would like to find all DMV's functions, anything that contains, say, object_id, or HOBT_ID OR session_id

    In normal SQL land, you can do this 
    select s.name+'.'+o.name from sys.objects o
    inner join sys.columns c on c.object_id = o.object_id
    inner join sys.schemas s on s.schema_id = o.schema_id
    WHERE C.name = 'applicationid'

    and this will return sales.applications, sales.applicationHistory etc.

    Is there a way to do the same for system objects?
    e.g. I would like <sys.objects, sys.indexes, sys.partitions,sys.dm_exec_procedure_stats,sys.dm_db_index_operation_stats>  etc to be in the result set when I query for columns where the columnName = 'object_id'
    Anyone have an idea how this can be done?

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • MadAdmin - Friday, June 15, 2018 9:23 AM

    I would like to find all DMV's functions, anything that contains, say, object_id, or HOBT_ID OR session_id

    In normal SQL land, you can do this 
    select s.name+'.'+o.name from sys.objects o
    inner join sys.columns c on c.object_id = o.object_id
    inner join sys.schemas s on s.schema_id = o.schema_id
    WHERE C.name = 'applicationid'

    and this will return sales.applications, sales.applicationHistory etc.

    Is there a way to do the same for system objects?
    e.g. I would like <sys.objects, sys.indexes, sys.partitions,sys.dm_exec_procedure_stats,sys.dm_db_index_operation_stats>  etc to be in the result set when I query for columns where the columnName = 'object_id'
    Anyone have an idea how this can be done?

    This?

    SELECT
      .[name] + '.' + [o].[name]
    FROM
      [sys].[all_objects]            [o]
      INNER JOIN [sys].[all_columns] [c]
        ON [c].[object_id] = [o].[object_id]
      INNER JOIN [sys].[schemas]
        ON .[schema_id] = [o].[schema_id]
    WHERE
      [c].[name] = 'object_id';

  • Lynn Pettis - Friday, June 15, 2018 9:34 AM

    MadAdmin - Friday, June 15, 2018 9:23 AM

    I would like to find all DMV's functions, anything that contains, say, object_id, or HOBT_ID OR session_id

    In normal SQL land, you can do this 
    select s.name+'.'+o.name from sys.objects o
    inner join sys.columns c on c.object_id = o.object_id
    inner join sys.schemas s on s.schema_id = o.schema_id
    WHERE C.name = 'applicationid'

    and this will return sales.applications, sales.applicationHistory etc.

    Is there a way to do the same for system objects?
    e.g. I would like <sys.objects, sys.indexes, sys.partitions,sys.dm_exec_procedure_stats,sys.dm_db_index_operation_stats>  etc to be in the result set when I query for columns where the columnName = 'object_id'
    Anyone have an idea how this can be done?

    This?

    SELECT
      .[name] + '.' + [o].[name]
    FROM
      [sys].[all_objects]            [o]
      INNER JOIN [sys].[all_columns] [c]
        ON [c].[object_id] = [o].[object_id]
      INNER JOIN [sys].[schemas]
        ON .[schema_id] = [o].[schema_id]
    WHERE
      [c].[name] = 'object_id';

    Lynn. You are worth you weight in gold. 
    Cant believe that dmv existed since 2008. All_object i mean.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • You are welcome.

Viewing 4 posts - 1 through 3 (of 3 total)

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