Find given string across all DBs of a SQL instance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Find given string across all DBs of a SQL instance

Author

Topic

cornel
Starting Member

3 Posts

Posted - 2009-01-12 : 05:53:39

Find given string across all databases of an SQL Server 2005 instance.

We have an SQL 2005 instance that contains half a dozen or so databases, and also Reporting Services.
How can I find all occurrences of a given string that is used in any SQL Server 2005 database of the SQL Server instance?. (Essential, for example, when trying to identify where a given field name is used so that we can modify all affected views, stored procedures, etc.)

I have used the stored procedure below to try to find all occurrences of a string, but this does not work as expected. (my stored procedure has an outer loop which finds all databases for the database instance. For each database it then runs a sseries of union queries to return the objects that contain the string parameter specified)

But my stored procedure suffers from the following 2 problems which I am unable to solve:
(1) It only scans the database which contains the stored procedure, even though I use a 'USE database-name' statement in the outer-most loop (and does so repeatedly fro each iteration of the ouer loop)
(2) it finds no hits in our Reporting Services database

Any help would be greatly appreciated.

it may be easiest to paste the procedure into Visual Studio.

USE [Harvest_Reports_Backend]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[CM_sp_FindString_in_any_User_DB]
@Findstring varchar(400)
AS
/* -------------------------------------------------------------------------
DESCRIPTION:
Stored procedure to search all user databases for a specified string.

TODO: find workaround for " USE @DBName;" which generates the following error

-- Msg 154, Level 15, State 1, Procedure CM_sp_FindString_in_any_User_DB, Line 68
-- a USE database statement is not allowed in a procedure, function or trigger.

*/

BEGIN
DECLARE @ErrMsg nvarchar(500) -- used to hold/print error messages
, @cmd nvarchar(4000) -- used with EXEC|sp_executesql
, @GenerateError bit -- used during exit processing to raise error. CM added 28.11.08
, @DBName sysname
, @stringToFind varchar(410)

-- log the start of sp.
PRINT convert(nvarchar(20), getdate(),113) + N' ' + N'CM_sp_FindString_in_any_User_DB' + ' starting.'
-- escape any embedded wild characters.
set @stringToFind = Replace(@FindString,'_','[_]')
set @stringToFind = '%' + Replace(@stringToFind,'%','[%]') + '%'
SET NOCOUNT ON;

-- determine which databases to search
CREATE TABLE #tempdbtbl (id int IDENTITY(1,1), dbname sysname); -- list of user databases to be searched
INSERT INTO #tempdbtbl
SELECT name
FROM master.sys.databases
WHERE name NOT IN (N'master', N'model', N'msdb', N'tempdb')
AND name NOT like 'AdventureWorks%'

-- Show list of databases to be backed up in 'results' window
SELECT DBName
FROM #tempdbtbl;

DECLARE @dbcount int
SET @dbcount = 0 -- initialise

DECLARE DBNameCursor CURSOR FAST_FORWARD FOR
SELECT DBName FROM #tempdbtbl;

OPEN DBNameCursor;
FETCH NEXT FROM DBNameCursor INTO @DBName;
-- 28.11.08 Print whole list of databases selected in messages window or text log file.
WHILE @@FETCH_STATUS = 0
BEGIN
set @dbcount = @dbcount + 1

If @dbcount = 1
begin
Print N'Searching for objects that contain ''' + @stringToFind + '''.';
PRINT N'The following databases will be searched:';
end;

PRINT convert(varchar(3), @dbcount) + N' ' + @dbname;

FETCH NEXT FROM DBNameCursor INTO @DBName;
END;
CLOSE DBNameCursor;

-- reopen the cursor to search each DbName in turn.
OPEN DBNameCursor;
FETCH NEXT FROM DBNameCursor INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Searching in ' + @DBName + ' ...'

USE @DBName;
generates error:
-- The above 2 lines generate following error:
--Msg 154, Level 15, State 1, Procedure CM_sp_FindString_in_any_User_DB, Line 68
--a USE database statement is not allowed in a procedure, function or trigger.

SET @cmd = N'USE ' + @DBname + N';';
EXEC sp_executesql @cmd;

-- Search for the given string in all Procedures, views and Paramenters

(SELECT
ROUTINE_NAME
, SPECIFIC_Catalog as DatabaseName --- Database name that contains the object.
, ROUTINE_TYPE As [Type]
, DATA_Type
, Character_maximum_length
, LAST_ALTERED
, CREATED
, SQL_DATA_ACCESS --reads or modifies
, COLLATION_Name
, Character_set_name
, ROUTINE_BODY
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_DEFINITION LIKE @stringToFind -- '%foobar%'
)
UNION ALL (
Select
SPECIFIC_NAME --parameter name
, SPECIFIC_Catalog --- Database name that contains the object.
, 'Parms'
, DATA_TYPE
, Character_maximum_length
, NULL --LAST_ALTERED
, NULL --Created
, Parameter_mode --IN or OUT
, COLLATION_Name
, Character_set_name
, NULL --ROUTINE_BODY
from Information_Schema.parameters --where parameter_name like @stringToFind
WHERE
PARAMETER_NAME LIKE @stringToFind
)
UNION ALL (
Select
TABLE_NAME
, TABLE_Catalog as DatabaseName --- Database name that contains the object.
, 'View'
, NULL -- ROUTINE_TYPE As [Type]
, NULL --Character_maximum_length,
, NULL --LAST_ALTERED
, NULL --Created
, Case is_Updatable
WHEN 'NO' then 'Not Updateable'
WHEN 'YES' then 'Is Updateable'
ELSE is_Updatable end
as SQL_DATA_ACCESS --reads or modifies
, NULL -- COLLATION_Name
, NULL --Character_set_name
, NULL --ROUTINE_BODY

from Information_Schema.views --where view_definition like @stringToFind
WHERE
VIEW_DEFINITION LIKE @stringToFind
)
order by
ROUTINE_NAME, DatabaseName, LAST_ALTERED desc

Get_Next:
FETCH NEXT FROM DBNameCursor INTO @DBName;
END;

CLOSE DBNameCursor;
DEALLOCATE DBNameCursor;

Exit_here:
-- clean up & exit
DROP TABLE #tempdbtbl;
SET NOCOUNT OFF;
if @GenerateError = 1
Begin
RAISERROR (@ErrMsg, 16, 1) WITH LOG
end;
else
PRINT convert(nvarchar(20), getdate(),113) + N' ' + N'CM_sp_FindString_in_any_User_DB' + N' ended.'
RETURN;

-- clean up, print error & exit
The_End:
SET @GenerateError = 1 -- 28.11.08 Request RaiseError during exit.
PRINT @ErrMsg;
goto Exit_here; --28.11.08
END

Best Regards,
Cornel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 08:56:34

use undocumented stored procedure sp_Msforeachdb to perform check on all the dbs of an instance. and use query below to get objects where string is used

select * from sys.sql_modules where definition like '%'+yourvalue+'%'

if its a column you want to search in table use

select * from INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%'+yourvalue+'%'

...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 08:57:43

http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-13 : 10:37:21

Stored procedure to search all tables in a particular database:
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-13 : 15:48:18

Check out some of the scripts in this link: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100884

- Lumbago

ssunny
Posting Yak Master

133 Posts

Posted - 2009-01-13 : 16:09:06

I use this script to find a given string in store procs and triggers.

select distinct name from sysobjects,syscomments
where sysobjects.id = syscomments.id
and sysobjects.type in('P','TR')
and sysobjects.name like '%'
and charindex(lower('your string'),text) > 0
order by name
go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources