Search all databases for a string

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 2000 Forums

SQL Server Development (2000)

Search all databases for a string

Author

Topic

jco510811
Starting Member

9 Posts

Posted - 2006-08-02 : 05:18:55

Hi

Was wondering if anyone can help i am trying to loop round all databases to find a sting within SP's etc... i have this so far as an idea of what i am trying to do.

DECLARE @DatabaseName varchar(255)

DECLARE DatabaseCursor CURSOR SCROLL FOR
SELECT
[name]
FROM
sysdatabases

OPEN DatabaseCursor

--DISABLE ALL CONSTRAINTS
FETCH FIRST FROM DatabaseCursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT
o.[Name]
FROM
@DatabaseName.dbo.SYSObjects o
left outer JOIN @DatabaseName.dbo.syscomments s ON s.id = o.id
WHERE
s.CText LIKE '%,101%'
AND xType = 'U'

FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

However this is not possible to do @DatabaseName.dbo.

Any ideas?

Thanks in advance for your help

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-02 : 05:37:20

Somthing like this ..


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FindProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[FindProc]
GO
CREATE Proc FindProc
(
 @pSearchText VarChar(800) -- Variable used for finding the table name
)
As

Declare @QryString nVarchar(4000) -- Variable will be used for the dynamic query
Declare @DbName Varchar(800) -- Variable for storing the database Name

create table #TblDataBase
(DbName varchar(1000),
ProcName Varchar(80) )

select @DbName = ''
while @DbName < (select max(name) from master..sysdatabases)
begin
select @DbName = min(name) from master..sysdatabases where [Name] > @DbName
     select @QryString = 'Select '''+ @DbName + ''' As DataBaseName, [Name] From [' + @DbName+']..Sysobjects sy Inner Join [' + 
     @DbName + ']..SysComments Com On sy.[ID] = Com.[ID] Where Com.[Text] Like ''%' + @pSearchText + '%'''
     Insert #TblDataBase exec (@QryString)
End
Select Dbname As 'DataBase Name ', [ProcName] As 'Procedure Name' From #TblDataBase

Drop Table #TblDateBase

GO

FindProc 'Create'

Chirag

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-02 : 05:37:45

select @sql =
'SELECT
o.[Name]
FROM
' + @DatabaseName + '.dbo.SYSObjects o
left outer JOIN ' + @DatabaseName + '.dbo.syscomments s ON s.id = o.id
WHERE
s.CText LIKE '%,101%'
AND xType = 'U'
'
exec (@sql)
And double up the quotes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-02 : 10:53:48

or script the procedures and do find

Madhivanan

Failing to plan is Planning to fail

jco510811
Starting Member

9 Posts

Posted - 2006-08-03 : 06:12:30

Brilliant thank you for all your help

My final code waas as follows:

if exists(select * from TempDB..sysobjects where id = object_id('TempDB..#databases'))
DROP TABLE #databases

DECLARE @DatabaseName varchar(250)
DECLARE @GetSps varchar(250)

create table #databases
(
SpName varchar(700)
, DatabaseName varchar(250)
)

DECLARE DatabaseCursor CURSOR FOR
SELECT
[name]
FROM
sysdatabases
WHERE
[name] NOT LIKE '% %'

OPEN DatabaseCursor

--DISABLE ALL CONSTRAINTS
FETCH FROM DatabaseCursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @GetSps = 'INSERT INTO #databases SELECT
o.[name],'+''''+@DatabaseName+''''+' as DatabaseName
FROM
'+@DatabaseName+'.dbo.SYSObjects o
inner join '+@DatabaseName+'.dbo.syscomments s ON s.id = o.id
WHERE
s.[Text] LIKE ''%,101%'''
execute (@GetSps)

FETCH FROM DatabaseCursor INTO @DatabaseName
END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

SELECT
*
FROM
#databases

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources