Setup:
I have a Visual Studio 2019 .Net solution and there's an SSDT project in it.
Goal:
I need to generate an sql change script automatically on each CI build (TFS) for further including it to build artifacts for distribution.
I know there's sqlpackage.exe tool that can generate a change script, but it requires either an existing database connection or a *.dacpac file to compare against.
Is there a standard way to generate an sql change script automatically on each CI build?
UPDATE:
Example:
Version #1 of my sql project:
MyProject\Schema Objects\Schemas\dbo\Tables\MyTable.sql:
CREATE TABLE [dbo].[MyTable] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (50) NOT NULL,
);
Version #2 of my sql project:
MyProject\Schema Objects\Schemas\dbo\Tables\MyTable.sql:
CREATE TABLE [dbo].[MyTable] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (50) NOT NULL,
[Description] VARCHAR (255) NOT NULL,
);
MyProject\Schema Objects\Schemas\dbo\Programmability\Stored Procedures\MyProcedure.sql
CREATE PROCEDURE [dbo].[MyProcedure]
BEGIN
...
...
END
Script that I can generate now:
CREATE DATABASE
...
...
CREATE TABLE MyTable
...
...
CREATE PROCEDURE MyProcedure
...
...
Script that I need (change script, containing only changes made relative to a previous version):
ALTER TABLE [dbo].[MyTable]
ADD [Description] VARCHAR (255) NOT NULL
GO
ALTER PROCEDURE MyProcedure
...
...
GO
