Looping through databases in stored proc

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?
Message posted via http://www.webservertalk.comLook 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.
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:76dfff48350745e79fe6fe1024555bb9@.SQ
webservertalk.com...
> 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?
> --
> Message posted via http://www.webservertalk.com|||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
"Robert Richards via webservertalk.com" wrote:

> 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?
> --
> Message posted via http://www.webservertalk.com
>|||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
"Robert Richards via webservertalk.com" wrote:

> 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?
> --
> Message posted via http://www.webservertalk.com
>|||Robert Richards via webservertalk.com wrote:
> 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?
>
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.|||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
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:76dfff48350745e79fe6fe1024555bb9@.SQ
webservertalk.com...
> 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?
> --
> Message posted via http://www.webservertalk.com