how to use multiple database from a stored procedure dynamically

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 A开发者_运维知识库S 
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.

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/