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:
here is my code
So far when I just try to loop through and set the database to use, I get the following error:
Code:
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
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