Posted by SQLJunkie on 02/08/07 21:00

Thanks everyone for the replies. The following example is fine for
generic SPs:
SELECT @SPname = @DatabaseName + '.dbo.SPName'
EXEC @SPname

But what if I have to run SPs like SP_updatestats etc? then it will
not work.

Thanks!



On Feb 7, 5:35 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> SQLJunkie (vsinh...@gmail.com) writes:
> > I have a small SQL script that rotates through all databases on the
> > server and executes a Stored Procedure in each of them. Here are the
> > steps:
> > 1: The first step is to get name of databases in to a cursor.
> > 2: Build dynamic SQL to change database to database in variable - this
> > step fails
> > 3: Execute SP
> > 4: Move to next database.
> > Is it not possible to change current database using Dynamic SQL? Can
> > someone help me here please?
>
> Yes, but the effect of the USE lasts only for the duration of the of the
> dynamic SQL.
>
> > SELECT
> > @SQL = 'USE ' + @DatabaseName
>
> > EXEC(@SQL)
>
> > ----- SQL Statement to be run
> > EXEC dbo.SPName
>
> If all you want to do is to run a stored procedure in each database,
> this is the easiest way to do:
>
> SELECT @SPname = @DatabaseName + '.dbo.SPName'
> EXEC @SPname
>
> You may also be interested in sp_MSforeachdb:
>
> EXEC sp_MSforeachdb 'EXEC ?.dbo.SPNAme'
>
> This procedure is undocumented and unsupported, but it's nevertheless
> fairly popular.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Need help running query on data/time field
• Prev in forum: Re: Need advice on system performance
• Thread view: Re: Dynamic SQL does not work!

[Reply to this message]