Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server Loops

Posted on 2007-10-02
6
Medium Priority
?
714 Views
Last Modified: 2008-01-09
I want to loop all my databases and search all stored procedures for references of tables in a particular database (ernst).  The problem with the following code is that I never get past the first database (master) in dbcursor.  Is my WHILE screwed up somewhere?

-- *****************************************************
ALTER    procedure spSearchAllSPFNForTableNames as
----------------------------------------------
-- Find a character string everywhere in server.
-- spSearchAllSQFNForTableNames
----------------------------------------------
DECLARE @dbname varchar(40)
DECLARE @search varchar(40)

declare @ss varchar(500)

DECLARE dbcursor CURSOR READ_ONLY
FOR select name from master..sysdatabases

-- *****************************************************************
DECLARE dbcursor2 CURSOR READ_ONLY
FOR SELECT DISTINCT
      syso.name AS sysobject_name
FROM
      ernst..sysobjects syso LEFT OUTER JOIN
        ernst..syscolumns sysc ON syso.id = sysc.id
WHERE
      syso.xtype = 'U'
ORDER BY syso.name
-- *****************************************************************

exec spDropTable 'ObjectSearch'
create table ObjectSearch (dbname varchar(32), obname varchar(100), obtype varchar(3) )

OPEN dbcursor
OPEN dbcursor2

FETCH NEXT FROM dbcursor INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
            FETCH NEXT FROM dbcursor2 INTO @search
            WHILE (@@fetch_status <> -1)
            BEGIN
                  IF (@@fetch_status <> -2)
                  BEGIN
                        set @ss = 'Insert into ObjectSearch (dbname,obname,obtype) '
                        set @ss = @ss + 'Select ''' + @dbname + ''' dbname, name ObjectName, type ObjectType  '
                        set @ss = @ss + ' from ' + @dbname + '..sysobjects so, ' + @dbname + '..syscomments sc '
                        set @ss = @ss + ' where so.id=sc.id and sc.text like ''%' + @search + '%'''
            
                        print @ss
                        exec (@ss)
                  END
                  FETCH NEXT FROM dbcursor2 INTO @search
            END

      END
      FETCH NEXT FROM dbcursor INTO @dbname

END

CLOSE dbcursor
CLOSE dbcursor2
DEALLOCATE dbcursor
DEALLOCATE dbcursor2

select * from ObjectSearch
order by dbname, obtype, obname

return  -- spSearchAllSQFNForTableNames
-- *****************************************************
0
Comment
Question by:famoso
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:dportas
ID: 20001398
You need to OPEN and CLOSE dbcursor2 within the outer loop.

Might it be easier to search your procedure code in source control? A search for "ernst." in VisualStudio or VSS for example.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20001406
You are looping through all the database.  The problem is that you read to the end of dbcursor2 while focused on the master database.  When you advance to the second database, dbcursor2 is still at the end.  You need to open/close dbcursor2 inside the first cursor loop.  
0
 

Author Comment

by:famoso
ID: 20001477
dportas
How do you mean?  Would I make a DB Project in VS?  How would this allow me to search procedure code?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:famoso
ID: 20001566
I'm getting an error: ...

Server: Msg 16916, Level 16, State 1, Procedure spSearchAllSPFNForTableNames, Line 39
A cursor with the name 'dbcursor2' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure spSearchAllSPFNForTableNames, Line 40
A cursor with the name 'dbcursor2' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure spSearchAllSPFNForTableNames, Line 55
A cursor with the name 'dbcursor2' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure spSearchAllSPFNForTableNames, Line 56
A cursor with the name 'dbcursor2' does not exist.


... with the following code corrections.  Are my cursors in the correct spot?

ALTER    procedure spSearchAllSPFNForTableNames as
----------------------------------------------
-- Find a character string everywhere in server.
-- spSearchAllSPFNForTableNames
----------------------------------------------
DECLARE @dbname varchar(40)
DECLARE @search varchar(40)

declare @ss varchar(500)

DECLARE dbcursor CURSOR READ_ONLY
FOR select name from master..sysdatabases where [name] NOT IN ('master','tempdb','model','msdb','CLAMS','Calculator','Manager','Famoso','Warehouse','Ernst_Tweak')

-- *****************************************************************
DECLARE dbcursor2 CURSOR READ_ONLY
FOR SELECT DISTINCT
      syso.name AS sysobject_name
FROM
      ernst..sysobjects syso LEFT OUTER JOIN
        ernst..syscolumns sysc ON syso.id = sysc.id
WHERE
      syso.xtype = 'U'
ORDER BY syso.name
-- *****************************************************************

exec spDropTable 'ObjectSearch'
create table ObjectSearch (dbname varchar(32), obname varchar(100), obtype varchar(3) )

OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
            OPEN dbcursor2
            FETCH NEXT FROM dbcursor2 INTO @search
            WHILE (@@fetch_status <> -1)
            BEGIN
                  IF (@@fetch_status <> -2)
                  BEGIN
                        set @ss = 'Insert into ObjectSearch (dbname,obname,obtype) '
                        set @ss = @ss + 'Select ''' + @dbname + ''' dbname, name ObjectName, type ObjectType  '
                        set @ss = @ss + ' from ' + @dbname + '..sysobjects so, ' + @dbname + '..syscomments sc '
                        set @ss = @ss + ' where so.id=sc.id and sc.text like ''%' + @search + '%'''
            
                        print @ss
                        exec (@ss)
                  END
                  FETCH NEXT FROM dbcursor2 INTO @search
            END
            CLOSE dbcursor2
            DEALLOCATE dbcursor2

      END
      FETCH NEXT FROM dbcursor INTO @dbname

END

CLOSE dbcursor
DEALLOCATE dbcursor

select * from ObjectSearch
order by dbname, obtype, obname

return  -- spSearchAllSPFNForTableNames

0
 
LVL 22

Accepted Solution

by:
dportas earned 2000 total points
ID: 20002128
The problem in your revised code is that you DEALLOCATE dbcursor2 within the loop.
You should CLOSE within the loop but don't DEALLOCATE within the loop.

I was referring to the search feature in Studio or in SourceSafe where you can search the entire project or solution (select Edit / Find). If you don't already have your database code under source control then you should certainly consider it.


0
 

Author Comment

by:famoso
ID: 20006588
You are right in suggesting that I have my sp's in VSS.  I am working on a new project which will be all data driven and this is why I need this help.  The next project will be in VSS.  I will try moving the DEALLOCATE.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question