Posted by Lucky on 06/23/06 14:32

yeah there is no table. its just a database list that i need. and the
business logic is very very big to paste here. i need some different
tables in different DB and the idea of using the foreachloop is
interesting but i dont know wether it will work with more than 250
lines of PL SQL code.

the o/p mean OUTPUT.

let me know if you want to know anything else.



Erland Sommarskog wrote:
> Lucky (tushar.n.patel@gmail.com) writes:
> > Here i'm pasting the sql code that i want to run. the code is ment to
> > fetch datbase list and update each database with some specific business
> > logic.
> >
> > DECLARE authors_cursor CURSOR FOR
> > SELECT sp_databases
>
> There is no table?
>
> > OPEN authors_cursor
> >
> > FETCH NEXT FROM authors_cursor
> > INTO @Database_name, @database_size, @Remarks
> >
> > WHILE @@FETCH_STATUS = 0
> > BEGIN
> >
> > print 'database : ' + @Database_name
> > print 'some business logic'
> >
> >
> > FETCH NEXT FROM authors_cursor
> > INTO @au_id, @au_fname, @au_lname
> > END
> >
> > CLOSE authors_cursor
> > DEALLOCATE authors_cursor
> >
> > NOTE:
> >
> > please notice the use of procedure to get list of the database in the
> > select statement of the DECLARING CURSOR. i want to use stored
> > procedure's o/p to iterate through the rows returned by the procedure.
>
> What does "o/p" mean?
>
> It would be interesting to know what "some business logic" contains.
>
> It's possible that you could use sp_MSforeachdb:
>
> EXEC sp_MSforeachdb N'SELECT db = ''?'', COUNT(*) FROM [?]..sysobjects'
>
> This procedure is undocumented and not supported from Microsoft, so
> you would have to look into the source code for the gory details on
> how it works. But basically it iterates over all databases, and
> run as the SQL statement once for each database. ? works as placeholder
> for the database name.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]