Changing Databases within a query

Question

Post reply

Changing Databases within a query

Elliott Berkihiser

SSCarpal Tunnel

Points: 4068
More actions
August 26, 2005 at 12:27 pm

#90639

Hello,
I would like to use a cursor to browse through a chosen sub-set of the databases residing on a single server. Within the cursor, I would like to use Information_Schema.columns to search the tables of that database for columns of a given name.
To use Information_Schema, the current database must be referenced. So I tried dynamically creating the "USE <database name> GO" statement and executing it with either EXEC and EXECUTESQL. This failed, and so did attempts to reference Information_Schema by writing something like
"<database_name_variable>..Information_Schema.columns" This produced an error message saying that there was no linked server named <database_name>
Is there a technique for dynamically using variables to move from one database to another within a query?
Elliott

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

EXEC sp_MSForeachDB 'if ''?'' in (''master'',''model'',''msdb'',''pubs'') begin Select ''?'' as ExecutedIn end'

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 2

Thanks Remi,

sp_MSForeachDB appears to scroll through the databases. But when I attempt to use Information_Schema.columns, the context appears to remain in Master. In other words the column names that are produced are from Master, not from Pubs (for instance).

I think SQL Server does not like to make it easy to dynamically switch the database context.

Elliott

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Forgot you didn't know that one .

EXEC sp_MSForeachDB 'if ''?'' in (''master'',''model'',''msdb'',''pubs'') begin Select O.Name as ObjectName, C.name as ColName from ?.dbo.SysObjects O inner join ?.dbo.SysColumns C on O.id = C.id where O.XType IN (''V'', ''U'') Order by O.Name, C.Colid end'

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

You need to switch context as part of the sp_executesql statement. Try this:

declare @strSQL nvarchar(1000)

set @strSQL = N'use tempdb '

set @strSQL = @strSQL + N'select top 10 * from information_schema.columns'

exec sp_executesql @strSQL

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 5

Thanks for the clarification, Remi. It worked fine.

And thanks for the tip on changing the context, Phil. Now I can script something like:

Set strSQL='USE ' + @db + ' '

to dynamically select a database to enter and to run the Information_Schema view from there.

Best regards,

Elliott