SQL Server’da Tüm Veritabanında Metin Aramak

Herkese merhaba,

Bu kısımda SQL Server’da tüm veritabanında metin arama kodları olacak.

/*
Use this script to search all objects, in all databases on the server, that 
are SQL language-defined modules in SQL Server.  These include the following:

D	DEFAULT (constraint or stand-alone)
P	SQL Stored Procedure
RF	Replication-filter-procedure
V	View
TR	SQL DML Trigger
FN	SQL scalar function
IF	SQL inline table-valued function
TF	SQL table-valued function (2012 - 2016)
R	Rule (old-style, stand-alone)
*/
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 = 'categoryid'

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

GO