Get Database Object By Name

Question

Post reply

Get Database Object By Name

M.F.R

SSC Enthusiast

Points: 151
More actions
September 1, 2006 at 10:21 pm

#91777

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.
September 4, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

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!

M.F.R SSC Enthusiast Points: 151 More actions · Answer 2

M.F.R

SSC Enthusiast

Points: 151

September 5, 2006 at 4:29 am

#658491

Thx For Reply,

This problems have been solved with the assist of all you guys, especially Ramesh.

Thx Very Much.

M.F.R

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

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)
Intro to Tally Tables and Functions