Чтобы получить информацию о заблокированных таблицах в Microsoft SQL Server, вы можете использовать различные методы. Вот несколько подходов с примерами кода:

Метод 1. Используйте представление sys.dm_tran_locks

SELECT 
    object_name(p.object_id) AS TableName,
    resource_type AS ResourceType,
    request_session_id AS SessionID,
    request_mode AS LockMode,
    request_status AS LockStatus
FROM 
    sys.dm_tran_locks l
    JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
WHERE 
    resource_type = 'OBJECT'

Метод 2. Запрос представлений sys.dm_exec_requests и sys.dm_exec_sessions

SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    r.session_id AS SessionID,
    r.blocking_session_id AS BlockingSessionID,
    r.wait_type AS WaitType,
    s.login_name AS LoginName
FROM 
    sys.dm_exec_requests r
    JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
    JOIN sys.partitions p ON p.hobt_id = r.resource_associated_entity_id
WHERE 
    r.blocking_session_id > 0

Метод 3: использовать системную хранимую процедуру sp_lock (не поддерживается, начиная с SQL Server 2005)

EXEC sp_lock

Метод 4. Запрос представлений sys.dm_tran_database_transactions и sys.dm_tran_locks

SELECT 
    DB_NAME(dt.database_id) AS DatabaseName,
    OBJECT_NAME(p.object_id) AS TableName,
    l.request_session_id AS SessionID,
    l.request_mode AS LockMode
FROM 
    sys.dm_tran_database_transactions dt
    JOIN sys.dm_tran_locks l ON dt.transaction_id = l.request_transaction_id
    JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
WHERE 
    dt.database_transaction_state = 4

Метод 5. Запрос представлений sys.dm_os_waiting_tasks и sys.dm_exec_sessions

SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    w.session_id AS SessionID,
    w.wait_duration_ms AS WaitDuration,
    s.login_name AS LoginName
FROM 
    sys.dm_os_waiting_tasks w
    JOIN sys.dm_exec_sessions s ON w.session_id = s.session_id
    JOIN sys.partitions p ON p.hobt_id = w.resource_description
WHERE 
    w.wait_type LIKE 'LCK%'