Looping through databases in stored proc -- sql server programming

Paul
2/3/2005 12:01:02 PM

You'll need to use dynamic SQL. Its complaining about the variable database
name in your EXEC statement. Check out this excellent article regarding
dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html

Just curious... What are you using this info for? Some sort of SQL admin.
application?

Paul

[quoted text, click to view]

Alejandro Mesa
2/3/2005 12:05:02 PM

Try,

use northwind
go

create table #t (
dbn sysname,
fileid int,
filen sysname,
fileg sysname null,
size_ varchar(15),
maxsize_ varchar(15),
growth varchar(15),
usage varchar(128)
)

declare @sql nvarchar(4000)
declare @db sysname
declare databases_cursor cursor
local
static
read_only
for
select
[name]
from
master..sysdatabases
where
dbid > 6
order by
[name]

open databases_cursor

while 1 = 1
begin
fetch next from databases_cursor into @db

if @@error <> 0 or @@fetch_status <> 0 break

set @sql = N'use [' + @db + N'] execute sp_helpfile'

insert into #t
execute sp_executesql @sql
end

close databases_cursor
deallocate databases_cursor

select
*
from
#t
order by
dbn, fileid

drop table #t
go

AMB

[quoted text, click to view]

Keith Kratochvil
2/3/2005 2:04:04 PM

Yuu could try declaring and executing a string within your stored procedure
(I assume that you are using a cursor):

declare @str varchar(255)

set @str = 'exec ' + @dbname + '..sp_helpfile'
--optional
select @str AS TheStringToExecute
exec (@str)

or you can use the undocumented stored procedure that is shown below:

EXEC sp_Msforeachdb 'PRINT (''?''); EXEC sp_helpfile'

--
Keith

[quoted text, click to view]

raydan
2/3/2005 3:00:21 PM

Look at the "Undocumented" stored procedure sp_MSforeachdb in the Master
database.
Don't forget, use at your own risk. Since it is undocumented, it may change
or disappear in the next version or service pack. If this is a one-time
thing, go ahead, but don't use in production code.

[quoted text, click to view]

Bob Barrows [MVP]
2/3/2005 3:04:37 PM

[quoted text, click to view]

http://www.sommarskog.se/dynamic_sql.html

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Robert Richards via SQLMonster.com
2/3/2005 7:50:57 PM

I am trying to loop through the databases on a server (SQL 2000) and
dynamically run sp_helpfile against each database on the server. Of course
that means I need to store the name of the database as a variable or
parameter.

When I use the following code I am told "a USE database statement is not
allowed in a procedure or trigger.":

use @dbname
go
exec sp_helpfile

When I use the following code I am told "Incorrect syntax near '.'"
exec @dbname..sp_helpfile

Any suggestions?

--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Looping through databases in stored proc