SQL Server中同时搜索表、存储过程和视图文本的问题

我来帮你调整现有代码，让它能同时在用户表、视图和存储过程里搜索指定文本。原来的代码只针对视图和存储过程（它们的定义存储在sys.sql_modules中），而表的相关文本需要从系统视图如sys.columns、sys.default_constraints等获取，下面是完整的改进版代码：

DECLARE @cmd VARCHAR(MAX), @search_string VARCHAR(200)

CREATE TABLE #temp (
    [Database_Name] sysname,
    [Schema_Name] sysname,
    [Object_Name] sysname,
    [Object_Type] nvarchar(60),
    [Match_Location] nvarchar(100), -- 标记匹配位置：列名/约束/对象定义
    [Matched_Text] nvarchar(MAX) -- 显示匹配的具体内容
)

-- 设置要搜索的目标文本
SET @search_string = 'your_search_text_here'

-- 构建遍历所有数据库的搜索命令
SET @cmd = 'USE [?]; 
INSERT INTO #temp
-- 1. 搜索视图和存储过程的定义文本
SELECT 
    DB_NAME() AS Database_Name,
    s.name AS Schema_Name,
    o.name AS Object_Name,
    o.type_desc AS Object_Type,
    ''Object Definition'' AS Match_Location,
    m.definition AS Matched_Text
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE o.type IN (''V'', ''P'') -- V=视图，P=存储过程
AND m.definition LIKE ''%' + REPLACE(@search_string, '''', '''''') + '%''

UNION ALL

-- 2. 搜索用户表的列名
SELECT 
    DB_NAME() AS Database_Name,
    s.name AS Schema_Name,
    t.name AS Object_Name,
    t.type_desc AS Object_Type,
    ''Column Name'' AS Match_Location,
    c.name AS Matched_Text
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name LIKE ''%' + REPLACE(@search_string, '''', '''''') + '%''

UNION ALL

-- 3. 搜索表的默认约束定义
SELECT 
    DB_NAME() AS Database_Name,
    s.name AS Schema_Name,
    t.name AS Object_Name,
    t.type_desc AS Object_Type,
    ''Default Constraint'' AS Match_Location,
    dc.definition AS Matched_Text
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
WHERE dc.definition LIKE ''%' + REPLACE(@search_string, '''', '''''') + '%''

UNION ALL

-- 4. 搜索表的检查约束定义
SELECT 
    DB_NAME() AS Database_Name,
    s.name AS Schema_Name,
    t.name AS Object_Name,
    t.type_desc AS Object_Type,
    ''Check Constraint'' AS Match_Location,
    cc.definition AS Matched_Text
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.check_constraints cc ON t.object_id = cc.parent_object_id
WHERE cc.definition LIKE ''%' + REPLACE(@search_string, '''', '''''') + '%'';'

-- 执行遍历搜索
EXEC sp_msforeachdb @cmd

-- 查看所有匹配结果
SELECT * FROM #temp
ORDER BY Database_Name, Schema_Name, Object_Type, Object_Name

-- 清理临时表
DROP TABLE #temp

关键改进说明： #

• 新增Match_Location和Matched_Text列，让你直观看到文本匹配的具体位置和内容
• 覆盖了四类搜索场景：视图/存储过程定义、表列名、默认约束、检查约束
• 用REPLACE(@search_string, '''', '''''')处理搜索文本中的单引号，避免SQL语法报错
• 通过UNION ALL合并所有结果，统一输出到临时表方便查看

使用小贴士： #

1. 把your_search_text_here替换成你要找的实际文本
2. 如果不需要某些搜索分支（比如不需要检查约束），直接删掉对应的UNION ALL块即可
3. 若只想搜索特定数据库，把sp_msforeachdb换成直接指定数据库名的语句就行

内容的提问来源于stack exchange，提问作者Cheon