在我的表、存储过程和 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

推荐答案

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

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%'

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