Query to create a new table to a specific database in SQL?

Question

I have 3 databases sytemdatabases,smoketest and learnqueries .

Every time i write and execute create table query (create table tablename (colname datatype(size))), a new table is created in the systemdatabases.

I need it to be created to smoketest database. I tried on this query (create table smoketest.newtablename(number int,name varchar(50));

It is showing an error (Msg 2760, Level 16, State 1, Line 1

The specified schema name "smoketest" either does not exist or you do not have permission to use it.) when i executed it.

It says 2 chances

1. Database does not exist - but the database exists
2. Does not have permission - what does it mean -how to set permission to create a new table to a specific database

Pls help

Answer 1

The 4 part 'dot' notation in Sql Server for tables is

Server.Database.Schema.Object

So you would need to create the table with at least 3 parts if smoketest is not the current database on your connection, e.g. if you are on master, and assuming you want the new table in schema dbo:

create table smoketest.dbo.Tablename(ID INT)

Alternatively, switch to the smoketest database and create the table with 1 or 2 part naming:

use smoketest
GO
create table dbo.Tablename(ID INT)
GO

Answer 2

USE smoketest
GO

create table newtablename(number int,name varchar(50));

If you have a permission issue, check out this SQL Server 2008: how do I grant privileges to a username?

Hope this helps.