exec ('use ' + @DBName) - how ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

exec ('use ' + @DBName) - how ?

Author

Topic

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-04-26 : 08:05:01

Hello,
I want to do something like this:

declare @DBName varchar(20)
declare @Str varchar(100)

set @DBName = 'Northwind'
set @Str = 'use ' + @DBName
exec (@Str)

Idea is to change Database in Query Analyzer depends on DB I choose.

But, when I started this script, all I got is
'The command(s) completed successfully.'

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-26 : 08:07:35

EXEC (@STR)....context/scope including any objects created/moved within the @STR ends at the end of the EXEC.

I don't think you can do what you are thinking of.
Explain in more detail the objecties of the exercise....and we'll see what we can suggest

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-04-28 : 04:33:35

Simple example:
I want to have in stored procedure, smth like this:
(I am in 'pubs' in QA)

.... sql statements (insert, delete, update, exec procedures ...)
go
use Northwind
go
.... sql statements (insert, delete, update, exec procedures ...)

and 'Northwind' to be a parameter of that stored procedure. So I made @Str = 'Use Northwind' and I tried to execute it.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-28 : 04:37:06

nope can't be done unless you do the second part all in dynamic sql.
no go's in the middle of sprocs.
besides each db should have it's own sprocs for whatever you want.

Go with the flow & have fun! Else fight the flow

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-04-28 : 07:14:00

Ok, I will be concrete.
I have one DB for maintaining all other databases on that server. In that DB there are a lot of stored proc, log tables etc (for backup operation, archiving, copying ...) One of procedures calculates space for all tables in specific DB. And it use sp_spaceused.
But if I use 'MAINTENANCE' database to start sp_spaceused, I couldn't executed it for other databases (exec sp_spaceused 'OtherDB.dbo.TableName'). Because it can work only for current database.
So, I want to include in that procedure 'use OtherDB' and to start sp_spaceused for current DB. I think I saw it somewhere, but I'm not sure.
Or, there is some other way.

Procedure is like this (it is simplified):
CREATE procedure sp_ProcedureName
as
begin

declare @table_name as varchar(50)
declare space_used cursor for
select rtrim(name) from sysobjects where xtype='u'

create table #t
(name varchar (128),
rows integer,
reserved varchar (20),
data varchar (20),
index_size varchar (20),
unused varchar (20))

open space_used
fetch next from space_used into @table_name
while @@fetch_status=0
begin
insert into #t
execute sp_spaceused @table_name
fetch next from space_used into @table_name
end

close space_used
deallocate space_used

select * from #t order by rows desc

end

All I want is to have only one procedure for calculating space, to start it on one database, and to use it for all other databases.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-28 : 10:22:09

Would this link give you a lead on how to move onwards?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35635&SearchTerms=foreachdb

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-28 : 10:52:11

You could do this:

insert into #t 
exec('
use '+@DBName+'
execute sp_spaceused '''+@table_name+'''
')

CODO ERGO SUM

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-28 : 13:14:43

Another way:
EXEC ('EXEC ' + @DBName + '.dbo.sp_spaceused ' + @table_name)

Mike Petanovitch

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-28 : 14:06:39

Isn't @table_name really database name there?

I believe this works with sp_spaceused:

exec pubs.dbo.sp_spaceused 'Authors'

quote:
Originally posted by mpetanovitch

Another way:
EXEC ('EXEC ' + @table_name + '.dbo.sp_spaceused')

Mike Petanovitch

CODO ERGO SUM

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-28 : 14:44:51

Yeah that was a typo.

Mike Petanovitch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources