找出Query有xx字眼的欄位

--找db裡的某個值
drop table #tablefld
select 
	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 #tablefld
from INFORMATION_SCHEMA.COLUMNS with (nolock)
where data_type not in ('int','datetime')
--select * from #tablefld

drop table #ret
create table #ret
(
	seq int 
	,cnt int
)

declare @table varchar(30)
declare @fld varchar(30)
declare @start int =1
declare @cmd varchar(1000)
while (@start <=2644)
begin 
	select @table = TABLE_NAME,@fld= column_name from #tablefld where Seq=@start		
	set @cmd =' insert into #ret select '+cast(@start as varchar(30))+', count(*) from '+@table+' where '+@fld+' like ''%xx%''   		'	
	exec (@cmd)	
	set @start=@start+1
end
select '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 @word
ORDER BY [JobName], [StepNo]

找出有xx字眼的SP Query

declare @word nvarchar(max)='%想要查的文字%'
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES  --這是個view,每個DB都會有
where ROUTINE_TYPE='PROCEDURE'
and ROUTINE_DEFINITION like @word
ORDER BY ROUTINE_NAME ASC

匯出SP Query

在資料庫按滑鼠右鍵/工作/產生指令碼

勾選SP