问题描述
我能够在我的视图和存储过程中搜索特定文本,但我无法同时搜索我的表.
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