Find paricular word or text in all database objects on the server

1Lam

Rate me:

5.00/5 (2 votes)

16 Feb 2013CPOL

12K

Create the following table on a local database:CREATE TABLE [dbo].[TB_TEMP_TABLESTORE]( [SEARCH_STRING] [nvarchar](128) NULL, [DB_NAME] [nvarchar](128) NULL, [OBJECT_NAME] [nvarchar](128) NULL, [OBJECT_TYPE] [nvarchar](50) NULL) ON [PRIMARY]Rename Yourdatabase to the same...

Create the following table on a local database:

CREATE TABLE [dbo].[TB_TEMP_TABLESTORE](
	[SEARCH_STRING] [nvarchar](128) NULL,
	[DB_NAME] [nvarchar](128) NULL,
	[OBJECT_NAME] [nvarchar](128) NULL,
	[OBJECT_TYPE] [nvarchar](50) NULL
) ON [PRIMARY]

Rename Yourdatabase to the same database where you stored table.
This will give you all instances throughout the entire Server.

Good for when you make code changes across a project that affects other projects or updates to a new server:

CREATE PROCEDURE [dbo].[spr_View_SearchAllObject] 
	-- Add the parameters for the stored procedure here
	@StringToSearch NVARCHAR(128)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;
--DECLARE @StringToSearch varchar(100)
--SET @StringToSearch = 'TB_STATES'
SET @StringToSearch = '%' + @StringToSearch + '%'
DECLARE @SQL NVARCHAR(MAX)
TRUNCATE TABLE YourDatabase.DBO.TB_TEMP_TABLESTORE
 
DECLARE DBNAME_cursor CURSOR FOR
	select [name] FROM [master].[sys].[databases]  (NOLOCK)
	where [name] not like '%DNU%'
	order by 1
	OPEN DBNAME_cursor
	DECLARE @DBNAME_NAME varchar(100)
	FETCH NEXT FROM DBNAME_cursor INTO @DBNAME_NAME
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
			-- set up cursor and run for each database name
			SET @SQL=N'USE  [' + @DBNAME_NAME + ']
			INSERT INTO [YourDatabase].[dbo].[TB_TEMP_TABLESTORE]
           ([SEARCH_STRING]
		   ,[DB_NAME]
           ,[OBJECT_NAME]
           ,[OBJECT_TYPE])
			SELECT Distinct ''' + @StringToSearch +''' AS [SEARCH_STRING],DB_NAME() as [DB_NAME], SO.Name AS [OBJECT_NAME], SO.[Type] AS [OBJECT_TYPE]
			FROM sysobjects SO (NOLOCK)
			WHERE [name] not like ''%DNU%''
			AND 
				(
				SO.Name  LIKE  ''' + @StringToSearch + '''
				OR EXISTS (SELECT * FROM syscomments SC (NOLOCK) 
					WHERE SO.Id = SC.ID
					AND SC.Text LIKE  ''' + @StringToSearch + ''')
				)
			ORDER BY SO.Name'
	--PRINT @SQL
	EXEC sp_executesql @SQL
	FETCH NEXT FROM DBNAME_cursor INTO @DBNAME_NAME
	
	END
	CLOSE DBNAME_cursor
	DEALLOCATE DBNAME_cursor
	SELECT * FROM [YourDatabase].[dbo].[TB_TEMP_TABLESTORE]
END

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

1Lam

Web Developer

United States

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

Hey,

This is awesome, I've been looking for something like that, thank you

Well my questions are:

What is DNU, and why it shouldn't be in the results ?

And also what is (NOLOCK) ?

I've made some changes to your SP, so it'll work for only one db and doesn't create a base table

USE [YOUR_DATABASE]
GO
/****** Object:  StoredProcedure [dbo].[spr_View_SearchAllObject]    Script Date: 08/10/2013 00:13:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spr_View_SearchAllObject]
	@StringToSearch NVARCHAR(128)
AS
BEGIN

SET NOCOUNT ON

SET @StringToSearch = '%' + @StringToSearch + '%'

IF OBJECT_ID (N'#TB_TEMP_TABLESTORE', N'U') IS NULL 
BEGIN	   
	CREATE TABLE #TB_TEMP_TABLESTORE
	(
		[SEARCH_STRING] [nvarchar](128) NULL,
		[DB_NAME] [nvarchar](128) NULL,
		[OBJECT_NAME] [nvarchar](128) NULL,
		[OBJECT_TYPE] [nvarchar](50) NULL
	) ON [PRIMARY]
END
ELSE
BEGIN
	DELETE FROM #TB_TEMP_TABLESTORE
END
 	
INSERT INTO #TB_TEMP_TABLESTORE ([SEARCH_STRING] ,[DB_NAME] ,[OBJECT_NAME] ,[OBJECT_TYPE])
SELECT Distinct @StringToSearch AS [SEARCH_STRING], DB_NAME() as [DB_NAME], SO.Name AS [OBJECT_NAME], SO.[Type] AS [OBJECT_TYPE]
FROM sysobjects SO (NOLOCK)
WHERE [name] not like '%DNU%'
AND 
(
	SO.Name LIKE @StringToSearch
OR 
	EXISTS (
	SELECT	* 
	FROM	syscomments SC (NOLOCK) 
	WHERE 	SO.Id = SC.ID
	AND 	SC.Text LIKE @StringToSearch
	)
)
ORDER BY SO.Name

SELECT * FROM #TB_TEMP_TABLESTORE

DELETE FROM #TB_TEMP_TABLESTORE
	
END

UPDATE:

We don't need the table if we are working on 1 DB

USE [YOUR_DATABASE]
GO
/****** Object:  StoredProcedure [dbo].[spr_View_SearchAllObject]    Script Date: 08/10/2013 00:13:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spr_View_SearchAllObject] 
	@StringToSearch NVARCHAR(128)
AS
BEGIN

SET NOCOUNT ON
 	
SELECT Distinct @StringToSearch AS [SEARCH_STRING], DB_NAME() as [DB_NAME], SO.Name AS [OBJECT_NAME], SO.[Type] AS [OBJECT_TYPE]
FROM sysobjects SO (NOLOCK)
WHERE [name] not like '%DNU%'
AND 
(
	SO.Name LIKE @StringToSearch
OR 
	EXISTS (
	SELECT	* 
	FROM	syscomments SC (NOLOCK) 
	WHERE 	SO.Id = SC.ID
	AND 	SC.Text LIKE ('%' + @StringToSearch+ '%')
	)
)
ORDER BY SO.Name

END

UPDATE 2:

Find using list of SearchWords

USE [YOUR_DATABASE]
Go

DECLARE @Strings TABLE (String NVARCHAR(250))

--DECLARE @Separator NVARCHAR(1) = ' ', @STRs NVARCHAR(500) = 'Bills PaymentDate=' ------ The Idea is to replace every space with UNION ALL SELECT to seperate between all parts, then select all values respectively in one one big select statment
--SET @STRs = ' SELECT ''' + REPLACE(@STRs, @separator, ''' UNION ALL SELECT ''') + ''' ' -- Use UNION for DISTINCT values
--INSERT INTO @Strings EXEC(@STRs) -- INSERT INTO @Strings Table SELECT * Values UNION(ed) Together

INSERT INTO @Strings SELECT 'Bills' UNION SELECT 'PaymentDate = ' --UNION SELECT 'SET'
DECLARE @MinCount INT = NULL --NULL = Count
IF(@MinCount IS NULL) BEGIN SET @MinCount = (SELECT COUNT(String) FROM @Strings) END
UPDATE S SET S.String = '%' + S.String + '%' FROM @Strings S

SELECT Distinct DB_NAME() AS [DB_NAME], SO.Name AS [OBJECT_NAME], SO.[Type] AS [OBJECT_TYPE]
FROM SYSOBJECTS SO (NOLOCK)
WHERE [NAME] NOT LIKE '%DNU%'
AND (SELECT COUNT(String) FROM @Strings WHERE SO.name LIKE String) >= @MinCount -- >= @Count-1 -- = @Count -- > 0
OR EXISTS (SELECT *
FROM SYSCOMMENTS SC (NOLOCK)
WHERE SO.ID = SC.ID AND (SELECT COUNT(String) FROM @Strings WHERE SC.Text LIKE String) >= @MinCount) -- >= @Count-1 -- = @Count -- > 0 --AND SO.Type = 'P'
ORDER BY SO.Name
GO

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.