SQLServer查询、搜索命令、语句SQL Server⾃带的系统存储过程sp_who和sp_lock也可以⽤来查阻塞和死锁
⼀. 阻塞查询 sp_lock
执⾏ exec sp_lock  下⾯列下关键字段
spid 是指进程ID，这个过滤掉了系统进程，只展⽰了⽤户进程spid>50。
dbid 指当前实例下的哪个数据库 , 使⽤DB_NAME() 函数来标识数据库
type 请求锁住的模式
mode 锁的请求状态
GRANT：已获取锁。
CNVRT：锁正在从另⼀种模式进⾏转换，但是转换被另⼀个持有锁（模式相冲突）的进程阻塞。
WAIT：锁被另⼀个持有锁（模式相冲突）的进程阻塞。
总结：当mode 不为GRANT状态时，需要了解当前锁的模式，以及通过进程ID查当前sql 语句
例如当前进程ID是416，且mode状态为WAIT 时，查看⽅式 DBCC INPUTBUFFER(416)
⽤sp_lock查询显⽰的信息量很少，也很难看出谁被谁阻塞。所以当数据库版本为2005及以上时不建议使⽤。
⼆.阻塞查询  dm_tran_locks
SELECT
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;
上⾯查询只显⽰有阻塞的会话，关注blocking_session_id 也就是被阻塞的会话ID，同样使⽤DBCC INPUTBUFFER来查询sql语句三.阻塞查询 sys.sysprocesses
SELECT
spid,
kpid,
blocked,
waittime AS'waitms',
lastwaittype,
DB_NAME(dbid)AS DB,
waitresource,
open_tran,
hostname,[program_name],
hostprocess,loginame,
[status]
FROM sys.sysprocesses WITH(NOLOCK)
WHERE    kpid>0AND[status]<>'sleeping'AND spid>50
sys.sysprocesses  能显⽰会话进程有多少，等待时间， open_tran有多少事务，阻塞会话是多少. 整体内容更为详细。
关键字段说明:
spid 会话ID（进程ID），SQL内部对⼀个连接的编号，⼀般来讲⼩于50
  kipid 线程ID
  blocked: 阻塞的进程ID，值⼤于0表⽰阻塞, 值为本⾝进程ID表⽰io操作
  waittime：当前等待时间（以毫秒为单位）。
  open_tran: 进程的打开事务数
  hostname：建⽴连接的客户端⼯作站的名称
  program_name 应⽤程序的名称。
  hostprocess ⼯作站进程 ID 号。
  loginame 登录名。
  [status]
    running = 会话正在运⾏⼀个或多个批
    background = 会话正在运⾏⼀个后台任务，例如死锁检测
    rollback = 会话具有正在处理的事务回滚
    pending = 会话正在等待⼯作线程变为可⽤
    runnable = 会话中的任务在等待，由scheduler来运⾏的可执⾏队列中。(重要)
    spinloop = 会话中的任务正在等待调节锁变为可⽤。
    suspended = 会话正在等待事件（如 I/O）完成。(重要)
    sleeping = 连接空闲
wait resource 格式为 fileid:pagenumber:rid 如(5:1:8235440)
kpid=0, waittime=0 空闲连接
kpid>0, waittime=0 运⾏状态
kpid>0, waittime>0 需要等待某个资源，才能继续执⾏,⼀般会是suspended（等待io）
kpid=0, waittime=0 但它还是阻塞的源头，查看open_tran>0 事务没有及时提交。
如果blocked>0，但waittime时间很短，说明阻塞时间不长，不严重
如果status 上有好⼏个runnable状态任务，需要认真对待。 cpu负荷过重没有及时处理⽤户的并发请求
/
*查询spid 对应的SQL*/
select er.session_id, AS varchar(255)) AS CallingSQL
from master.sys.dm_exec_requests er
WITH (NOLOCK)
CROSS APPLY MASTER.sys.fn_get_sql (er.sql_handle) csql
where er.session_id=@spid --session_id
--  更新、删除等事物锁查询
BEGIN TRAN
delete Tab where ID in(1,2)
--  列出锁信息
EXEC  sp_lock  @@spid
-
-  提交或者回滚事务
COMMIT TRAN
--SQL分页 offset fetch next⽅式（SQL2012以上的版本才⽀持：推荐使⽤）
select*from ArtistModels  order by ArtistId offset 4 rows fetch next5 rows only
--order by ArtistId offset 页数 rows fetch next 条数 rows only ----
--查询所有表
SELECT NAME,*FROM SYSOBJECTS WHERE XTYPE='U'order by SYSOBJECTS.name
--查询所有存储过程
select*from sysobjects where type='P'order by[name]
--查询表约束
exec sp_helpconstraint @objname=book_detail --@objname=prx_class
go
-- 查看内容(存储过程)
sp_helptext 'P_Sys_MenuList'
--查询所有触发器
select name from sysobjects where xtype='TR'
--查询所有视图
select name from sysobjects where xtype='V'
--查询触发器与关联表
SELECT tb2.name AS tableName,tb1.name AS triggerName FROM Sysobjects tb1 JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.id pe='TR'
/**判断是否存在该触发器**/
--if exists (select * from sysobjects where id=object_id(N'Trg_IC_Log_Insert1') and type='tr')
-- or
if (object_id('Trg_IC_Log_Insert1','tr') is not null )
drop TRIGGER[Trg_IC_Log_Insert1]
go
/*修改表命令*/
--移除主键
alter table tb_name drop constraint PK_name
--添加多列主键
alter table  tb_name  add constraint PK_name primary key(column1,column2,column3)
--修改表列名或属性 add 增加列,drop 移除列
alter table  tb_name alter column column1 varchar(10) null
-
-通过某个字段名称到数据库中对应的表
USE Works
GO
select DISTINCT object_name(id)
from syscolumns
where name like'%the_class%'order by1
GO
------------------------------------------
select*from sysobjects
where object_name(id) in (
'OE_ORDER_LINES_ALL',
'OE_ORDER_HEADERS_ALL',
'OE_TRANSACTION_TYPES_TL',
'FNDLOOKUPVALUES',
'HZ_CUST_SITE_USES_ALL',
'HZ_CUST_ACCT_SITES_ALL')
--sql 按默认顺序排序，⽣成RowNum列(⾏号列)
select identity(int,1,1) as sort,*into #temp from tb1
select*from #temp order by sort
sql 分组后根据最⼤值得到记录明细
select*from表名as a where exists (select1from
(select⼯单编号,max(处理时间) as处理时间from表名group by⼯单编号) as b where a.⼯单编号=b.⼯单编号and a.处理时间=b.处理时间)
SELECT*FROM
(
select row_number() over(partition by mn order by NotesTime DESC) as number,*from T_TaskNotes
) as tb where number=1
SQLServer: 解决“错误15023：当前数据库中已存在⽤户或⾓⾊”
Use newzs
go
sp_change_users_login 'update_one', 'infos1', 'infos1'
/*
其中newzs为存在孤⽴⽤户的数据库，update_one是存储过程的参数，表⽰只处理⼀个⽤户，前⼀个infos1是“⽤户”，后⼀个infos1是“登录”，以上这个SQL表⽰将服务器登录“infos1”与 newzs数据库⽤户“infos1”重新连接起来。这样就可以正常使⽤数据*/
/*数据库⼿动抛出错误异常*/
--内容级别
RAISERROR ('678 ''数据异常'' 已经结账' , 16, 1) WITH NOWAIT
RAISERROR ('⽆效⽤户ID。' , 16, 1)
/*分词搜索查*/
declare@a varchar(10)
declare@s varchar(1000)
declare@t table(name varchar(10))
set@a='a,b,c'
set@s=right(replace(','+@a,',',''' as name union select '''),len(replace(','+@a,',',''' as name union select '''))-15)+''''
insert into@t exec(@s)
select*from tb b where exists
(select1from@t a where charindex(a.name,b.name)>0)
if object_id('[tb]') is not null drop table[tb]
create table[tb] (id int,name varchar(25))
insert into[tb]
select1,'asdf富⼠康sfsaf'union all
select2,'富⼠康sdaf跳楼'union all
select3,'sdaf跳楼'union all
select4,'asdf富⼠康as跳楼郭台铭asf'union all
declare@name varchar(50),@sql varchar(8000)
set@Name='富⼠康跳楼郭台铭'
set@sql=' (case when charindex('''+replace(@name,'',''',name)>0 then 1 else 0 end)+(case when charindex(''')+''',name)>0 then 1 else 0 end)'
set@sql='select * from tb order by '+@sql+' desc'
/**全⽂检索（查询整个数据库中某个特定值所在的表和字段的⽅法）**/
⼀、
DECLARE@keyWord VARCHAR(200)
set@keyWord='hy'
-- 最⼤的长度.
DECLARE@MaxLength INT;
-- 数据库表名称
DECLARE@TableName VARCHAR(100);
-- 数据库表ID
DECLARE@object_id INT;
-- 查询列名的SQL
DECLARE@ColumnSql VARCHAR(500);
-- 列名.
DECLARE@clumnName VARCHAR(20);
-- 查询数据是否存在的SQL
DECLARE@Sql NVARCHAR(500);
-- 查询数据是否存在结果
DECLARE@RowCount INT;
BEGIN
-- 取得传⼊的字符串长度
-
- 对于表定义中，字段长度⼩于该长度的，将不检索.
SET@MaxLength=LEN(@keyWord);
-- 定义游标. 取得所有的数据库表名称.
DECLARE c_test_main CURSOR FAST_FORWARD FOR
SELECT object_id, name FROM sys.tables
-- 打开游标.
OPEN c_test_main;
-- 填充数据.
FETCH NEXT FROM c_test_main INTO@object_id, @TableName;
-- 假如检索到了数据，才处理.
WHILE@@fetch_status=0
BEGIN
-- 准备⽤于执⾏的查询列信息的 SQL 语句.
SET@ColumnSql='DECLARE cur_column CURSOR for SELECT col.name '
+' lumns col, pes typ '-- 从列视图与数据类型视图查询
+' WHERE '
+'  col.system_type_id = typ.system_type_id'-- 列视图与数据类型视图关联
+'  AND typ.name IN(''varchar'', ''nvarchar'', ''char'', ''nchar'') '-- 只查询指定数据类型的.
+'  AND col.max_length > '+LTRIM(STR(@MaxLength )) -- 最⼤长度要⼤于输⼊信息长度的
+'  AND col.object_id ='+LTRIM(STR(@object_id)); -- 表ID
EXEC(@ColumnSql);
OPEN cur_column;
FETCH next FROM cur_column INTO@clumnName;
WHILE@@FETCH_STATUS=0
BEGIN
SET@Sql='SELECT @RowCount = COUNT(1) FROM '+@TableName+' WHERE '+@clumnName+' LIKE ''%'+@keyWord+'%''';
EXEC SP_EXECUTESQL @Sql, N'@RowCount INT OUTPUT', @RowCount OUTPUT;
IF@RowCount>0
BEGIN
PRINT@TableName+'.'+@clumnName;
END
FETCH next FROM cur_column INTO@clumnName;
END
CLOSE cur_column;
DEALLOCATE cur_column;
-- 填充下⼀条数据.
FETCH NEXT FROM c_test_main INTO@object_id, @TableName;
END;
-- 关闭游标
CLOSE c_test_main;
-- 释放游标.
DEALLOCATE c_test_main;
END
⼆、
declare@sql nvarchar(max),@search nvarchar(100)
set@search='hy'
set@sql=''
select@sql=@sql+'select top 1 '''+sysobjects.name+''' as [table],'''+syscolumns.name+''' as [columns],'+syscolumns.name+' as [value] from '+sysobjects.name+' where '+syscolumns.name+'='''+@search+''' union all 'from sysobjects select@sql=@sql+'select null,null,null'
exec sp_executesql @sql
三、
select convert(varchar(255),'') dsca
into #y
where1=0
-- delete #y
declare@s varchar(255)
set@s='hy'
DECLARE bbb cursor for
select  TABLE_NAME,column_name  FROM lumns
where table_name in (select  table_name from information_schema.tables where TABLE_type='BASE TABLE'and table_name like'%%' )
and data_type like'%varchar%'
declare@t varchar(255)
open bbb
fetch next from bbb into@t,@f
while@@fetch_status=0
begin
exec( ' if exists (select * from '+@t+' where '+@f+'='+''''+@s+''''+'  )  insert into #y select '+''''+@t+'.'+@f+''''  )
--insert into #t select  '+''''+'select * from  '+@t+' WHERE '+@f+'=@F'+'''
fetch next from bbb into@t,@f
end
close bbb
deallocate  bbb
select*from #y
drop table #y
/**全⽂检索END*/
SET NOCOUNT ON;--存储过程，当 SET NOCOUNT 为 ON 时，不返回计数（表⽰受 Transact-SQL 语句影响的⾏数）。当 SET NOCOUNT 为 OFF 时，返回计数。declare@date datetime,@date1dateti
me,@substoreid varchar(20)
set@date='2014-09-22'
set@date1='2014-09-30'
set@substoreid='9001'
--临时表⽤法
select tag,code,name,num,money into #mid from (
select1as tag,prx_code as code,prx_name as name,sum(isnull(num,0)) as num,sum(isnull(money,0)) as money from tb1
union
select2as tag,receive_minout.pay_class as code,name as name,0as num,sum(isnull(money,0)) as money from tb2
) A
select*from #mid order by tag
drop table #mid
--SQL每组数据只取前⼏条数据的写法
select*
from (select row_number() over(partition pe order by date desc) rn,
t.*
from表名 t)
where rn <=2;
/*
type要分的类
date 排序
rn 每类要显⽰⼏条数据
*/
/*SQL执⾏中占CPU资源最多的前10条查询 */
select top20
total_worker_time/execution_count as avg_cpu_cost,plan_handle,
execution_count,
(select substring(text,statement_start_offset/2+1,
(case when statement_end_offset=-1
then len(convert(nvarchar(max),text))*2
else statement_end_offset
end- statement_start_offset)/2)
from sys.dm_exec_sql_text(sql_handle)) as query_text
from sys.dm_exec_query_stats
order by[avg_cpu_cost]desc
-- 出⼯作负荷中运⾏最频繁的查询
select top10 total_worker_time,plan_handle,execution_count,
(select substring(text,statement_start_offset /2+1,
(case when statement_end_offset =-1
then len(convert(nvarchar(max),text))*2
else statement_end_offset
end- statement_start_offset)/2)
from sys.dm_exec_sql_text(sql_handle)) as query_text
from sys.dm_exec_query_stats
order by execution_count desc
--到被编译得最多的前10位查询计划
select top10 plan_generation_num,execution_count,
(select substring(text,statement_start_offset /2+1,
(case when statement_end_offset =-1
then len(convert(nvarchar(max),text))*2
else statement_end_offset
end- statement_start_offset)/2)
from sys.dm_exec_sql_text(sql_handle)) as query_text
from sys.dm_exec_query_stats
where plan_generation_num>1
order by plan_generation_num desc
--执⾏最慢的SQL语句
SELECT
(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,total_worker_time/1000 N'所⽤的CPU总时间ms'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写⼊总次数'
,
execution_count N'执⾏次数'
,, (qs.statement_start_offset/2) +1,
sql约束条件大于0((CASE statement_end_offset
WHEN-1THEN )
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) +1) N'执⾏语句'
,creation_time N'语句编译时间'
,last_execution_time N'上次执⾏时间'
FROM
sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
, (qs.statement_start_offset/2) +1,
((CASE statement_end_offset
WHEN-1THEN )
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) +1) not like'�tch%'
ORDER BY
total_elapsed_time / execution_count DESC;
--总耗CPU最多的前个SQL:
SELECT TOP20
total_worker_time/1000AS[总消耗CPU 时间(ms)],execution_count [运⾏次数],
last_execution_time AS[最后⼀次执⾏时间],max_worker_time /1000AS[最⼤执⾏时间(ms)],
(CASE WHEN qs.statement_end_offset =-1
THEN )
ELSE qs.statement_end_offset END-qs.statement_start_offset)/2+1)
AS[使⽤CPU的语法], qt.text[完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY  total_worker_time DESC
-
-平均耗CPU最多的前个SQL:
SELECT TOP20
total_worker_time/1000AS[总消耗CPU 时间(ms)],execution_count [运⾏次数],
last_execution_time AS[最后⼀次执⾏时间],min_worker_time /1000AS[最⼩执⾏时间(ms)],
max_worker_time /1000AS[最⼤执⾏时间(ms)],
,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset =-1
THEN )
ELSE qs.statement_end_offset END-qs.statement_start_offset)/2+1)
AS[使⽤CPU的语法], qt.text[完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE  execution_count>1
ORDER BY (qs.total_worker_ution_count/1000) DESC
/*查询缺失索引*/
SELECT
DatabaseName =DB_NAME(database_id)
,[Number Indexes Missing]=count(*)
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY2DESC;
SELECT TOP10
[Total Cost]=ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage]= equality_columns
, [InequalityUsage]= inequality_columns
, [Include Cloumns]= included_columns
FROM        sys.dm_db_missing_index_groups g
INNER JOIN    sys.dm_db_missing_index_group_stats s
up_handle = g.index_group_handle
INNER JOIN    sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY[Total Cost]DESC;
/*将⼀列多⾏数据合并为⼀⾏*/
SELECT DISTINCT[Student]
,STUFF(
(
SELECT','+[Course]
FROM[dbo].[SC]
WHERE Student = A.Student
FOR XML PATH('')
)
,1,1,''
)AS Course
FROM[dbo].[SC]AS A
/*拆分字典匹配合并成⼀⾏*/
SELECT
STUFF(
(SELECT','+ T1.CodeName FROM (
SELECT Code,CodeName from T_ListCode TL JOIN dbo.f_splitstr(T_ST21.Com_FunctionCategory,',') TY ON TL.Code=TY.F1    )AS T1 FOR XML PATH('')),1,1,'') AS CodeName
from T_ST21
/*SQL递归查询*/
WITH SearcRiver AS
(
SELECT RiverID,UpRiverID FROM T_River
WHERE RiverID ='1100000000'
UNION ALL
SELECT A.RiverID,A.UpRiverID FROM T_River A, SearcRiver B
WHERE A.UpRiverID=B.RiverID
)
select*from SearcRiver
--try catch 事物-回滚
begin  try
begin transaction tr
select*from Car_Brand
update car_log set[reason]='ceshiceshiceshi'where[modelid]=557;
select1/0
print'i have executed!'
select*from AREA
print'executed success';
commit transaction tr

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。