SqlServer常用定位脚本

-- 查询存储过程包含的text

select distinct object_name(id) from syscomments where id in(select object_id from sys.objects where type ='P') and text like'%银库%'

-- 执行时间过长脚本查询

SELECT r.session_id ,     DB_NAME(qt.[dbid]) AS [DatabaseName] ,     r.start_time, r.[status], r.blocking_session_id,        SUBSTRING(qt.[text], r.statement_start_offset / 2,        ( CASE WHEN r.statement_end_offset = -1               THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2               ELSE r.statement_end_offset          END - r.statement_start_offset ) / 2) AS [statement] ,          qt.[text],         r.wait_type,        r.wait_time,         r.wait_resource,        r.cpu_time ,        r.total_elapsed_time / 60000 AS [elapsed_minutes],        r.reads ,        r.writes ,        r.logical_readsFROM    sys.dm_exec_requests AS r        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qtWHERE   r.session_id > 50ORDER BY r.start_time

--根据关键字查找存储过程

set nocount onIF OBJECT_ID('tempdb..#db') IS NOT NULL DROP TABLE #dbIF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #tempdeclare @row int,@sql nvarchar(max),@dbname nvarchar(100)create table #db(id int identity(1,1),dbname nvarchar(100))create table #temp(dbname nvarchar(100),ProName nvarchar(100))declare @proComment nvarchar(100),@queryType int,@queryDBname nvarchar(100)set @queryDBname=''--选填，查询制定数据库，不填表示查询所有数据库set @queryType=2--必填，1按存储过程名称模糊查询，2表示按内容模糊查询set @proComment='本人找回登录密码'--必填，查询的关键字insert #db(dbname) select name from sys.databases where name not in('master','tempdb','model','msdb','ReportServer','ReportServerTempDB') and name not like '%_old' and name not like '%_bak'and (ISNULL(@queryDBname,'')='' or name=@queryDBname)set @row=@@ROWCOUNTwhile (@row>0)beginselect @dbname=dbname from #db where id=@rowset @sql='use '+@dbname+'insert into #temp select distinct '''+@dbname+''', object_name(id) as ProNamefrom dbo.syscommentswhere id in (select object_id from sys.objects where type in(''P'',''pc''))'if @queryType = 1set @sql=@sql+' and object_name(id) like ''%'+@proComment+'%'''else set @sql=@sql+' and text like ''%'+@proComment+'%''' exec(@sql)set @row=@row-1endset @sql='DECLARE @SQL NVARCHAR(MAX)'select @sql=@sql+'USE ['+dbname+']PRINT(''USE ['+dbname+']GOIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''[dbo].['+ProName+']'''') AND type in (N''''P'''', N''''PC''''))DROP PROCEDURE [dbo].['+ProName+']GO'')SET @SQL = OBJECT_DEFINITION(object_id('''+ProName+'''))while len(@SQL) > 4000beginprint left(@SQL, charindex(char(10), @SQL, 2000))set @SQL = right(@SQL, len(@SQL) - charindex(char(10), @SQL, 2000))endif len(@SQL) > 0 print @SQLprint ''GO'''from #TEMP--print(@sql)exec(@sql)IF OBJECT_ID('tempdb..#db') IS NOT NULL DROP TABLE #dbIF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #tempGO