Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Creating a New Table in a Given Schema

By: FYIcenter.com

(Continued from previous topic...)

How To Create a New Table in a Given Schema?

When you create a new table, you can specify in which schema you want this table to be located by prefixing the table name with the schema name. In the tutorial example below, a new table "test" is created in schema "fyi":

USE FyiCenterData;
GO

CREATE TABLE fyi.test (id INT);
GO
Command(s) completed successfully.

SELECT t.name AS table_name, t.type_desc, 
   s.name AS schema_name
   FROM sys.tables t, sys.schemas s
   WHERE t.schema_id = s.schema_id
   AND t.name = 'test';
GO
table_name  type_desc   schema_name
----------- ----------- ------------
test        USER_TABLE  fyi

The last query confirms that table "test" is inside schema "fyi".

(Continued on next topic...)

1. What Is a Schema in SQL Server 2005?
2. How To Create a New Schema in a Database?
3. How To List All Schemas in a Database?
4. How To Create a New Table in a Given Schema?
5. How To Transfer an Existing Table from One Schema to Another Schema?
6. How To List All Objects in a Given Schema?
7. What Is the Default Schema of Your Login Session?
8. Who Is the Owner of a Schema?
9. How To Change the Ownership of a Schema?
10. What Happens If You Are Trying to Access a Schema Not Owned by You?
11. How To Drop an Existing Schema?