I was used to use alter script base on table dbVersion and each change in database was in if statement.
for example:
DECLARE @DbVersion BIGINT,
@now DATETIME = GETDATE()
IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE TYPE = 'U' and NAME = 'dbversion') BEGIN
CREATE TABLE dbversion
(
Id BIGINT NOT NULL IDENTITY PRIMARY KEY,
Version INT NOT NULL,
Description NVARCHAR(2048) NULL,
Created DATETIME2 NOT NULL DEFAULT GETDATE(),
Stamp TIMESTAMP NOT NULL
)
INSERT INTO dbversion (version, description, created)
VALUES (0, 'Create version table for database', GETDATE())
END
SELECT @DbVersion = MAX(version) FROM dbversion
IF @DbVersion = 0 BEGIN
CREATE TABLE AppUserRole
(
Id BIGINT NOT NULL PRIMARY KEY,
RoleName NVARCHAR(16) NOT NULL,
RoleDescription NVARCHAR(64) NOT NULL,
CreatedDate DATETIME2 NOT NULL DEFAULT GETDATE()
)
INSERT INTO AppUserRole(Id, RoleName, RoleDescription, CreatedDate)
VALUES (1, 'role 1', 'role description', @now),
(2, 'role 2', 'role description', @now),
(3, 'role 3', 'role description', @now)
SET @DbVersion = @DbVersion + 1
INSERT INTO dbversion (version, description, created)
VALUES (@DbVersion, 'description', @now)
END
This is ok. I drop a column CreatedDate from table AppUserRole in next version:
IF @DbVersion = 1
BEGIN
ALTER TABLE AppUserRole DROP COLUMN CreatedDate
SET @DbVersion = @DbVersion + 1
INSERT INTO dbversion (version, description, created)
VALUES (@DbVersion, 'description', @now)
END
I am start to use SQL Server Management Studio v17.1 for SQL Server 13.0. In older version of Management Studio, the script always ran without errors.
In new version editor, I get an error
Invalid column name 'CreatedDate'
and the script does not even run.
This behavior will only begin when the table structure cache is updated - until then I can run the script over and over without errors.
In my opinion, Management Studio should not show this error, because in this location in script is no error in fact. If someone removes records in the dbVersion table, the error should occur at runtime.
When I try to debug this script, Management Studio will not allow it because the script ends on the first proper code (usually USE dbNAme)
Is there any way to prevent this script behavior? I use these structured DB scripts on many projects and I do not want to rewrite them - some are already very extensive.
Thank you
