How to select tables in a schema using sysobjects

14 January,2015 by Jack Vamvas

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to select tables in a schema using sysobjects


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer