June 15, 2018 at 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?
June 15, 2018 at 9:34 am
MadAdmin - Friday, June 15, 2018 9:23 AMI would like to find all DMV's functions, anything that contains, say, object_id, or HOBT_ID OR session_idIn 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';
June 15, 2018 at 9:46 am
Lynn Pettis - Friday, June 15, 2018 9:34 AMMadAdmin - Friday, June 15, 2018 9:23 AMI would like to find all DMV's functions, anything that contains, say, object_id, or HOBT_ID OR session_idIn 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.
June 15, 2018 at 11:39 am
You are welcome.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy