How to get SCHEMA.TABLES from another database?

Question

I want to get a list of tables exist in another database. for example if i have connected DB1 database and i want to get a list of tables from DB2 then how is it possible?

I know there are some another approaches like connect DB2 and execute insert query to insert schema into #temp table then connect DB1 using USE [DB1] statement and use that #temp table.

But, I don't want to change my sql connection at runtime. Because, there are some dependencies i have created on my sql connection.

UPDATED:

Database can be restored in same server. Now i am using following query to get Table List from the database.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

version of sql server is 2005.

Answer 1

You need sp_addlinkedserver()

http://msdn.microsoft.com/en-us/library/ms190479.aspx

Example:

exec sp_addlinkedserver @server = 'test'

then

select * from [server].[database].[schema].[table]

Copy Table Schema and Data From One Database to Another Database in SQL Server

Answer 2

Remus Rusanu has already mentioned Here

Try this Query

declare @sql nvarchar(max);
set @sql = N'select cast(''master'' as sysname) as db_name, name collate Latin1_General_CI_AI, object_id, schema_id, cast(1 as int) as database_id  from master.sys.tables ';

select @sql = @sql + N' union all select ' + quotename(name,'''')+ ', name collate Latin1_General_CI_AI, object_id, schema_id, ' + cast(database_id as nvarchar(10)) + N' from ' + quotename(name) + N'.sys.tables'
from sys.databases where database_id > 1
and state = 0
and user_access = 0;

exec sp_executesql @sql;

Answer 3

I assume that you've set the database in your connection string.

If the other database is on the same server, you can reference them with a three-part name

select * from otherdb.schema.table

If it's not, you can use a four-part name

select * from otherserver.otherdb.schema.table 

But my advice would be to not do that directly but rather create a synonym for each object that you plan to reference externally.

That way you gain some flexibility if you do something like rename the external database.