CODEWITHSUNDEEP
sql-serverdatabase
Sapna
Sapna

Reputation: 411

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

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

Upvotes: 15

Views: 47186

Answers (2)

StuartLC
StuartLC

Reputation: 107237

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

Upvotes: 16

Teis Lindemark
Teis Lindemark

Reputation: 362

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.

Upvotes: 10

Related Questions