Simple exec - SQL Server Forums

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

Transact-SQL (2000)

Simple exec

Author

Topic

hydtohou
Starting Member

24 Posts

Posted - 2006-05-24 : 18:28:39

I wanted to do something like the following

declare @sql varchar(100)
declare @dbName varchar(10)
set @dbName = 'somedb'
set @sql= 'Use '+ @dbName
execute (@sql)

It does no change the database context to specified database -in my case 'somedb' .If I set @dbName to any name which is not a database it throws an error.

How can change the database context. I have to @dbName to get the database name.

Regards,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-24 : 18:33:29

Dynamic SQL does not run in the same session as the other commands. So it changed the database but only from within the EXEC part.

Tara Kizer
aka tduggan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-24 : 20:43:17

you can access the table of another db directly
in db1 to access table2 of db2 :

select * from db2.dbo.table2

hydtohou
Starting Member

24 Posts

Posted - 2006-05-25 : 12:09:49

Thanks khtan ,
But I need to use the Select statement in a cursor declaration.

i.e
declare someCursor cursor for select * from @dbName.dbo.sysobjects

Let me tell you what I really want.
I want to run 'sp_recompile' for all my sprocs in all databases.
I know that I can dump all the sprocs in all the databases into a temp table and run it.

But I wanteed to write something which will get all the database names and then for each database gets the sproc names and run sp_recompile. And make it to run as a job.

Regards,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-25 : 12:26:54

Just do this part in dynamic SQL:

select * from @dbName.dbo.sysobjects

Tara Kizer
aka tduggan

hydtohou
Starting Member

24 Posts

Posted - 2006-05-25 : 12:33:28

Was that a question or solution
anyways

declare @sql nvarchar(100)
declare @dbName varchar(20)
set @dbName = 'master'
set @sql = 'select * from '+@dbName+'.dbo.sysobjects'
exec sp_executesql @statement =@sql

Regards,

quote:
Originally posted by tkizer

Just do this part in dynamic SQL:

select * from @dbName.dbo.sysobjects

Tara Kizer
aka tduggan

hydtohou
Starting Member

24 Posts

Posted - 2006-05-25 : 16:06:37

Hi Tara,

I was wondering this is what you wanted me to do.
Regards,

quote:
Originally posted by hydtohou

Was that a question or solution
anyways

declare @sql nvarchar(100)
declare @dbName varchar(20)
set @dbName = 'master'
set @sql = 'select * from '+@dbName+'.dbo.sysobjects'
exec sp_executesql @statement =@sql

Regards,

quote:
Originally posted by tkizer

Just do this part in dynamic SQL:

select * from @dbName.dbo.sysobjects

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-25 : 16:12:03

Yes.

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources