how to use multiple database from a stored procedure dynamically

Question

In Sql Server 2005, I have a stored procedure, in which i have wrote some commands to create a table and add some records in it.

Create Procedure Procedure1 AS 
Begin
    create table TmpTable(CD Decimal(10,0), Descr Varchar(50));
    Insert Into TmpTable Values(0,'Not Applicable');
    Insert Into TmpTable Values(1,'ALL');
    Insert Into TmpTable Values(2,'Selected');
    Insert Into TmpTable Values(3,'Only New');
END

i want to create this table in all available databases, i don't know, how many databases are available when i call this store procedure.

Pasha Immortals · Accepted Answer · 2012-01-15 21:36:52Z

1

You can try looping through the list of Databases on your server using a cursor and then inside the loop for the cursor doing the above code. SQL Server through its Master database which every server instance has has functions in it that you can call to get system vise information.

Ive done some thing in the past.

Try having a look at this link, hopefully it will be useful to you:

http://sqlserverplanet.com/tsql/list-all-tables-in-a-database/

answered Jan 15, 2012 at 21:36

Pasha Immortals

8491 gold badge7 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to use multiple database from a stored procedure dynamically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related