How to select tables in a schema using sysobjects

How can I list all tables in a SQL Server database schema? I’d like to present a list of tables to the database owner,  who can decide if the tables can be deleted.

The need arose when I attempted to delete a database user  who owned a database schema . I received the error message Msg 15138 - The database principal owns a schema in the database and cannot be dropped. Read the blog post for full details of the problem and the workaround.

One option in the workaround is to drop the schema . Dropping the schema requires the objects within the schema to be dropped.

A couple of steps are required to find and select the tables.

Get the schema ids from the sys.schemas

Select * from sys.schemas

Add the schema ids to the IN  part of the sql statement

select schema_name(schema_id) as [schema_name],
[name] as [table_name]
from sys.objects
where
type = 'U'
AND schema_id IN ('10','19','25','29','33','36')
order by schema_id,[name]

This is just one way of presenting the information required to select the tables owned by a schema. Hopefully this gives you enough detail to customise a solution for your requirements.

Read More on database schemas

SQL Server - ALTER SCHEMA - SQL Server DBA

ALTER AUTHORIZATION sql to change owner of a database - SQL ...

Msg 5075 - The database collation cannot be changed if a schema-bound object depends on it