CODEWITHSUNDEEP
sql-serversystem
Haoest
Haoest

Reputation: 13906

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

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?

Upvotes: 9

Views: 9492

Answers (2)

Gudmundur Orn
Gudmundur Orn

Reputation: 2003

Another possibility is to use:

select * from your_database_name.information_schema.tables

Upvotes: 0

Daniel Schaffer
Daniel Schaffer

Reputation: 57862

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

Upvotes: 16

Related Questions