Variable use of 'use' statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Variable use of 'use' statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-17 : 08:23:37

Harry writes "Oke, i am a beginner i know.
I try to create a query with a variable use of the 'use' statement.
My query looks like this

DECLARE c1 CURSOR FOR
SELECT name FROM sysdatabases --for read only
OPEN c1
DECLARE @name sysname
FETCH NEXT FROM c1 INTO @name
WHILE (@@FETCH_STATUS <> -1)
BEGIN
use @name --<<< This does not work
exec sp_spaceused
FETCH NEXT FROM c1 INTO @name
END
DEALLOCATE c1

I want to make one list of all the databases on the server, to show the status of the spaceused.
How can i make this "use" statement variable.

Hope somebody can help.

Thank you in advance."

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-06-17 : 08:44:58

Hi!

This works (dynamic SQL):

DECLARE c1 CURSOR FOR
SELECT name FROM sysdatabases --for read only
OPEN c1
DECLARE @name sysname
FETCH NEXT FROM c1 INTO @name
WHILE (@@FETCH_STATUS <> -1)
BEGIN

exec ('exec ' + @name + '..sp_spaceused')

FETCH NEXT FROM c1 INTO @name
END
DEALLOCATE c1

Regards,
Kalle Dahlberg

dsdeming

479 Posts

Posted - 2002-06-17 : 08:48:39

You have to use dynamic SQL to do what you're trying. Search the forums for "dynamic SQL". That should get you started in the right direction.

Nazim
A custom title

1408 Posts

Posted - 2002-06-17 : 09:21:26

you can avoid dynamic sql on this one. Remember, use @dbname doesnt makes the database active .

sp_msforeachdb 'exec ?..sp_spaceused'

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

hvdtol
Yak Posting Veteran

50 Posts

Posted - 2002-06-17 : 13:28:05

Thank everybody, this is what i was looking for.

Best regards,

Harry

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-09-07 : 04:26:38

I have tried using your code but I get the following:

Server: Msg 201, Level 16, State 4, Procedure sp_MSforeachdb, Line 0
Procedure 'sp_MSforeachdb' expects parameter '@command1', which was not supplied.

Is there anyway I can restrict what database's it runs on? I need a nice and easy way to update 59 databases with a new field. There is also other database's on this server that have different layouts.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources