查看被锁表：
--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插入指定数据 <