USE @database syntax error

[xp8103]

I am trying to set up a procedure to take care of truncating and shrinking the t-logs of all the user db's on my server. I keep getting a syntax error on the "USE @database" line. what the heck am I missing???


USE Master
DECLARE @fileName varchar(50)
DECLARE @database varchar (100)
DECLARE dbnames Cursor
FOR SELECT Name
FROM sysdatabases
OPEN dbnames
FETCH NEXT FROM dbnames INTO @database
WHILE (@@Fetch_Status <> -1)
BEGIN
IF @database NOT IN ('master','model','tempdb','msdb')
BEGIN
USE @database
SELECT @fileName = NAME FROM sys.database_files
BACKUP LOG @database WITH TRUNCATE_ONLY
DBCC SHRINKFILE (@fileName)
END
FETCH NEXT FROM dbnames INTO @database
END
CLOSE dbnames
DEALLOCATE dbnames

 

[HarleyQuinn]

You would need to use EXEC, something like:

EXEC ('Use '+ @database)

Hope this helps


HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[xp8103]

Yep, that works. But I discovered it doesn't. USE isn't recognized inside the loop because USE Master was defined first for this thread. Putting the functions inside the loop in a string and executing it spawns another thread where USE @database works. Thanks!!