SQLserver查询作业、视图、函数、存储过程中的关键字

⼀、查询视图、函数、存储过程中的关键字

SELECT a.name,a.[type],b.[definition] FROM sys.all_objects a,sys.sql_modules b

WHERE a.is_ms_shipped=0 AND a.object_id = b.object_id AND a.[type] IN ('P','V','AF') AND b.[definition] LIKE '%关键字%'ORDER BY a.[name]

⼆、作业中的关键字

SELECT B.name,* FROM msdb.dbo.sysjobsteps A LEFT JOIN msdb.dbo.sysjobs B ON A.job_id=b.job_id WHERE command LIKE '%关键字%'

三、判断表是否存在

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'DifferenceData_temp') AND type in (N'U'))DROP TABLE DifferenceData_temp