How to use table of different database in SQL Server

Question

I have to transport some tables(16 tables) to another database and there are a number of stored procedures(200 tables) which use these tables.

Transporting stored procedures to another database is not preferred.

For my case:

dbA contains sp_xyz stored procedure, tableB and tableC tables.

and content of sp_xyz can be :

 SELECT A.column1, B.column2 
 FROM
 tableB A 
 JOIN tableC B ON A.fk_b_id = B.id

we want to transport tableC to dbB. So how should I change this sp with minimum change.

You should avoid using sp_ as a prefix for stored procedures in SQL Server. It's reserved for Microsofts use (it stands for "system procedure", not stored procedure), and they're slower to access than other names (because master is searched before the current database). In general, you shouldn't need to use prefixes for objects in SQL - just the position of an object in a query or statement should inform you on what type of object it is (exception: tables and views - but they should be interchangable anyway) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 10, 2013 at 7:54
dont forget to mark it as accepted if you got the info you want — Pranay Rana
– Pranay Rana, Commented Jan 10, 2013 at 11:27
@PranayRana I know how to mark. But I prefer to mark question answer 2 days later because I need to see every candidate answer. I have already vote your answer;) — ibrahimyilmaz
– ibrahimyilmaz, Commented Jan 10, 2013 at 15:42
@PranayRana What do you think about creating view of this tables? and stored procedutes reach this table via its view? – ibrahimyilmaz 15 hours ago — ibrahimyilmaz
– ibrahimyilmaz, Commented Jan 11, 2013 at 7:17

Dan Guzman · Accepted Answer · 2018-10-21 20:05:45Z

9

If you want to use a table in another database then you can do like this in sql server when the database is on same server:

Select * from [DBName].[Schema].[Table]

If the database is in another server, specify the linked server name too:

Select * from [DBServer].[DBName].[Schema].[Table]

Schema name - replace by your schema which is "dbo" by default in sql server.

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

answered Jan 10, 2013 at 7:43

Pranay Rana

177k37 gold badges244 silver badges267 bronze badges

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

2 Comments

kindly correct me if it is schema.db or db.schema as i tried with db.schema and it worked for me...

@sfah - its mistake its db.schema

NoNaMe · Accepted Answer · 2013-01-10 07:51:22Z

1

I tried a query for this and found that you can use

SELECT * FROM DB_Name.Schema.Table_Name

e-g

SELECT * FROM abcDB.dbo.address

answered Jan 10, 2013 at 7:51

NoNaMe

6,28833 gold badges86 silver badges114 bronze badges