How do you display a list of tables in another database?

Question

I can use:

select * from sys.tables

in mssql to list all tables in the current database. Is there anyway I can use a similar syntax to display a list of tables in another database?

Let's say I am using A with:

use A

can i show tables in database B?

+9

Haoest Dec 23. '08 at 18:20

2 answers

Another possibility is to use:

select * from your_database_name.information_schema.tables

0

Gudmundur orn Apr 21 09 at 12:03

Daniel Schaffer · Accepted Answer · 2008-12-23T18:23:59+0000

This is for me (MS SQL 2005 and newer):

select * from your_database_name.sys.tables

Be aware that you (or whatever authentication context you are using) still needs read permission on this database.

To use your example:

use a;
go

select * from sys.tables; -- selects table info from a
select * from b.sys.tables; -- selects table info from b