88 lines
3.3 KiB
Transact-SQL
88 lines
3.3 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
begin tran
|
|
if exists (select 1 from sys.tables where name like 'Department')
|
|
begin
|
|
exec sp_rename 'dbo.Department', 'View'
|
|
end
|
|
if exists (select 1 from sys.all_columns where name like 'DepartmentId' and object_id = OBJECT_ID('Department2CostCenter'))
|
|
begin
|
|
exec sp_rename 'dbo.Department2CostCenter.DepartmentId', 'ViewId', 'COLUMN'
|
|
end
|
|
if exists (select 1 from sys.tables where name like 'Department2CostCenter')
|
|
begin
|
|
exec sp_rename 'dbo.Department2CostCenter', 'View2CostCenter'
|
|
end
|
|
if exists (select 1 from sys.all_columns where name like 'DepartmentId' and object_id = OBJECT_ID('User2GLDepartment'))
|
|
begin
|
|
exec sp_rename 'dbo.User2GLDepartment.DepartmentId', 'ViewId', 'COLUMN'
|
|
end
|
|
if exists (select 1 from sys.tables where name like 'User2GLDepartment')
|
|
begin
|
|
exec sp_rename 'dbo.User2GLDepartment', 'User2View'
|
|
end
|
|
if exists (select 1 from sys.all_columns where name like 'DepartmentId' and object_id = OBJECT_ID('Company2GLDepartment'))
|
|
begin
|
|
exec sp_rename 'dbo.Company2GLDepartment.DepartmentId', 'ViewId', 'COLUMN'
|
|
end
|
|
if exists (select 1 from sys.tables where name like 'Company2GLDepartment')
|
|
begin
|
|
exec sp_rename 'dbo.Company2GLDepartment', 'Company2View'
|
|
end
|
|
|
|
if exists (select * from sys.foreign_keys where name like 'FK_Team2View_TeamId')
|
|
BEGIN
|
|
ALTER TABLE [dbo].[Team2View] DROP CONSTRAINT [FK_Team2View_TeamId]
|
|
END
|
|
if exists (select * from sys.foreign_keys where name like 'FK_Team2View_ViewId')
|
|
BEGIN
|
|
ALTER TABLE [dbo].[Team2View] DROP CONSTRAINT [FK_Team2View_ViewId]
|
|
END
|
|
if not exists (select * from sys.tables where name like 'Team2View')
|
|
BEGIN
|
|
CREATE TABLE [dbo].[Team2View](
|
|
[Id] [uniqueidentifier] NOT NULL,
|
|
[TeamId] [uniqueidentifier] NOT NULL,
|
|
[ViewId] [uniqueidentifier] NOT NULL,
|
|
CONSTRAINT [PK_Team2View] PRIMARY KEY CLUSTERED
|
|
(
|
|
[Id] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
|
|
ALTER TABLE [dbo].[Team2View] WITH CHECK ADD CONSTRAINT [FK_Team2View_View] FOREIGN KEY([ViewId])
|
|
REFERENCES [dbo].[View] ([Id])
|
|
|
|
ALTER TABLE [dbo].[Team2View] CHECK CONSTRAINT [FK_Team2View_View]
|
|
|
|
ALTER TABLE [dbo].[Team2View] WITH CHECK ADD CONSTRAINT [FK_Team2View_TeamId] FOREIGN KEY([TeamId])
|
|
REFERENCES [dbo].[Team] ([Id])
|
|
|
|
ALTER TABLE [dbo].[Team2View] CHECK CONSTRAINT [FK_Team2View_TeamId]
|
|
END
|
|
|
|
update Security set SecurityObject='Views' where SecurityObject like 'Departments'
|
|
update Security set SecurityObject='ViewDashboard' where SecurityObject like 'DepartmentDashboard'
|
|
update AspNetUsers set PagePreferences = REPLACE(PagePreferences, 'Department_Board', 'View_Board') where PagePreferences like '%Department_Board%'
|
|
|
|
if exists (select 1 from sys.all_columns where name like 'departmentid' and object_id = OBJECT_ID('Team'))
|
|
BEGIN
|
|
exec sp_sqlexec 'insert into Team2View
|
|
select newid(), t.Id, t.DepartmentId
|
|
from Team t
|
|
where t.DepartmentId is not null and not exists (select 1 from Team2View tv where tv.TeamId = t.Id and tv.ViewId = t.DepartmentId)'
|
|
|
|
if exists (select * from sys.foreign_keys where name like 'FK_Team_Department')
|
|
BEGIN
|
|
ALTER TABLE [dbo].[Team] DROP CONSTRAINT [FK_Team_Department]
|
|
END
|
|
|
|
alter table team
|
|
drop column departmentId
|
|
END
|
|
commit tran
|
|
--rollback tran
|
|
--select * from AspNetUsers where PagePreferences like '%department%'
|