152 lines
5.7 KiB
Transact-SQL
152 lines
5.7 KiB
Transact-SQL
use EnVisage
|
|
|
|
begin transaction upd
|
|
|
|
alter table [Client] alter column [Name] nvarchar(200) not null
|
|
go
|
|
|
|
alter table [Company] alter column [Name] nvarchar(200) not null
|
|
go
|
|
|
|
alter table [Contact] alter column [LastName] nvarchar(100) not null
|
|
alter table [Contact] alter column [FirstName] nvarchar(100) not null
|
|
alter table [Contact] alter column [Email] nvarchar(100) not null
|
|
alter table [Contact] add default 0 for [Type]
|
|
alter table [Contact] alter column [Type] int not null
|
|
go
|
|
|
|
alter table [CreditDepartment] alter column [Name] nvarchar(200) not null
|
|
alter table [CreditDepartment] alter column [CreditNumber] nvarchar(100) not null
|
|
alter table [CreditDepartment] add default NewId() for [Id]
|
|
go
|
|
|
|
alter table [Expenditure] alter column [Name] nvarchar(200) not null
|
|
alter table [Expenditure] add default NewId() for [Id]
|
|
go
|
|
|
|
alter table [Expenditure_Category] alter column [ExpenditureId] uniqueidentifier not null
|
|
alter table [Expenditure_Category] alter column [GLId] uniqueidentifier not null
|
|
alter table [Expenditure_Category] alter column [UOMId] uniqueidentifier not null
|
|
alter table [Expenditure_Category] alter column [CreditId] uniqueidentifier not null
|
|
alter table [Expenditure_Category] add default NewId() for [Id]
|
|
go
|
|
|
|
alter table [Expenditure2Expenditure] add default NewId() for [Id]
|
|
go
|
|
|
|
alter table [Fee_Calculation] alter column [ExpenditureCategoryId] uniqueidentifier not null
|
|
alter table [Fee_Calculation] add default NewId() for [Id]
|
|
go
|
|
|
|
alter table [FiscalYear] alter column [Name] nvarchar(100) not null
|
|
alter table [FiscalYear] alter column [Type] int not null
|
|
alter table [FiscalYear] alter column [YearInt] int not null
|
|
alter table [FiscalYear] alter column [QuarterInt] int not null
|
|
alter table [FiscalYear] alter column [PeriodInt] int not null
|
|
alter table [FiscalYear] alter column [StartDate] datetime not null
|
|
alter table [FiscalYear] alter column [EndDate] datetime not null
|
|
alter table [FiscalYear] alter column [StartDate] datetime not null
|
|
alter table [FiscalYear] add default 0 for [NonWorking]
|
|
alter table [FiscalYear] alter column [NonWorking] tinyint not null
|
|
alter table [FiscalYear] add [AdjustingPeriod_temp] bit null
|
|
go
|
|
update [FiscalYear] set [AdjustingPeriod_temp] = 0 where [AdjustingPeriod] != 'YES'
|
|
go
|
|
update [FiscalYear] set [AdjustingPeriod_temp] = 1 where [AdjustingPeriod] = 'YES'
|
|
go
|
|
ALTER TABLE [FiscalYear] DROP COLUMN [AdjustingPeriod]
|
|
go
|
|
EXEC sp_rename 'dbo.FiscalYear.AdjustingPeriod_temp', 'AdjustingPeriod', 'COLUMN';
|
|
GO
|
|
|
|
alter table [GLDepartment] alter column [GLNumber] nvarchar(40) not null
|
|
alter table [GLDepartment] alter column [Name] nvarchar(200) not null
|
|
go
|
|
|
|
delete from [History] where [ModificationType] is null
|
|
go
|
|
alter table [History] alter column [XML] xml not null
|
|
alter table [History] alter column [TimeStamp] date not null
|
|
alter table [History] alter column [ModifiedBy] uniqueidentifier not null
|
|
alter table [History] alter column [EntityType] nvarchar(50) not null
|
|
alter table [History] alter column [ModificationType] nvarchar(10) not null
|
|
go
|
|
|
|
alter table [Holiday] alter column [Name] nvarchar(510) not null
|
|
alter table [Holiday] add default NewId() for [Id]
|
|
go
|
|
|
|
alter table [Note] alter column [Title] nvarchar(100) not null
|
|
go
|
|
|
|
alter table [PeopleResource] add default NewId() for [Id]
|
|
|
|
alter table [Project] alter column [TypeId] uniqueidentifier not null
|
|
alter table [Project] alter column [StatusId] uniqueidentifier not null
|
|
alter table [Project] alter column [Name] nvarchar(200) not null
|
|
go
|
|
|
|
delete from [Rate] where [ExpenditureCategoryId] is null
|
|
alter table [Rate] alter column [ExpenditureCategoryId] uniqueidentifier not null
|
|
alter table [Rate] alter column [Rate] decimal(15, 4) not null
|
|
alter table [Rate] alter column [StartDate] datetime not null
|
|
alter table [Rate] alter column [EndDate] datetime not null
|
|
update [Rate] set [FreezeRate] = 0 where [FreezeRate] is null
|
|
go
|
|
alter table [Rate] alter column [FreezeRate] int not null
|
|
|
|
alter table [Scenario] alter column [Name] nvarchar(200) not null
|
|
alter table [Scenario] alter column [Type] int not null
|
|
alter table [Scenario] add default 0 for [ProjectedRevenue]
|
|
update [Scenario] set [ProjectedRevenue] = 0 where [ProjectedRevenue] is null
|
|
go
|
|
alter table [Scenario] alter column [ProjectedRevenue] decimal(15, 4) null
|
|
go
|
|
alter table [Scenario] add [FreezeRevenue_temp] bit null
|
|
go
|
|
update [Scenario] set [FreezeRevenue_temp] = 0 where [FreezeRevenue] != 1 or FreezeRevenue is null
|
|
go
|
|
update [Scenario] set [FreezeRevenue_temp] = 1 where [FreezeRevenue] = 1
|
|
go
|
|
ALTER TABLE [Scenario] DROP COLUMN [FreezeRevenue]
|
|
go
|
|
EXEC sp_rename 'dbo.Scenario.FreezeRevenue_temp', 'FreezeRevenue', 'COLUMN';
|
|
GO
|
|
alter table [Scenario] alter column [FreezeRevenue] bit not null
|
|
go
|
|
alter table [Scenario] add default GetDate() for [LastUpdate]
|
|
|
|
update [Scenario] set [GrowthScenario] = 0 where [GrowthScenario] is null
|
|
go
|
|
alter table [Scenario] add [GrowthScenario_temp] bit null
|
|
go
|
|
update [Scenario] set [GrowthScenario_temp] = 0 where [GrowthScenario] != 1
|
|
go
|
|
update [Scenario] set [GrowthScenario_temp] = 1 where [GrowthScenario] = 1
|
|
go
|
|
ALTER TABLE [Scenario] DROP COLUMN [GrowthScenario]
|
|
go
|
|
EXEC sp_rename 'dbo.Scenario.GrowthScenario_temp', 'GrowthScenario', 'COLUMN';
|
|
GO
|
|
alter table [Scenario] alter column [GrowthScenario] bit not null
|
|
go
|
|
alter table [Scenario] add default NewId() for [Id]
|
|
go
|
|
|
|
alter table [Team] alter column [Name] nvarchar(100) not null
|
|
alter table [Team] add default NewId() for [Id]
|
|
go
|
|
|
|
alter table [Type] alter column [Name] nvarchar(50) not null
|
|
update [Type] set [IsRevenueGenerating] = 0 where [IsRevenueGenerating] is null
|
|
go
|
|
alter table [Type] add default 0 for [IsRevenueGenerating]
|
|
go
|
|
alter table [Type] alter column [IsRevenueGenerating] bit not null
|
|
|
|
alter table [UOM] alter column [Name] nvarchar(100) not null
|
|
alter table [UOM] alter column [UOMValue] decimal(15, 4) not null
|
|
go
|
|
|
|
|
|
commit transaction upd |