Problem in using USE dbname

Sorry was not clear earlier I want to do something like this

DECLARE AllDatabases CURSOR FOR

-- Get the names of all databases
SELECT name FROM master..sysdatabases

-- Open the cursor that holds the names of all databases on the server
OPEN AllDatabases

-- Declare variable for holding the name of the current database being processed
DECLARE @DB NVARCHAR(128)
DECLARE @EXECUTE NVARCHAR(128)

-- Get the name of the first database
FETCH NEXT FROM AllDatabases INTO @DB

-- Process all databases until there are no more
WHILE (@@FETCH_STATUS = 0)

BEGIN
set @execute='[USE '+@DB+']' ;
Execute @execute

FETCH NEXT FROM AllDatabases INTO @DB
END --(@@FETCH_STATUS = 0)

--
--End "Generate DBCC SHOWFILESTATS Commands" Section
--

-- close and deallocate cursor to hold name of all databases on server
CLOSE AllDatabases
DEALLOCATE AllDatabases

Adminanup at 2007-9-3 >

I tried thsi it is printing but still not all the database are being looped it is just using the current active one so I need to do the USE @db to loop through all the databases and collect the dbcc sqlperf(logpsace) and filestats into a table.

Any ideas where I am missing

Adminanup at 2007-9-3 >

The problem here is that the USE @DB statement is only valid for the EXECUTE statement. As soon as the EXECUTE statement completes, the current database is back to the original.

In order to accomplish what you want, there are 2 ways to do this:

a) Create a @strSQL variable string in the cursor that will be a concatenation of the statements that will do everything in one shot (switch database contexts, run DBCC showfilestats, saving/logging data, etc.):

DECLARE @strSQL nvarchar(1000)

SET @strSQL = 'USE [' + @dbName + '] INSERT INTO MyTable EXECUTE (''DBCC SHOWFILESTATS'')'

EXECUTE (@strSQL)

b) Create (and call in the cursor) a stored procedure that will do all of this for you by passing in the @DBName parameter:

CREATE PROCEDURE PRC_Test
@dbName sysname
AS
BEGIN

DECLARE @strSQL nvarchar(1000)

SET @strSQL = 'USE [' + @dbName + '] INSERT INTO MyTable EXECUTE (''DBCC SHOWFILESTATS'')'

EXECUTE (@strSQL)

END
Go

Also, you don't need the cursor for the sqlperf(logspace) command as the result set will include all databases.

Hope that helps.

Mike

Lyam1971 at 2007-9-3 >