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

Question

I can use select * from sys.tables in mssql to show a list of all tables in the current database. Is there anyways I can use similar syntax to show list of tables in another database?

Say I am using A with use A statement, can I show tables in database B?

Answer 1

This does it for me (MS SQL 2005 and newer):

select * from your_database_name.sys.tables

Keep in mind that you (or whatever authentication context you're using) will still need read permission on that 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

Answer 2

Another possibility is to use:

select * from your_database_name.information_schema.tables