some questions about namespaces and schemas

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

some questions about namespaces and schemas

Author

Topic

spider3
Starting Member

11 Posts

Posted - 2007-06-20 : 10:25:25

Hi eveyone, I'm reading about namespaces and schemas in MS SQL 2005, and that's exactly what I was looking for for the structure of my database.

I've a database and 4 different applications that will use it, and every application will create a new database every year, for example, i'd like to use the following schema:

MainDatabase.MyApplication.MyYear.MyTable

In MS SQL i've created my main schema using:

USE Mydatabase;
CREATE SCHEMA MyApplication
GO

Now I want to add sub-groups (in this example, I need to create Year2006, Year2007, and so on).

Even If i create those schemas, how can I "add" them inside the Salary main schema?
I've tryed with
ALTER SCHEMA MyApplication TRANSFER Year2006

but it doesn't work since Year2006 is not an object.

Thank you!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-20 : 10:55:52

Schema is object owner basically, so you can't add sub-schema to it. Notation of db object is db_name.schema.obj_name, that's it.

spider3
Starting Member

11 Posts

Posted - 2007-06-20 : 11:09:33

But how can I crate a situation described in this tutorial?

http://www.functionx.com/sqlserver/Lesson02.htm
(see at the bottom)

I need that MyApplication is the namespace, then Year2006 a schema containing all the tables for this year, and so on with the other years.

Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-20 : 11:30:43

The concept of a NAMESPACE doesn’t exist in a SQL 2005 database.

CODO ERGO SUM

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-20 : 11:38:00

That tutorial references schema.object as namespace, it's virtual.

spider3
Starting Member

11 Posts

Posted - 2007-06-20 : 11:43:06

Mh, this tutorial has tricked me then =(

I'll create the tables manually then and avoid using schemas for now.

thanks for the infos!