Add And Remove Objects From A Schema In T-SQL 3

[Skittle]

How can I add and remove tables from a schema in T-SQL?



Dazed and confused
N+, MCDBA 2000, MCAD .NET

 

[mrdenny]

Is this what you are looking for?

CREATE TABLE {Schema}.TableName
(Col1 int,
...)

DROP TABLE {Schema}.TableName

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

If you right click a random table in your DB and script it as a Drop, it will include a statement that checks for the existance of the table before it tries dropping it.

Simply re-write the code to look for the table you want it to drop. This way you don't have to worry about the infamous "Table does not exist" error that occurs when you try to drop something that isn't in the database.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[BoulderBum]

ALTER SCHEMA targetSchema TRANSFER sourceSchema.ResourceName

 

[mrdenny]

Skittle, which of the provided options are you looking for?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Skittle]

Sorry about the dely getting back to this - I was dragged away screaming on to a legacy green screen project for '5 mimnutes'. Yeah right.


I was actually looking for a way of removing a table from a schema without deleting the table. A table can exist outside a schema right? So I would asume after a table has been added to a schema it can be taken out again.


Dazed and confused

 

[Skittle]

I think BoulderBum's response is what I was looking for although it leads me to another question.

When you add a table to SQL 2005 WITHOUT specifying a schema, the table does not seem to be part of any schema.
Am I right about this or am I missing something silly?


Dazed and confused

 

[mrdenny]

All objects exist within a schema. No object can exist without a schema.

If you don't specify the schema name when you create the object it uses your default schema to create the object.

Your defualt schema will typically be your username within the database or dbo unless it is specifically set to something else.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

I believe if you have the SysAdmin role on your login in 2k5 and you create the object without specifying the schema, it will also default to dbo.

Denny, Correct me if I'm wrong.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

It will, unless you change the default schema of the dbo user in the database. When you connect to a database as a member of the sysadmin fixed server role, you come into the databased masked to the dbo user, so you get all the settings which do along with that (unless your username is in the database with specific settings of it's own).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com