Find all system objects that have column name = X

• MadAdmin

  SSChampion

  Points: 11261

  More actions

  June 15, 2018 at 9:23 am

  #370808

  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]

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 15, 2018 at 9:34 am

  #1994133

  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';


• MadAdmin

  SSChampion

  Points: 11261

  More actions

  June 15, 2018 at 9:46 am

  #1994135

  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]

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 15, 2018 at 11:39 am

  #1994170

  You are welcome.