SQLSERVER查询锁表，阻塞，使用表名查询存储过程，行数，表字段类型等常规查询实例讲解

查看被锁表：

--spid 锁表进程

--tableName 被锁表名

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName

from sys.dm_tran_locks where resource_type='OBJECT'

-- 解锁：

declare @spid int

Set @spid = 57 --锁表进程

declare @sql varchar(1000)

set @sql='kill '+cast(@spid as varchar)

exec(@sql)

查询当前阻塞:

WITH CTE_SID ( BSID, SID, sql_handle )

AS ( SELECT blocking_session_id ,

session_id ,

sql_handle

FROM sys.dm_exec_requests

WHERE blocking_session_id <> 0

UNION ALL

SELECT A.blocking_session_id ,

A.session_id ,

A.sql_handle

FROM sys.dm_exec_requests A

JOIN CTE_SID B ON A.SESSION_ID = B.BSID

)

SELECT C.BSID ,

C.SID ,

S.login_name ,

S.host_name ,

S.status ,

S.cpu_time ,

S.memory_usage ,

S.last_request_start_time ,

S.last_request_end_time ,

S.logical_reads ,

S.row_count ,

q.text

FROM CTE_SID C

JOIN sys.dm_exec_sessions S ON C.sid = s.session_id

CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q

ORDER BY sid

使用表名查询存储过程:

select * from sys.all_sql_modules where definition like '%表名%'

查询表的列行数:

select count(*) from syscolumns s where s.id = object_id('表名');

查询表的字段类型:

select st.name as 'TypeName',sc.name,sc.length from syscolumns sc,systypes st

where sc.xtype=st.xtype

and sc.id in(

select id from sysobjects where xtype='U' and name='表名')

表自增列添加数据:

set identity_insert 表名 ON --允许对自增列Id插入指定数据

insert into table_name(Id,Name) values(1,'test')

set identity_insert 表名 OFF --关闭对自增列Id插入指定数据 <