欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-11-02 19:23:58
查看被锁表: --spid 锁表进程 --tableName 被锁表名 select request_session_id spid,OBJECT_NAME(resource...

查看被锁表:

--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插入指定数据 <