Re: Looping through databases in stored proc

From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 02/03/05 

Date: Thu, 3 Feb 2005 14:04:04 -0600

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 SQLMonster.com" <forum@SQLMonster.com> wrote in message
news:76dfff48350745e79fe6fe1024555bb9@SQLMonster.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.sqlmonster.com

Relevant Pages

  • Re: Creating a view that uses a sp to retrive data
    ... Use Northwind ... And I still should qualify the database name. ... > db other than the stored procedure. ... exec tempdb..usp')x ...
    (microsoft.public.sqlserver.programming)
  • Re: Please Help
    ... I think if you use USE in a stored procedure, ... execute the entire statement dynamically with an EXEC - e.g. ... The database where the ...
    (microsoft.public.sqlserver.security)
  • Re: stored procedure
    ... Seems you can't have a variable for the database name in CREATE DATABASE. ... the whole CRE¤ATE DATABASE command in a variable and use EXECto execute the statement. ... But why do you want to create a database from inside a stored procedure? ... >> is done inside EXEC so it is in its own scope. ...
    (microsoft.public.sqlserver.server)
  • Re: Stored Procedures in the Master Database
    ... The error occurs on a GRANT, not EXEC. ... >>I have a stored procedure that I want to make available to ... >>following command in the master database: ...
    (microsoft.public.sqlserver.security)
  • Re: How do I: "Build a DOS Path" - My table is using adjacency model - ParentID
    ... Try following stored procedure. ... exec return_path 10, @str output ...
    (microsoft.public.sqlserver.programming)