用到的table
sys.syscolumns 查table名稱和欄位大小
SYS.SYSPROCEDURE 查SP 名稱和內容
sysobjects 查SP名稱
syscomments 查SP內容

用到的SP
sp_columns 查table schema

找SP內容有xx字眼
way1

declare @word0 nvarchar(333)	declare @word nvarchar(333) 
  
set @word0='xxxxx' 
set @word='%'+@word0+'%'  

 SELECT DISTINCT @word,o.name
 	,出現的次數=     (LEN(c.text) - 
            LEN(REPLACE(c.text, @word0, ''))) /
            LEN(@word0)
	,位置=CHARINDEX(@word0, c.text) --	,Charindex(@word0,ROUTINE_DEFINITION,1) 
	,substring (c.text, CHARINDEX(@word0, c.text),len(c.text))     	
 , c.* 
 FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE (o.xtype = 'P'           --查SP
OR o.xtype = 'V')              --查View
And (o.name LIKE @word  --查SP或View名稱
OR c.text LIKE @word)   --查SP或View內含文字

way2

select * from xxx.SYS.SYSPROCEDURE where proc_defn like 'sp_xxxx

找table欄位xx

    Select 
       [table] = creator ,tablename =tname,colunname = cname,typename = coltype,length
       ,scale = syslength,*
    From sys.syscolumns 
    Where 1=1 
        and cname like '%xx%'  
    Order By tname; 

index list
select * FROM SYS.SYSINDEXES

查這個table的shema
exec sp_columns tablename

輸出schema of SP/view
sp_helptext 'sp_name'

sql中丟出錯誤訊息
raiserror "This SP is not use anymore,Please contact #LGBTOK"

--table流水號
rowid numeric(10,0) identity not null

排程有沒有這個字眼

declare @word nvarchar(333)
set @word='%我要查的內容%'

SELECT
[sJOB].[name] AS [JobName]
, [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]
,*
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
or [sJOB].[name] like @word
ORDER BY [JobName], [StepNo]