Get Database Object By Name
-
Hey All,
I've stored procedure for exporting and populating data from databases located on outlets/branch offices to database located on head office.
for example :
i've 5 outlets databases just name it outlet1, outlet2,...,outlet5 and 1 database as data warehouse,name it central.All databases above are located in one machine on head office.
Data collected from each outlets and restored into db server machine on head office with corresponding name.
so, i'm writing a stored procedure with several parameters, one of them is @dbname varchar(20) that represents outlet database name like outlet1, outlet2, and so on.
how can i use this parameter in sql statement form like this :
SELECT
*
INTO
central.dbo.Table1
FROM
some_function_to_convert_varchar_into_dbobject(@dbname).dbo.Table1
--OR Using with cursor
DECLARE MyCur INSENSITIVE CURSOR FOR
SELECT
*
FROM
some_function_to_convert_varchar_into_dbobject(@dbname).dbo.Table1
i've been tried to looking for the function that acts like some_function_to_convert_varchar_into_dbobject function on sql server help.There is only 2 functions i've been founded that is DB_ID and DB_NAME that acts like that,but did'nt return value as db object except varchar and int.
i have planning to place this stored procedure on central database.
thanks for any help.
my english is not good enough, so please forgive me if any faults.
-
This was removed by the editor as SPAM
-
There is now way to do this as far as I know.
However, in a round about way there is.
If you use 'sp_helpdb', you will get back all the database names on the server. You could try outputting this into a temprary table, then querying the table to find the actual database name using 'like %dbname'. IE if you know the db name always starts with the same letters, it will be easy.
Best I can come up with.
------------------------------
The Users are always right - when I'm not wrong! -
Thx For Reply,
This problems have been solved with the assist of all you guys, especially Ramesh.
Thx Very Much.
M.F.R
-
Funny, I don't see a post here by Ramesh...
So, what did you guys come up with? 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply