Organize your SQL Server 2005 tables in a better way !!

When you create tables in SQLServer (2005) by default its all get created under default schema. (dbo.)

If you have too many tables then the table list could be confusing / clumsy.

Here enters Schema

(see image)

To organize your tables into meaningful groups (or namespaces)

First create a schema

CREATE SCHEMA Person
GO

Then Alter your existing table

ALTER SCHEMA Lookup
TRANSFER dbo.Contact
GO

So now that you have grouped your tables into different schemas.

How to use them ? The usual way but with schema name added to it.

SELECT * FROM Person.Contact

---

What is Schema ? (from MSDN)Beginning in SQL Server 2005, each object belongs to a database schema. A database schema is a distinct namespace that is separate from a

database user.  Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any

user, and schema ownership is transferable.

In previous versions of SQL Server, database users and schemas were conceptually the same object. Beginning in SQL Server 2005, users and schemas

are separate, and schemas serve as containers of objects.

Advertisement