none
How do I create a schema, and a table in that schema?

    Question

  • Using SQL Server Management Studio, how do I create a schema, and then create a table owned by that schema? I need to do this for Sql Server 2000, 2005, & 2008 (for unit tests).

    thanks - dave


    Very funny video - Reporting as a Metaphor
    Thursday, October 07, 2010 6:42 PM

Answers

  • Or by SSMS, Click on your Database, then Security and right click on Schema. Create schema and name your Schema and choose the owner. 

    Next, Right Click on your tables and create a New table. While you are there, if you can press F4, it will list properties for this table. One of the item listed in there is Schema which by default is DBO, you can choose your own schema and assign to your table.


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    Thursday, October 07, 2010 10:29 PM
  • In SQL Server 2000 there is no option of creating schema and everything will be under DBO schema . In SQL 2005 and above :

    Create schema <schema name> AUTHORIZATION <user or role>
    Example :Create schema David AUTHORIZATION DBO

    create table <dbname>.<schemaname>.<tablename(colname datatype) >
    Example : create table MSDB.David.test(a int)


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Marked as answer by DavidThi808 Sunday, October 10, 2010 4:40 PM
    Friday, October 08, 2010 10:43 AM

All replies

  • For creating Schema here is the link for you :

    http://msdn.microsoft.com/en-us/library/ms189462.aspx

     

    And here is for Table:

    http://msdn.microsoft.com/en-us/library/ms174979.aspx

     

     

     


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    Thursday, October 07, 2010 10:22 PM
  • Or by SSMS, Click on your Database, then Security and right click on Schema. Create schema and name your Schema and choose the owner. 

    Next, Right Click on your tables and create a New table. While you are there, if you can press F4, it will list properties for this table. One of the item listed in there is Schema which by default is DBO, you can choose your own schema and assign to your table.


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    Thursday, October 07, 2010 10:29 PM
  • In SQL Server 2000 there is no option of creating schema and everything will be under DBO schema . In SQL 2005 and above :

    Create schema <schema name> AUTHORIZATION <user or role>
    Example :Create schema David AUTHORIZATION DBO

    create table <dbname>.<schemaname>.<tablename(colname datatype) >
    Example : create table MSDB.David.test(a int)


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Marked as answer by DavidThi808 Sunday, October 10, 2010 4:40 PM
    Friday, October 08, 2010 10:43 AM
  • In SQL Server 2000 there is no option of creating schema and everything will be under DBO schema . In SQL 2005 and above :

    Create schema <schema name> AUTHORIZATION <user or role>
    Example :Create schema David AUTHORIZATION DBO

    create table <dbname>.<schemaname>.<tablename(colname datatype) >
    Example : create table MSDB.David.test(a int)


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/


    "In SQL Server 2000 there is no option of creating schema and everything will be under DBO schema "

    Little correction in above statement:
    In SQL 2000, user and schema were not seperated. If you have a user called Abhay, then you can have object in Schema Abhay. (i.e. Abhay.Table)

     


    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Friday, October 08, 2010 11:12 AM
    Moderator
  • Thank you all!!!
    Very funny video - What's your Metaphor?
    Sunday, October 10, 2010 4:41 PM