数据库关键字搜索视图和存储过程

[sql]代码库

DECLARE @keyWord AS NVARCHAR(128) = N'keyWord'
 
SELECT DISTINCT 'Stored Procedure' [Type],
	'[' + SCHEMA_NAME(sp.schema_id) + '].[' + sp.NAME + ']' [Name],
	sp.NAME
FROM sys.all_objects AS sp
INNER JOIN dbo.syscomments c
	ON c.id = sp.object_id
		AND CASE 
			WHEN c.number > 1
				THEN c.number
			ELSE 0
			END = 0
WHERE (c.TEXT LIKE N'%' + @keyWord + N'%')
	AND (
		(
			sp.type = N'P'
			OR sp.type = N'RF'
			OR sp.type = N'PC'
			)
		AND (
			CAST(CASE 
					WHEN sp.is_ms_shipped = 1
						THEN 1
					WHEN (
							SELECT major_id
							FROM sys.extended_properties
							WHERE major_id = sp.object_id
								AND minor_id = 0
								AND class = 1
								AND NAME = N'microsoft_database_tools_support'
							) IS NOT NULL
						THEN 1
					ELSE 0
					END AS BIT) = 0
			)
		)  
	AND sp.NAME <> @keyWord
 
UNION ALL
	 
SELECT 'View' [Type],
	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) [Name],
	TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE N'%' + @keyWord + N'%'
	AND TABLE_NAME <> @keyWord