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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply