Как добавить создание таблицы в базу данных sql server?

Question

[atm87]

Есть код создания базы данных.
Как добавить в код ниже создание таблицы ?

$Server = '.\SQLEXPRESS'
$InitialCatalog = 'master'
$DatabaseName = 'managementDB'
$ConnectionString = "Data Source=$Server; Initial Catalog=$InitialCatalog; Integrated Security=True;"

$conn = New-Object Data.SqlClient.SqlConnection;
$conn.ConnectionString = $ConnectionString;
$conn.Open();
Try {
	$sql = "IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '$DatabaseName') CREATE DATABASE [$DatabaseName] COLLATE Cyrillic_General_CI_AS;"
	$cmd = New-Object Data.SqlClient.SqlCommand $sql, $conn;
	$cmd.ExecuteNonQuery();
}
Catch {
    Write-Host "Error creating database" $DatabaseName ": " $_.Exception.Message
    Exit
}
    
$cmd.Dispose();
$conn.Close();
$conn.Dispose();

Comments

[Роман Безруков]

Надо поднять соединение уже к самой базе данных, т.е. $ConnectionString будет типа "Server = $SQLServer; Database = $SQLDBName; User ID = $uid; Password = $pwd;"
дальше по аналогии с этими командами

$sql = "IF NOT EXISTS...;"
$cmd = New-Object Data.SqlClient.SqlCommand $sql, $conn;
$cmd.ExecuteNonQuery();

[MaxKozlov]

можно и через use, я думаю
а то и через create $database.$table

[atm87]

Думаю вот так:

$Server = '.\SQLEXPRESS'
$InitialCatalog = 'master'
$DatabaseName = 'managementDB'

# Connect to SQL server
$conn = New-Object System.Data.Sqlclient.SqlConnection 
$conn.ConnectionString = "Data Source=$Server; Initial Catalog=$InitialCatalog; Integrated Security=True;"
$conn.Open()

# Создание БД
$cmd = $conn.CreateCommand() 
$cmd.CommandText = "IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '$DatabaseName') CREATE DATABASE [$DatabaseName] COLLATE Cyrillic_General_CI_AS;"
$cmd.ExecuteNonQuery() | Out-Null

# Создание БД
$cmd.CommandText = "USE $DatabaseName; IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ipaddress' ) CREATE TABLE [dbo].[ipaddress]([ip][varchar](15) NULL UNIQUE) ON [PRIMARY];"

$cmd.ExecuteNonQuery() | Out-Null 

$cmd.Dispose();
$conn.Close();
$conn.Dispose();

Answer 1

[Константин Цветков]

atm87:

$Server = '.\SQLEXPRESS'
$InitialCatalog = 'master'
$DatabaseName = 'managementDB'

# Connect to SQL server
$conn = New-Object System.Data.Sqlclient.SqlConnection 
$conn.ConnectionString = "Data Source=$Server; Initial Catalog=$InitialCatalog; Integrated Security=True;"
$conn.Open()

# Создание БД
$cmd = $conn.CreateCommand() 
$cmd.CommandText = "IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '$DatabaseName') CREATE DATABASE [$DatabaseName] COLLATE Cyrillic_General_CI_AS;"
$cmd.ExecuteNonQuery() | Out-Null

# Создание БД
$cmd.CommandText = "USE $DatabaseName; IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ipaddress' ) CREATE TABLE [dbo].[ipaddress]([ip][varchar](15) NULL UNIQUE) ON [PRIMARY];"

$cmd.ExecuteNonQuery() | Out-Null 

$cmd.Dispose();
$conn.Close();
$conn.Dispose();