自己写来玩的，只做测试用。使用sys.dm_tran_locks表Resource_description列值。完全不排除不准确的可能
只适用Clustered Index，Non-Clustered Index使用DBCC PAGE更快一些。
1 IF OBJECT_ID('USP_PKHash') IS NOT NULL
2 DROP PROC USP_PKHash
3 GO
4 CREATE PROC USP_PKHash
5 @TableName AS NVARCHAR(255),
6 @PrimaryKeyColumn AS NVARCHAR(255),
7 @Filter AS NVARCHAR(2000) = N''
8 AS
9 BEGIN
10 DECLARE @SQL NVARCHAR(2000)
11 SET @SQL =
12 'CREATE TABLE #_HASH(ID INT, HashValue varchar(255))
13 SET NOCOUNT ON
14 DECLARE @ID INT
15 DECLARE @HASH VARCHAR(255)
16 DECLARE CUR_X CURSOR SCROLL_LOCKS FOR
17 SELECT _PrimaryKeyColumn_ From _TableName_ _FILTER_
18 OPEN CUR_X
19 FETCH NEXT FROM CUR_X INTO @ID
20 WHILE @@FETCH_STATUS = 0
21 BEGIN
22 BEGIN TRY
23 SELECT @HASH = RTRIM(RESOURCE_DESCRIPTION) FROM SYS.DM_TRAN_LOCKS WHERE RESOURCE_TYPE = ''KEY''
24 END TRY
25 BEGIN CATCH
26 RAISERROR (N''More than 1 KEY LOCK detected in sys.dm_tran_locks , Procedure Ended'', 11, 1)
27 RETURN
28 END CATCH;
29
30 INSERT INTO #_HASH VALUES(@ID, @HASH);
31
32 FETCH NEXT FROM CUR_X INTO @ID
33 END
34 CLOSE CUR_X
35 DEALLOCATE CUR_X
36
37 SET NOCOUNT OFF
38 SELECT * FROM #_HASH
39 DROP TABLE #_HASH';
40 IF EXISTS(
41 SELECT 1/0 AS COL
42 FROM SYS.DM_TRAN_LOCKS L JOIN SYS.PARTITIONS P
43 ON L.RESOURCE_ASSOCIATED_ENTITY_ID = P.HOBT_ID
44 WHERE L.RESOURCE_TYPE = 'KEY' AND OBJECT_NAME(P.[OBJECT_ID]) = @TableName)
45 BEGIN
46 RAISERROR(N'KEY LOCK detected in sys.dm_tran_locks Before Execution, Procedure Ended.', 11, 1)
47 RETURN
48 END
49
50 SET @SQL = REPLACE(@SQL, '_PrimaryKeyColumn_', @PrimaryKeyColumn)
51 SET @SQL = REPLACE(@SQL, '_TableName_', @TableName)
52 IF @Filter <> N''
53 SET @SQL = REPLACE(@SQL, '_FILTER_', N'WHERE ' + @Filter)
54
55 EXEC SP_EXECUTESQL @SQL
56 END
57 GO
usp_PKHash N'TableName', N'PKColumnName', N'Filter'
