SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Find given string across all DBs of a SQL instance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cornel
Starting Member

United Kingdom
3 Posts

Posted - 01/12/2009 :  05:53:39  Show Profile  Reply with Quote
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

Edited by - cornel on 01/13/2009 07:13:06

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/13/2009 :  08:56:34  Show Profile  Reply with Quote
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+'%'

...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/13/2009 :  08:57:43  Show Profile  Reply with Quote
http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm
Go to Top of Page

darkdusky
Aged Yak Warrior

591 Posts

Posted - 01/13/2009 :  10:37:21  Show Profile  Reply with Quote
Stored procedure to search all tables in a particular database:
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 01/13/2009 :  15:48:18  Show Profile  Reply with Quote
Check out some of the scripts in this link: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100884



- Lumbago
Go to Top of Page

ssunny
Posting Yak Master

USA
133 Posts

Posted - 01/13/2009 :  16:09:06  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000