找出Query有xx字眼的栏位

--找db里的某个值drop table #tablefldselect row_number() OVER(ORDER BY TABLE_NAME,column_name) As Seq,    TABLE_NAME as TABLE_NAME--    ,ORDINAL_POSITION as COLUMN_ID    ,column_name --    ,data_type as data_type into #tablefldfrom INFORMATION_SCHEMA.COLUMNS with (nolock)where data_type not in ('int','datetime')--select * from #tableflddrop table #retcreate table #ret(seq int ,cnt int)declare @table varchar(30)declare @fld varchar(30)declare @start int =1declare @cmd varchar(1000)while (@start <=2644)begin select @table = TABLE_NAME,@fld= column_name from #tablefld where Seq=@startset @cmd =' insert into #ret select '+cast(@start as varchar(30))+', count(*) from '+@table+' where '+@fld+' like ''%xx%''   'exec (@cmd)set @start=@start+1endselect 'select * from '+TABLE_NAME +' where '+ column_name+ ' like ''%xx%'' ' from #tablefld where Seq in (select seq from #ret where cnt>0)

找出Query有xx字眼的排程

不适用:SQL 2000

key word : sql job query

Ref
https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/

declare @word nvarchar(max)='%想要查的文字%'SELECT    [sJOB].[job_id] AS [JobID]    , [sJOB].[name] AS [JobName]    , [sJSTP].[step_uid] AS [StepID]    , [sJSTP].[step_id] AS [StepNo]    , [sJSTP].[step_name] AS [StepName]    , CASE [sJSTP].[subsystem]        WHEN 'ActiveScripting' THEN 'ActiveX Script'        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'        WHEN 'PowerShell' THEN 'PowerShell'        WHEN 'Distribution' THEN 'Replication Distributor'        WHEN 'Merge' THEN 'Replication Merge'        WHEN 'QueueReader' THEN 'Replication Queue Reader'        WHEN 'Snapshot' THEN 'Replication Snapshot'        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'        ELSE sJSTP.subsystem      END AS [StepType]    , [sPROX].[name] AS [RunAs]    , [sJSTP].[database_name] AS [Database]    , [sJSTP].[command] AS [ExecutableCommand]    , CASE [sJSTP].[on_success_action]        WHEN 1 THEN 'Quit the job reporting success'        WHEN 2 THEN 'Quit the job reporting failure'        WHEN 3 THEN 'Go to the next step'        WHEN 4 THEN 'Go to Step: '                     + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))                     + ' '                     + [sOSSTP].[step_name]      END AS [OnSuccessAction]    , [sJSTP].[retry_attempts] AS [RetryAttempts]    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]    , CASE [sJSTP].[on_fail_action]        WHEN 1 THEN 'Quit the job reporting success'        WHEN 2 THEN 'Quit the job reporting failure'        WHEN 3 THEN 'Go to the next step'        WHEN 4 THEN 'Go to Step: '                     + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))                     + ' '                     + [sOFSTP].[step_name]      END AS [OnFailureAction]FROM    [msdb].[dbo].[sysjobsteps] AS [sJSTP]    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]        ON [sJSTP].[job_id] = [sJOB].[job_id]    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]        ON [sJSTP].[job_id] = [sOSSTP].[job_id]        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]        ON [sJSTP].[job_id] = [sOFSTP].[job_id]        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]where [sJSTP].[command]  like @wordORDER BY [JobName], [StepNo]

找出有xx字眼的SP Query

declare @word nvarchar(max)='%想要查的文字%'SELECT ROUTINE_NAME, ROUTINE_DEFINITIONFROM INFORMATION_SCHEMA.ROUTINES  --这是个view,每个DB都会有where ROUTINE_TYPE='PROCEDURE'and ROUTINE_DEFINITION like @wordORDER BY ROUTINE_NAME ASC

汇出SP Query

在资料库按滑鼠右键/工作/产生指令码

勾选SP