USE @mDATABASE is not correct , why ?

[sarah77]

I want to read the schema information for each database
on the server to feed my data dictionary. I wrote the following :

USE master
DECLARE MyCursor CURSOR FOR select name from sysdatabases
DECLARE @mDB sysname(50)
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @mDB
WHILE @@FETCH_STATUS=0
BEGIN
USE @mDB
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
FETCH NEXT FROM MyCursor INTO @mDB
END


But an error message says "incorrect syntax" against the statement USE @mDB

Why ? can't I use avariable for the name of database !

Any comment will be much appriciated

 

[codestorm]

Not sure about it in this instance, but normally to put variables into SQL statements in that manner you'd probably have to use EXEC <insert witticism here>
codestorm

 

[tlbroadbent]

You cannot use variable names for database, table, view, procedure or column names in a SQL statement. You can dynamically create and execute a SQL statement and use variables while creating the statment.

Besides that, executing a USE statment in the procedure will not work. Try the following.

---------------------
USE master

DECLARE MyCursor CURSOR FOR
SELECT name FROM sysdatabases

DECLARE @mDB sysname

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @mDB

WHILE @@FETCH_STATUS=0
BEGIN
EXEC ('SELECT * FROM ' + @mDB + '.INFORMATION_SCHEMA.COLUMNS')
FETCH NEXT FROM MyCursor INTO @mDB
END

CLOSE MyCursor
DEALLOCATE MyCursor Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.