在我的表、存储过程和 SQL Server 视图中查找文本 [英] Find text in my tables, stored procedures, and views in SQL Server

问题描述

我能够在我的视图和存储过程中搜索特定文本，但我无法同时搜索我的表.

I am able to search for specific text in my views and stored procedure, but I'm not able to search through my tables at the same time.

这是我所拥有的:

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

CREATE TABLE #temp
(
    [Database_Name]     sysname,
    [Schema_Name]       sysname,
    [Object_Name]       sysname,
    [Object_Type]       nvarchar(60) 
)

-- Set the search string
SET @search_string = 'text'

SET @cmd = 'INSERT INTO #temp SELECT DISTINCT ''?'', s.name AS Schema_Name, o.name AS Object_Name, o.type_desc FROM [?].sys.sql_modules m INNER JOIN [?].sys.objects o ON m.object_id = o.object_id INNER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id WHERE m.definition Like ''%' + @search_string + '%'''

-- Uncomment the following if you have problems with your command and want to see the command
--PRINT @cmd

-- Run for every database on the server
EXEC sp_MSforeachdb @cmd

-- Retrieve your results from the temp table
SELECT *
FROM #temp
ORDER BY [Database_Name], [Object_Name], [Object_Type]

-- If you want to omit certain databases from your results, simply add 
-- the appropriate WHERE clause, as in the following:
--SELECT *
--FROM #temp
--WHERE db NOT IN ('DB1', 'DB4', 'DB7')
--ORDER BY db, obj_type, obj_name

DROP TABLE #temp

推荐答案

请尝试使用此查询进行列搜索

Please try this query for column search

SELECT 
    t.[name]    TableName
    , c.[name]  ColumnName
FROM sys.columns c
INNER JOIN sys.tables t on t.object_id = c.object_id
WHERE t.[type] = 'U' 
AND c.[name] LIKE '%Text%'

下面的脚本将获得表和其他对象的所有数据库的结果.希望这会有所帮助.

Below script will get result for all the databases for both Tables and other obejcts. Hope this will help.

DECLARE @command varchar(1000) 
DECLARE @SearchWord VARCHAR(20) = 'Text'
CREATE TABLE #Search (DatabaseName VARCHAR(255),SchemaName VARCHAR(50),ObjectName VARCHAR(255),ObjectType VARCHAR(50))

SET @command = 'USE ? INSERT INTO #Search
                    SELECT DB_NAME(), SCHEMA_NAME(t.schema_id),t.[name] TableName, ''Table'' FROM sys.columns c INNER JOIN sys.tables t on t.object_id = c.object_id WHERE t.[type] = ''U'' AND c.[name] LIKE ' + '''%' + @SearchWord + '%'''
EXEC sp_MSforeachdb @command;

SET @command = 'USE ? INSERT INTO #Search
                SELECT DISTINCT DB_Name(),s.name AS Schema_Name, o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE m.definition Like ''%' + @SearchWord + '%'''
EXEC sp_MSforeachdb @command 
SELECT * FROM #Search;
DROP TABLE #Search

这篇关于在我的表、存储过程和 SQL Server 视图中查找文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！