sql server 查找包含字符串的对象-优快云博客

本文提供SQL Server中查询包含特定字符串的各类对象的方法，包括表名、列名及存储过程等，通过不同SQL语句实现全面搜索。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql server 查找包含字符串的对象

SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
where sm.definition like '%要匹配的内容%' --collate SQL_Latin1_General_CP1_CI_AS
ORDER BY o.type;
GO


select OBJECT_NAME(id) AS object_name,text
from syscomments
where text like '%要匹配的内容%'


-- Declare the text we want to search for
DECLARE @Text nvarchar(4000);
SET @Text = '要匹配的内容';
-- Get the schema name, table name, and table type for:
-- Table names
SELECT
       TABLE_SCHEMA  AS 'Object Schema'
      ,TABLE_NAME    AS 'Object Name'
      ,TABLE_TYPE    AS 'Object Type'
      ,'Table Name'  AS 'TEXT Location'
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%'+@Text+'%'
UNION
 --Column names
SELECT
      TABLE_SCHEMA   AS 'Object Schema'
      ,COLUMN_NAME   AS 'Object Name'
      ,'COLUMN'      AS 'Object Type'
      ,'Column Name' AS 'TEXT Location'
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%'+@Text+'%'
UNION
-- Function or procedure bodies
SELECT
      SPECIFIC_SCHEMA     AS 'Object Schema'
      ,ROUTINE_NAME       AS 'Object Name'
      ,ROUTINE_TYPE       AS 'Object Type'
      ,ROUTINE_DEFINITION AS 'TEXT Location'
FROM  INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%'+@Text+'%'
      AND (ROUTINE_TYPE = 'function' OR ROUTINE_TYPE = 'procedure');