public void TransformCreateTableToAlterColumnTest()
{
string rawScript = @"CREATE TABLE [Person].[Contact](
[ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[NameStyle] [dbo].[NameStyle] NOT NULL CONSTRAINT [DF_Contact_NameStyle] DEFAULT ((0)),
[Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstName] [dbo].[Name] NOT NULL,
[MiddleName] [dbo].[Name] NULL)" ;
string schema = "Person";
string tableName = "Contact";
string changedScript;
#region expected script ...
string changedScriptExpected = @"IF EXISTS(SELECT 1 FROM information_schema.columns WHERE TABLE_NAME = 'Contact' AND TABLE_SCHEMA = 'Person' AND COLUMN_NAME = 'ContactID')
ALTER TABLE [Person].[Contact] ALTER COLUMN [ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
ELSE
ALTER TABLE [Person].[Contact] ADD [ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
GO
IF EXISTS(SELECT 1 FROM information_schema.columns WHERE TABLE_NAME = 'Contact' AND TABLE_SCHEMA = 'Person' AND COLUMN_NAME = 'NameStyle')
ALTER TABLE [Person].[Contact] ALTER COLUMN [NameStyle] [dbo].[NameStyle] NOT NULL
ELSE
ALTER TABLE [Person].[Contact] ADD [NameStyle] [dbo].[NameStyle] NOT NULL
GO
IF EXISTS(SELECT 1 FROM information_schema.columns WHERE TABLE_NAME = 'Contact' AND TABLE_SCHEMA = 'Person' AND COLUMN_NAME = 'Title')
ALTER TABLE [Person].[Contact] ALTER COLUMN [Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ELSE
ALTER TABLE [Person].[Contact] ADD [Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF EXISTS(SELECT 1 FROM information_schema.columns WHERE TABLE_NAME = 'Contact' AND TABLE_SCHEMA = 'Person' AND COLUMN_NAME = 'FirstName')
ALTER TABLE [Person].[Contact] ALTER COLUMN [FirstName] [dbo].[Name] NOT NULL
ELSE
ALTER TABLE [Person].[Contact] ADD [FirstName] [dbo].[Name] NOT NULL
GO
IF EXISTS(SELECT 1 FROM information_schema.columns WHERE TABLE_NAME = 'Contact' AND TABLE_SCHEMA = 'Person' AND COLUMN_NAME = 'MiddleName')
ALTER TABLE [Person].[Contact] ALTER COLUMN [MiddleName] [dbo].[Name] NULL)
ELSE
ALTER TABLE [Person].[Contact] ADD [MiddleName] [dbo].[Name] NULL)
GO
";
#endregion
List <string> expected = new List <string>();
expected.AddRange(new string[] { "ContactID", "NameStyle", "Title", "FirstName", "MiddleName" });
List <string> actual;
actual = ScriptWrapping.TransformCreateTableToAlterColumn(rawScript, schema, tableName, out changedScript);
Assert.AreEqual(changedScriptExpected, changedScript);
Assert.AreEqual(string.Join(",", expected.ToArray()), string.Join(",", actual.ToArray()));
}
public void TransformCreateTableToAlterColumnTest_MissingTableName()
{
string rawScript = @"CREATE TABLE [Person].[Contact](
[ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[NameStyle] [dbo].[NameStyle] NOT NULL CONSTRAINT [DF_Contact_NameStyle] DEFAULT ((0)),
[Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstName] [dbo].[Name] NOT NULL,
[MiddleName] [dbo].[Name] NULL)" ;
string schema = "Person";
string tableName = "";
string changedScript;
string changedScriptExpected = rawScript;
List <string> expected = new List <string>();
List <string> actual;
actual = ScriptWrapping.TransformCreateTableToAlterColumn(rawScript, schema, tableName, out changedScript);
Assert.AreEqual(changedScriptExpected, changedScript);
Assert.AreEqual(string.Join(",", expected.ToArray()), string.Join(",", actual.ToArray()));
}
