Azure database: create table if not exists

Question

I've seen answers for the 'create table if not exists' questions for Microsoft SQL Server (for instance, CREATE TABLE IF NOT EXISTS equivalent in SQL Server)

However this doesn't work for Azure databases - not sure if there's no access to sysobjects or the information schema, but I can't find a reliable way to find out if a table has already been created, and if it has not, to then create the table. It appears that you have to have access to master for this to work.

When I try this query (from the solution in the aforementioned link) on Azure, it fails:

if not exists (select * from sysobjects where name='cars' and xtype='U')
    create table cars (
        Name varchar(64) not null
    )
go

Error:

Msg 262, Level 14, State 1, Line 2
CREATE TABLE permission denied in database 'master'.

Any idea how to do this on Azure?

not to be overly simplistic, but you have database Master throwing the error. Is your data, in fact, in the master database and not in a named one? — JenInCode, Apr 06 '18 at 19:42
That's a great question. I don't know what they're doing behind the scenes in Azure. This isn't in a server I operate as a VM on Azure, it's their database service. https://azure.microsoft.com/en-us/services/sql-database/ — joelc, Apr 06 '18 at 19:44

score 3 · Accepted Answer · answered Apr 06 '18 at 20:10

3

You can't create tables in the master database in Azure. Please create a database and run your create table script there.

answered Apr 06 '18 at 20:10

Satya_MSFT

1,024
6
10

I have a database on Azure already. I need to see if a table exists in it, and if not, create it. – joelc Apr 06 '18 at 20:20
3

You are not connected to that database and instead connected to master database. Please connect to the database that you want the tabled to be in. in SQL Azure, you cannot execute cross database queries. – Satya_MSFT Apr 06 '18 at 20:40
I'm connected to my database. When I open SSMS, I'm connecting to the servername as shown in Azure for the database instance. If I try to do a 'USE [databasename]' it fails with 'Msg 40508, Level 16, State 1, Line 1 USE statement is not supported to switch between databases. Use a new connection to connect to a different database.' – joelc Apr 06 '18 at 20:45
3

@joelc, from SSMS, select your user database from the drop-down menu. SSMS will creates a new connection for you behind the scenes. – Dan Guzman Apr 06 '18 at 22:28
Got it - the query now works from SSMS. I'll mark @Satya_MSFT answer accepted. Thanks for the clarification Dan! – joelc Apr 06 '18 at 23:28

Azure database: create table if not exists

1 Answers1