用到的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 cINNER JOIN sysobjects o ON c.id=o.idWHERE (o.xtype = 'P'           --查SPOR o.xtype = 'V')              --查ViewAnd (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]