How to Query a DB for all it's Schema and Associated Table Names

Question

Hello. What is needed is a list of each schema and tables in a DB. I can find a simple query that returns this simple list.

Found queries that in addition to above and also columns, object IDs, etc..

Can anyone point me in the right direction?

Thank you for any help.

Answer 1

If you want the schemas and tables for a particular database, then

use YourDatabaseName
go
Select s.name As SchemaName, t.name As TableName
From sys.schemas s 
Inner Join sys.tables t On s.schema_id = t.schema_id
Order By SchemaName, TableName;

(Replace YourDatabaaseName with the name of the database you are interested in.)

If you want the Schema and Table names of every database, then

Exec sys.sp_MSforeachdb 'Use [?]
Select ''?'' As DatabaseName, s.name As SchemaName, t.name As TableName
From sys.schemas s 
Inner Join sys.tables t On s.schema_id = t.schema_id
Order By SchemaName, TableName;'

Tom