CODEWITHSUNDEEP
sqlsql-server-2008
ibrahimyilmaz
ibrahimyilmaz

Reputation: 18919

How to use table of different database in SQL Server

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.

Upvotes: 4

Views: 10073

Answers (2)

Pranay Rana
Pranay Rana

Reputation: 176906

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.

Upvotes: 9

NoNaMe
NoNaMe

Reputation: 6222

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

Upvotes: 1

Related Questions