MSSQL : How do i look at contents of a schema?

Question

Hi I am trying to understand schemas better in MSSQL/ SQL Server.

I can find the list of schemas, and see what tables are covered in schemas... however is there a way to simply dump the contents of a schema, much like a table? Many sources say it can't be done, that a schema isn't like a table. How then, do I look at all the contents of a schema?

Answer 1

in sqlserver a fully qualified table name is:

<servername>.<databasename>.<owner>.<tablename>

when schema support was added, a scheme was defined as a owner. so:

SELECT * FROM sys.schemas;

just lists the defined schema (owner) names.

you can find all the tables for a schema (dbo is the default schema name and stands for database owner):

select * 
from sys.tables t
join sys.schemas s on t.schema_id = s.schema_id  
where s.name = 'dbo'

you can use the object catalog to get all the schema information for other objects you want:

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/object-catalog-views-transact-sql?view=sql-server-ver16

these are the tables tools use to generate create database scripts.

Answer 2

Hi @joe yang

How do i look at contents of a schema?

You can query the INFORMATION_SCHEMA views to retrieve information about the objects (tables, views, procedures) within a specific schema.

SELECT * 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'YourSchemaName';

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".