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