Cursor Error

[sandylou]

Hi I am trying to run through all of the databases in our server and look for a field. I have created this cursor and keep getting an error every time I try to USE the database in my cursor. Can someone help? I want to loop through each database in the server and then insert into a table every database and table where this field exists.
So far when I just try to loop through and set the database to use, I get the following error:

Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near '@vardbname'.
Server: Msg 137, Level 15, State 2, Line 11
Must declare the variable '@vardbname'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@vardbname'.

here is my code

Select Catalog_Name 
into #tmpDatabases
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME NOT IN('master','msdb','tempdb','model') 
ORDER BY CATALOG_NAME


Declare dbChangeCursor Cursor For
Select Catalog_Name from #tmpDatabases
Order by catalog_name asc


Declare @dbCount INT
	,@vardbname VARCHAR(100)
	

SET @dbCount = (SELECT Count(*) FROM #tmpDatabases)

SELECT  @dbCount = @dbCount
	,@vardbName = ''


Open dbChangeCursor

FETCH NEXT FROM dbChangeCursor 
INTO @vardbname


--cursor through dbs
WHILE @@FETCH_STATUS = 0
BEGIN

USE @vardbname
GO

--print @vardbname
/*Select * from syscolumns
select SO.name as dbObjectName,sc.name as dbfieldName
from syscolumns SC 
left join sysobjects SO on SC.id = SO.id
Where sc.name like '%empid%'*/

--update with newemplid

print @vardbname
Fetch next from dbChangeCursor into @vardbname
END
Close dbChangeCursor

Deallocate dbChangeCursor

Drop table #tmpDatabases

 

[frdba]

instead of

Declare @dbCount INT
,@vardbname VARCHAR(100)

try

Declare @dbCount INT
Declare @vardbname VARCHAR(100)

 

[sandylou]

That still gives the same error

 

[bborissov]

You can't use variable in USE command.
Also GO in between of the script is not a good idea. That terminates the batch and all local variables are gone

You could use undocumented SP:
sp_MSForEachDB

sp_MSForEachDB 'IF EXSISTS(SELECT *
                             FROM ?.INFORMATION_SCHEMA.Columns
                           WHERE Column_Name LIKE ''%empid%'')
                   BEGIN
                       .......
                   END'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[sandylou]

is there any other way? I can't seem to get the sp_MSFOREACHDB to work

declare @sql varchar (1000)
SET @sql = (select SO.name as dbObjectName,sc.name as dbfieldName
from syscolumns SC 
left join sysobjects SO on SC.id = SO.id
Where sc.name like '%emp-number%')



EXEC sp_MSForEachDb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'',''pubs'',''northwind'') 
BEGIN 

exec(@SQL)



END'

 

[bborissov]

sandyloum
ALL local variables are visible ONLY in current BATCH, so you could try:

EXEC sp_MSForEachDb 
    'IF ''?'' NOT IN (''master'', ''model'', ''msdb'',
                      ''tempdb'',''pubs'',''northwind'')
        BEGIN
             select SO.name as dbObjectName,
                    sc.name as dbfieldName
             from ?.dbo.syscolumns SC
             left join ?.dbo.sysobjects SO on SC.id = SO.id
             Where sc.name like ''%emp-number%''
        END'

not tested well

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP