iT邦幫忙

0

[IQ] 找"SP內容/排程"有xx字眼&找table欄位xx

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

用到的SP
sp_columns 查table schema


找SP內容有xx字眼

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]


尚未有邦友留言

立即登入留言