Hi, I'm trying to get object names in one query where the objects comefrom multiple databases. The scope will likely be master when running from a response job to a blocking alert. Is there a trick I can use to avoid having to loop though each database andusing a database scope specific query?I created a function as an example of what I'd like to do (e.g., database id as a parameter), butthis will not work.Perhaps OPENQUERY or something? If somebody has done what I would like, I'd appreciate a tip. Thanks, Randy

SELECT sys.dm_tran_locks.*,

dbo.fn_translockobjectid(

sys.dm_tran_locks.resource_type,

sys.dm_tran_locks.resource_database_id,

sys.dm_tran_locks.resource_associated_entity_id

) as [ObjectId]

FROM sys.dm_tran_locks WITH (NOLOCK)

INNER JOIN sys.dm_os_waiting_tasks wt WITH (NOLOCK)

ON wt.resource_address = sys.dm_tran_locks.lock_owner_address

CREATE FUNCTION dbo.fn_translockobjectid(

@resource_type nvarchar(120),

@resource_database_id int,

@resource_associated_entity_id bigint

)

RETURNS int

AS

BEGIN

DECLARE @ObjectId int,

@SQL nvarchar(max);

SET @SQL = N'USE ' + DB_NAME(@resource_database_id) + N'

DECLARE @ObjectId int;

SET @ObjectId = CASE

WHEN @resource_type = ''OBJECT''

THEN @resource_associated_entity_id

WHEN @resource_type IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')

THEN (SELECT object_id FROM sys.partitions

WHERE hobt_id = @resource_associated_entity_id)

WHEN @resource_type = ''ALLOCATION_UNIT''

THEN (

SELECT CASE

WHEN type IN (1, 3)

THEN (SELECT object_id FROM sys.partitions

WHERE hobt_id = allocation_unit_id)

WHEN type = 2

THEN (SELECT object_id FROM sys.partitions

WHERE partition_id = allocation_unit_id)

ELSE NULL

END

FROM sys.allocation_units

WHERE allocation_unit_id = @resource_associated_entity_id

)

ELSE NULL

END;

SELECT @ObjectId;'

EXEC dbo.sp_executeSQL

@SQL

, N'@resource_type nvarchar(120)

, @resource_associated_entity_id int

, @ObjectId int OUTPUT'

, @resource_type = @resource_type

, @resource_associated_entity_id = @resource_associated_entity_id

, @ObjectId = @ObjectId OUTPUT

RETURN @ObjectId

END

Hi,

I think you first create linked server of different database which are you using in query and after that you can use the different database tables in one query or views.

-- Detailed blocking information with query information along with database name
SELECT
owt.session_id AS waiting_session_id,
    owt.blocking_session_id,
DB_NAME(tls.resource_database_id) AS database_name,
    (SELECT SUBSTRING(est.[text], ers.statement_start_offset/2 + 1,
(CASE WHEN ers.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
ELSE ers.statement_end_offset
END
- ers.statement_start_offset
) / 2)
FROM sys.dm_exec_sql_text(ers.[sql_handle]) AS est) AS waiting_query_text,
CASE WHEN owt.blocking_session_id > 0 
THEN (
SELECT
est.[text] FROM sys.sysprocesses AS sp
CROSS APPLY sys.dm_exec_sql_text(sp.[sql_handle]) as est
WHERE sp.spid = owt.blocking_session_id)
ELSE
NULL
END AS blocking_query_text,
    (CASE tls.resource_type
WHEN 'OBJECT' THEN OBJECT_NAME(tls.resource_associated_entity_id, tls.resource_database_id)
WHEN 'DATABASE' THEN DB_NAME(tls.resource_database_id)
ELSE (SELECT  OBJECT_NAME(pat.[object_id], tls.resource_database_id)
FROM sys.partitions pat WHERE pat.hobt_id = tls.resource_associated_entity_id)

END
) AS object_name,
owt.wait_duration_ms,
owt.waiting_task_address,
owt.wait_type,
tls.resource_associated_entity_id,
tls.resource_description AS local_resource_description,
tls.resource_type,
tls.request_mode,
tls.request_type,
tls.request_session_id,
owt.resource_description AS blocking_resource_description,
qp.query_plan AS waiting_query_plan
FROM sys.dm_tran_locks AS tls
INNER JOIN sys.dm_os_waiting_tasks owt ON tls.lock_owner_address = owt.resource_address
INNER JOIN sys.dm_exec_requests ers ON tls.request_request_id = ers.request_id AND owt.session_id = ers.session_id
OUTER APPLY sys.dm_exec_query_plan(ers.[plan_handle]) AS qp
GO