How - Create and Use Database Directly After Creation in SQL Server

How - create and use database directly after creation in SQL Server?

Put a GO statement after the CREATE...

...
CREATE DATABASE Arms2;
PRINT 'CREATE DATABASE Arms2'
GO
USE Arms2

Creating a database through an sql script and then using that database

Yes, insert the GO command in between your scripts.

CREATE DATABASE MyDatabase
GO
USE [MyDatabase]

Establishing a new SQL Connection after creating a database in a server

The way I would do this is to add a USE statement before the CREATE TABLE. So the CREATE TABLE command would look like this:

"USE  " & dbName & ";
GO

CREATE TABLE ..."

EDIT: As pointed out in the comments, the GO separator cannot be used in a .NET SQL Command.

Instead one can use three-part naming to specify the database like this:

"CREATE TABLE   " & dbName & ".dbo.MyTable ( ..."

Or use SMO which does allow one to use the GO separator. This technique is thoroughly described in the accepted answer to this question.

How to use USE DBName after CREATE DATABASE DBName in one script in Transact-SQL?

Handle the rollback yourself.

• first create database in one batch
• next use database in another batch
• If you decide that you no longer want the database
  
  • drop database

Create database and table in a single .sql script

This worked for me:

CREATE DATABASE mydb;
GO
USE mydb;
GO
CREATE TABLE mydb.dbo.Customers
    (
      ID INT ,
      FirstName VARCHAR(255) ,
      LastName VARCHAR(255),
    );

How to create a database from existing Database without any data?

Try this DBCC command: DBCC CLONEDATABASE
https://support.microsoft.com/en-us/help/3177838/how-to-use-dbcc-clonedatabase-to-generate-a-schema-and-statistics-only

CREATE DATABASE runs successfully but no DB created

Solution: Run SSMS as Admin.

Despite CREATE DATABASE working fine via an unelevated command line, SSMS requires admin privileges to do the same. The silent failure is...a possible bug?

I'll do further research on this but my working hypothesis is that when executing via a command line, it uses the SQL Server Windows Service instance's credentials (Network Service for older versions, NT Service\MSSQL$SQLEXPRESS for later versions - there's a strong whiff of a permissions issue here), to write to the %programfiles% folder. SSMS uses the currently logged in user (unelevated) if connecting via a Windows account. Without elevation, there is no write access to %programfiles%.

Still though, even if this is the case (to be verified), there should still be an access error when executing CREATE DATABASE in this context.

---

Related Topics