查看被锁表:
--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插入指定数据
查询表是否有表分区
select * from sys.partitions where object_name(object_id)='表名'
为空时没有
查询表关联的外键
SELECT * FROM sys.foreign_keys
WHERE referenced_object_id=OBJECT_ID('表名');
根据外键查询表名
SELECT 主表名=object_name(b.rkeyid)
,主键列ID=b.rkey
,主键列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
,外键表ID=b.fkeyid
,外键表名称=object_name(b.fkeyid)
,外键列ID=b.fkey
,外键列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)
,级联更新=ObjectProperty(a.id,'CnstIsUpdateCascade')
,级联删除=ObjectProperty(a.id,'CnstIsDeleteCascade')
,外键名称=d.name
FROM sysobjects a
join sysforeignkeys b on a.id=b.constid
join sysobjects c on a.parent_obj=c.id
join sys.foreign_keys d on b.constid=d.object_id
where a.xtype='f' AND c.xtype='U'
and d.name='外键'
删除外键
ALTER TABLE 表名 DROP constraint 外键名
#创建序列
CREATE SEQUENCE SEC_TODO_CODE
AS decimal(3,0)
START WITH 125
INCREMENT BY 25
MINVALUE 100
MAXVALUE 200
CYCLE
CACHE 3
;
#使用序列
SELECT NEXT VALUE FOR SEC_TODO_CODE
#查询需要创建的索引 ,对上一次查询的操作
SELECT migs.group_handle, mid.*
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON (migs.group_handle = mig.index_group_handle)
INNER JOIN sys.dm_db_missing_index_details AS mid
ON (mig.index_handle = mid.index_handle)
WHERE migs.group_handle = 2
#查看表中索引
SELECT * FROM SYS.indexes
WHERE OBJECT_NAME(object_id)='索引名称'