How to select database name in sys.dm_*

Question

I'm calling stored proc through a service. I wanted my stored proc to be running on a database. I've two questions

1. Is sys.dm_* tables specific to a database or it is general one?

select * FROM sys.dm_exec_requests

Does above query return requests of all the databases or the current database where the query is being executed?

2. If sys.dm_* specific to a database is there any way to specify the database through stored procedure. I'm aware that we cannot use use. and select * FROM [dbname].sys.dm_exec_requests isn't working it seems. It returns the result for the current DB it seems. I may be wrong here.

Answer 1

It looks like the dm_* tables are global to the system. The statement select * from [dbname].sys.dm_exec_requests returns data for all databases on the server. If the table contains database specific data, then it will include a database_id field.

If you want to limit the data your stored procedure returns to just one database, then you have to qualify by database_id. Both of the following statements work in stored procedures:

/* specify the database name manually */
declare @db_name sysname = 'master'

select *
from   sys.dm_exec_requests er
       inner join sys.databases d on er.database_id = d.database_id
where  d.name = @db_name

/* gets the database name for the current database */
select *
from   sys.dm_exec_requests er
       inner join sys.databases d on er.database_id = d.database_id
where  d.name = db_name()