161

This is from SQL Server 2008, ssms

When I create a table, it creates under dbo.

I would like to create it under a different schema, but when I use the 'New Table' dialog, I can never find the field where to specify this.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Matt
  • 25,943
  • 66
  • 198
  • 303

7 Answers7

284
  1. Right-click on the tables node and choose New Table...
  2. With the table designer open, open the properties window (view -> Properties Window).
  3. You can change the schema that the table will be made in by choosing a schema in the properties window.
adrianbanks
  • 81,306
  • 22
  • 176
  • 206
  • 1
    How would you do that programmatically. i.e. Create Table SUSER_SNAME().[MyTableName] but that actually works. Also, is that a fair way of storing temporary global variables to allow different applications to talk to each other? – Adamantish Sep 03 '14 at 18:12
  • 1
    Ok thanks but I meant having the schema name as a variable according to which user is running it. Did it with a little dynamic SQL but then found a better way to put these vars in a table on the user's machine anyway. – Adamantish Sep 04 '14 at 17:52
  • 2
    @adrianbanks very kind of you to point them in the right direction sir! – Shaun Jul 03 '15 at 15:22
  • 1
    Thanks @adrianbanks. Hadn't done this for a while and was on the verge of writing a create script! – Karl Gjertsen Sep 22 '16 at 10:38
62

Try running CREATE TABLE [schemaname].[tableName]; GO;

This assumes the schemaname exists in your database. Please use CREATE SCHEMA [schemaname] if you need to create a schema as well.

EDIT: updated to note SQL Server 11.03 requiring this be the only statement in the batch.

Shaun
  • 4,057
  • 7
  • 38
  • 48
  • 2
    Yeah, I realize I could do that, I was just wondering if the dialog version had an option somewhere to do this. – Matt Sep 28 '09 at 23:41
  • 1
    I tried the same, its not working, Am I doing something wrong? **OR** it just work in MSSQL 2012? – Pankaj Parkar Nov 26 '15 at 13:07
  • 9
    @PankajParkar: This won't work if schema doesn't exist. Create schema first using `create schema [schema_Name]` followed by above query. – Sangram Nandkhile Jan 05 '16 at 06:47
17

                           create a database schema in SQL Server 2008
1. Navigate to Security > Schemas
2. Right click on Schemas and select New Schema
3. Complete the details in the General tab for the new schema. Like, the schema name is "MySchema" and the schema owner is "Admin".
4. Add users to the schema as required and set their permissions:
5. Add any extended properties (via the Extended Properties tab)
6. Click OK.
                           Add a Table to the New Schema "MySchema"
1. In Object Explorer, right click on the table name and select "Design":
2. Changing database schema for a table in SQL Server Management Studio
3. From Design view, press F4 to display the Properties window.
4. From the Properties window, change the schema to the desired schema:
5. Close Design View by right clicking the tab and selecting "Close":
6. Closing Design View
7. Click "OK" when prompted to save
8. Your table has now been transferred to the "MySchema" schema.

Refresh the Object Browser view To confirm the changes
Done

Aftab Uddin
  • 169
  • 1
  • 3
15

Hit F4 and you'll get what you are looking for.

map
  • 184
  • 1
  • 4
11

Shaun F's answer will not work if Schema doesn't exist in the DB. If anyone is looking for way to create schema then just execute following script to create schema.

create schema [schema_name]
CREATE TABLE [schema_name].[table_name](
 ...
) ON [PRIMARY]

While adding new table, go to table design mode and press F4 to open property Window and select the schema from dropdown. Default is dbo.

You can also change the schema of the current Table using Property window.

Refer:

enter image description here

Sangram Nandkhile
  • 17,634
  • 19
  • 82
  • 116
  • 1
    In SQL Server 11.0.3+ this will throw an error, CREATE SCHEMA must be the only operation in the batch. `CREATE SCHEMA setup; GO` – hajikelist Nov 15 '16 at 23:43
4

When I create a table using SSMS 2008, I see 3 panes:

  • The column designer
  • Column properties
  • The table properties

In the table properties pane, there is a field: Schema which allows you to select the schema.

Brannon
  • 25,687
  • 5
  • 39
  • 44
2

The default schema for the user could be changed with the following query and avoids changing the property every time a table is to be created.

USE [DBName] 
GO 
ALTER USER [YourUserName] WITH DEFAULT_SCHEMA = [YourSchema] 
GO
Nagaraj Raveendran
  • 1,150
  • 15
  • 23