USE statement - 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 2005 Forums

Transact-SQL (2005)

USE statement

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-23 : 23:20:30

im trying to make the below work:

create table #databaselist(
name varchar(200),
id int IDENTITY(0,1)
)

declare @count2 int, @i2 int, @dbtouse varchar(200)

insert into #databaselist
select name from master..sysdatabases where name <> 'tempdb'

select @count2 = count(name) from #databaselist

set @i2 = 0

while @i2 <= @count2
BEGIN
select @dbtouse = name from #databaselist where id = @i2
use @dbtouse select * from sysobjects
set @i2 = @i2 +1
END

drop table #databaselist

but the use statement wont allow a variable next to it, the point of this is to be an alternative to sp_msforeachdb

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-23 : 23:36:34

problem fixed sorry

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-23 : 23:37:06

you have to use dynamic sql, sp_executesql to do this

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-23 : 23:37:46

How do you solve it ?

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-23 : 23:56:49

pretty much what u said:

create table #databaselist(
name varchar(200),
id int IDENTITY(0,1)
)

declare @count2 int, @i2 int, @dbtouse varchar(200),@sqluse nvarchar(200)

insert into #databaselist
select name from master..sysdatabases where name <> 'tempdb'

select @count2 = count(name) from #databaselist

set @i2 = 0

while @i2 <= @count2
BEGIN
select @dbtouse = name from #databaselist where id = @i2
set @sqluse = 'use '+@dbtouse
exec sp_executesql @sqluse
set @i2 = @i2 +1
END

drop table #databaselist

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-24 : 00:03:55

thanks you

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-24 : 02:06:45

hmm can someone else try the above code, for me its ignore the USE section of it, and its returning results for master not each db

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-24 : 02:11:41

The change of default database only occur within the dynamic sql scope.

Try this

use msdb

declare @s varchar(100)

select @s = 'use master'

exec(@s)

Peter Larsson
Helsingborg, Sweden

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-24 : 02:21:07

that doesnt make it dynamic enough to change with the above script, what im trying to do is go through each db, without using sp_msforeach db

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-24 : 02:22:53

[code]
drop table #databaselist
drop table #sysobjects

create table #databaselist
(
name varchar(200),
id int IDENTITY(0,1)
)

create table #sysobjects
(
dbname varchar(100),
name varchar(200)
)

declare @count2 int, @i2 int, @dbtouse varchar(200),@sqluse nvarchar(200)
declare @sql nvarchar(1000)

insert into #databaselist
select name from master..sysdatabases where name <> 'tempdb'

select @count2 = count(name) from #databaselist

set @i2 = 0

while @i2 <= @count2
BEGIN
select @dbtouse = name from #databaselist where id = @i2

select @sql = N'select db_name(), name from ' + @dbtouse + '..sysobjects'
insert into #sysobjects exec sp_executesql @sql

set @i2 = @i2 +1
END

select * from #sysobjects
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-24 : 02:28:34

My point was that the outer USE did change the default database, and the inner USE (the one in the dynamic execution) did not!
Whatever you are planning to do with the dynamic sql for each database, has to be placed in the same execution as the USE statement!

Try this!

declare	@currname sysname,
	@maxname sysname,
	@sqlcmd varchar(8000)

select	@currname = min(name),
	@maxname = max(name)
from	master..sysdatabases
where	name <> 'tempdb'

while @currname <= @maxname
	begin
		set @sqlcmd = 'use ' + quotename(@currname) + '; select * from information_schema.tables'

		exec (@sqlcmd)

		select	@currname = min(name)
		from	master..sysdatabases
		where	name > @currname
			and name <> 'tempdb'
	end

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-24 : 02:35:04

rnbguy,

use Peter's query if you want one result set per database. For my query, i have combined it into a single result set by using a temp table.

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-24 : 07:10:08

ahh right i didnt know that, so how would i incorporate that with something like this:

Insert into #temp
Select 'DBCC SHOWCONTIG ('+so.name+') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' from
sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type ='U'
AND si.indid < 2
AND si.rows > 0
AND so.name NOT LIKE '% %'

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-24 : 18:37:28

its okay ill do it the reallllyyy long way, i dont think it could be simplified

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-24 : 19:53:02

quote:
Originally posted by rnbguy

its okay ill do it the reallllyyy long way, i dont think it could be simplified

If you thought that, why bother to post at all?

Do you want everything on a silver platter?


create table ##statements (data varchar(8000))

declare	@currname sysname,
	@maxname sysname,
	@sqlcmd varchar(8000)

select	@currname = min(name),
	@maxname = max(name)
from	master..sysdatabases
where	name <> 'tempdb'

while @currname <= @maxname
	begin
		set @sqlcmd = 'use ' + quotename(@currname) + '; 

Insert into ##statements
Select ''USE ' + quotename(@currname) + ';DBCC SHOWCONTIG ('' + so.name + '') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'' from 
sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type = ''U''
AND si.indid < 2
AND si.rows > 0
AND so.name NOT LIKE ''% %''


'
		exec (@sqlcmd)

		select	@currname = min(name)
		from	master..sysdatabases
		where	name > @currname
			and name <> 'tempdb'
	end


select * from ##statements
drop table ##statements

Peter Larsson
Helsingborg, Sweden

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-24 : 19:59:00

is there something wrong with posting a question on a forum peter?

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-24 : 20:02:23

but thanx for the reply either way (minus to attitude)
ill give it a go

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 01:01:31

Is it really that hard to replace the secondary statement in my post on 01/24/2007 : 02:28:34?
Just replace the "; select * from information_schema.tables" with the query you posted later.

My reaction was against that you did not even try for yourself to learn and developer your skills.
The best way to learn and developer your SQL skills, are testing! Test, test, try and test again...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources