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

SQL Server 2005 : Verify table already exists

Verifying that an object exists

If the table exists, it is deleted. If the table does not exist, the DROP TABLE statement is not executed.

IF OBJECT_ID (N’dbo.AWBuildVersion’, N’U’) IS NOT NULL
 DROP TABLE dbo.AWBuildVersion;
GO

Syntax :

OBJECT_ID (‘object_name’, [‘object_type’])

Object Type is optional, and possible values it can hold are.. (Items in bold are frequently used)

Object type:

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

PK = PRIMARY KEY constraint

P = SQL stored procedure

PC = Assembly (CLR) stored procedure

FN = SQL scalar function

FS = Assembly (CLR) scalar function

FT = Assembly (CLR) table-valued function

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML trigger

TR = SQL DML trigger

IF = SQL inlined table-valued function

TF = SQL table-valued-function

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure

IT = Internal table