$ SqlDatabase execute ^
"-database=Data Source=server;Initial Catalog=database;Integrated Security=True" ^
-from=c:\SqlDatabase\Examples\ExecuteScriptsFolder ^
-varVariable1=value1 ^
-varVariable2=value2
PS> Execute-SqlDatabase `
-database "Data Source=server;Initial Catalog=database;Integrated Security=True" `
-from c:\SqlDatabase\Examples\ExecuteScriptsFolder `
-var Variable1=value1,Variable2=value2 `
-InformationAction Continueexecute script from folder "c:\SqlDatabase\Examples\ExecuteScriptsFolder" on [MyDatabase] on server [MyServer] with "Variable1=value1" and "Variable2=value2"
| Option | Description |
|---|---|
| -database | set connection string to target database |
| -from | a path to a folder or zip archive with sql scripts or path to a sql script file. Repeat -from to setup several sources. |
| -fromSql | an sql script text. Repeat -fromSql to setup several scripts. |
| -configuration | a path to application configuration file. |
| -log | optional path to log file |
| -var | set a variable in format "=var[name of variable]=[value of variable]" |
| -whatIf | shows what would happen if the command runs. The command is not run |
# execute migration script files in Scripts folder
-from=C:\MyDatabase\Scripts
# execute script files from Scripts.zip archive
-from=C:\MyDatabase\Scripts.zip
# execute script files from Scripts folder in MyDatabase.zip archive
-from=C:\MyDatabase.zip\Scripts
# execute scripts from file Script.sql
-from=C:\MyDatabase\Script.sql
# execute scripts from file Script.sql in MyDatabase.zip archive
-from=C:\MyDatabase.zip\Script.sql"-fromSql=CREATE TABLE [dbo].[Person]"
"-fromSql=CREATE TABLE [{{Schema}}].[{{Table}}]" -varSchema=dbo -varTable=Person-- script.sql
PRINT 'create table {{Schema}}.{{Table}}'
CREATE TABLE [{{Schema}}].[{{Table}}]# execute script.sql
-from=script.sql -varSchema=dbo -varTable=Person
# output
script.sql ...
variable Schema was replaced with dbo
variable Table was replaced with Person-- script at runtime
PRINT 'create table dbo.Person'
CREATE TABLE [dbo].[Person]- 0 - OK
- 1 - invalid command line
- 2 - errors during execution
- run all script`s files in the root folder, sorted alphabetically
- run all script`s in each sub-folder, sorted alphabetically
| File | Execution order |
|---|---|
| data | 4 |
| └── 01_staff.sql | 4.1 |
| 01_demo.sql | 1 |
| 02_demo.Department.sql | 2 |
| 03_demo.Employee.sql | 3 |
| Name | Description |
|---|---|
| DatabaseName | the target database name |
If the database specified in the connection string does not exist, execution will be terminated with the appropriate error.
File name 02_demo.Department.sql
PRINT 'create table demo.Department'
GO
CREATE TABLE demo.Department
(
Id INT NOT NULL IDENTITY(1, 1)
,Name NVARCHAR(300) NOT NULL
)
GO
ALTER TABLE demo.Department ADD CONSTRAINT PK_Department PRIMARY KEY CLUSTERED (Id)
GO
CREATE NONCLUSTERED INDEX IX_Department_Name ON demo.Department (Name)
GODO $$
BEGIN
RAISE NOTICE 'create table demo.department';
END
$$;
CREATE TABLE demo.department
(
id serial
,name varchar(300) NOT NULL
);
ALTER TABLE demo.department ADD CONSTRAINT pk_department PRIMARY KEY (Id);
CREATE INDEX ix_department_name ON demo.department (name);SELECT 'create table department' info;
CREATE TABLE department
(
id INT NOT NULL AUTO_INCREMENT
,name VARCHAR(300) NOT NULL
,PRIMARY KEY pk_department (id)
);
CREATE INDEX ix_department_name ON department (name);File name 02_demo.Department.ps1, see details about powershell scripts here.
param (
$Command,
$Variables
)
Write-Information "create table demo.Department"
$Command.CommandText = @"
CREATE TABLE demo.Department
(
Id INT NOT NULL IDENTITY(1, 1)
,Name NVARCHAR(300) NOT NULL
)
"@
$Command.ExecuteNonQuery()
$Command.CommandText = "ALTER TABLE demo.Department ADD CONSTRAINT PK_Department PRIMARY KEY CLUSTERED (Id)"
$Command.ExecuteNonQuery()
$Command.CommandText = "CREATE NONCLUSTERED INDEX IX_Department_Name ON demo.Department (Name)"
$Command.ExecuteNonQuery()File name 02_demo.Department.dll, see details about assembly scripts here.
namespace <any namespace name>
{
public /*sealed*/ class SqlDatabaseScript /*: IDisposable*/
{
public void Execute(IDbCommand command, IReadOnlyDictionary<string, string> variables)
{
Console.WriteLine("create table demo.Department");
command.CommandText = @"
CREATE TABLE demo.Department
(
Id INT NOT NULL IDENTITY(1, 1)
,Name NVARCHAR(300) NOT NULL
)
";
command.ExecuteNonQuery();
command.CommandText = 'ALTER TABLE demo.Department ADD CONSTRAINT PK_Department PRIMARY KEY CLUSTERED (Id)';
command.ExecuteNonQuery();
command.CommandText = 'CREATE NONCLUSTERED INDEX IX_Department_Name ON demo.Department (Name)';
command.ExecuteNonQuery();
}
}
}