Posted by Erland Sommarskog on 06/23/06 10:50

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]