Рефакторинг схем баз данных

use northwind
go
set transaction isolation level serializable
set xact_abort on
go

if object_id('tempdb..#err') is not null
	drop table #err
go
create table #err(flag bit)
go
begin transaction
go

exec sp_rename 'dbo.Customers', 'Customer', 'OBJECT'

go
if (@@error <> 0) and (@@trancount > 0)
	rollback transaction
go
if (@@trancount = 0)
begin
	insert into #err(flag) select cast(1 as bit)
	begin transaction
end
go
ALTER view dbo.[Orders Qry] AS
SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate,
	Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
	Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry,
	Customer.CompanyName, Customer.Address, Customer.City, Customer.Region, Customer.PostalCode, Customer.Country
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
go
raiserror('update view <dbo.Orders Qry>...', 0, 1) with nowait

go
if (@@error <> 0) and (@@trancount > 0)
	rollback transaction
go
if (@@trancount = 0)
begin
	insert into #err(flag) select cast(1 as bit)
	begin transaction
end
go
ALTER view dbo.[Quarterly Orders] AS
SELECT DISTINCT Customer.CustomerID, Customer.CompanyName, Customer.City, Customer.Country
FROM Customer RIGHT JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'
go
raiserror('update view <dbo.Quarterly Orders>...', 0, 1) with nowait

go
if (@@error <> 0) and (@@trancount > 0)
	rollback transaction
go
if (@@trancount = 0)
begin
	insert into #err(flag) select cast(1 as bit)
	begin transaction
end
go
ALTER view dbo.Invoices AS
SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,
	Orders.ShipCountry, Orders.CustomerID, Customer.CompanyName AS CustomerName, Customer.Address, Customer.City,
	Customer.Region, Customer.PostalCode, Customer.Country,
	(FirstName + ' ' + LastName) AS Salesperson,
	Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName,
	"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity,
	"Order Details".Discount,
	(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
FROM 	Shippers INNER JOIN
		(Products INNER JOIN
			(
				(Employees INNER JOIN
					(Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID)
				ON Employees.EmployeeID = Orders.EmployeeID)
			INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
		ON Products.ProductID = "Order Details".ProductID)
	ON Shippers.ShipperID = Orders.ShipVia
go
raiserror('update view <dbo.Invoices>...', 0, 1) with nowait

go
if (@@error <> 0) and (@@trancount > 0)
	rollback transaction
go
if (@@trancount = 0)
begin
	insert into #err(flag) select cast(1 as bit)
	begin transaction
end
go
ALTER PROCEDURE dbo.CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customer C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
go
raiserror('update storedprocedure <dbo.CustOrderHist>...', 0, 1) with nowait

go
if (@@error <> 0) and (@@trancount > 0)
	rollback transaction
go
if (@@trancount = 0)
begin
	insert into #err(flag) select cast(1 as bit)
	begin transaction
end
go
ALTER view dbo.[Customer and Suppliers by City] AS
SELECT City, CompanyName, ContactName, 'Customers' AS Relationship
FROM Customer
UNION SELECT City, CompanyName, ContactName, 'Suppliers'
FROM Suppliers
--ORDER BY City, CompanyName
go
raiserror('update view <dbo.Customer and Suppliers by City>...', 0, 1) with nowait

go
if (@@error <> 0) and (@@trancount > 0)
	rollback transaction
go
if (@@trancount = 0)
begin
	insert into #err(flag) select cast(1 as bit)
	begin transaction
end
go
ALTER view dbo.[Sales Totals by Amount] AS
SELECT "Order Subtotals".Subtotal AS SaleAmount, Orders.OrderID, Customer.CompanyName, Orders.ShippedDate
FROM 	Customer INNER JOIN
		(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
	ON Customer.CustomerID = Orders.CustomerID
WHERE ("Order Subtotals".Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231')
go
raiserror('update view <dbo.Sales Totals by Amount>...', 0, 1) with nowait

go
if (@@error <> 0) and (@@trancount > 0)
	rollback transaction
go
if (@@trancount = 0)
begin
	insert into #err(flag) select cast(1 as bit)
	begin transaction
end
go
if exists (select * from #err)
begin
	print 'the database <northwind> update failed'
	rollback transaction
end
else
begin
	print 'the database <northwind> update succeeded'
	commit transaction
end

go
     

use northwind
go
-- Step 1. Create lookup table.
CREATE TABLE dbo.City (
CityId INT NOT NULL identity(1, 1)
,CityName NVARCHAR(15) NULL
,CONSTRAINT PK_City PRIMARY KEY CLUSTERED (CityId)
,CONSTRAINT City_ixCityName UNIQUE (CityName)
)
GO

-- Step 2. Fill lookup table.
INSERT dbo.City (CityName)
SELECT DISTINCT City
FROM dbo.Customer
GO

-- Step 3. Add column.
ALTER TABLE dbo.Customer ADD CityId INT NULL
GO

-- Step 4. Update table dbo.Customer.
UPDATE s
SET s.CityId = t.CityId
FROM dbo.Customer s
INNER JOIN dbo.City t ON s.City = t.CityName
GO

-- Step 5. Create foreign key constraint.
ALTER TABLE dbo.Customer ADD CONSTRAINT FK_Customer_City FOREIGN KEY (CityId) REFERENCES dbo.City (CityId)
GO
     

USE northwind
GO

-- STEP 1. Add new column(s) --
IF NOT EXISTS (
SELECT *
FROM syscolumns s
WHERE s.NAME = 'Region'
AND s.id = object_id(N'dbo.City')
)
BEGIN
ALTER TABLE dbo.City ADD Region NVARCHAR(15) NULL
END
GO

IF NOT EXISTS (
SELECT *
FROM syscolumns s
WHERE s.NAME = 'Country'
AND s.id = object_id(N'dbo.City')
)
BEGIN
ALTER TABLE dbo.City ADD Country NVARCHAR(15) NULL
END
GO


GO

-- STEP 2. Copy data --
-- (You can modify this query if needed)
SET IDENTITY_INSERT dbo.City ON

INSERT INTO dbo.City WITH (TABLOCKX) (CityId)
SELECT CityId
FROM dbo.Customer src
WHERE NOT EXISTS (
SELECT *
FROM dbo.City dest
WHERE src.CityId = dest.CityId
)

SET IDENTITY_INSERT dbo.City OFF

UPDATE dest
WITH (TABLOCKX)

SET dest.Region = src.Region
,dest.Country = src.Country
FROM dbo.City dest
INNER JOIN dbo.Customer src ON (src.CityId = dest.CityId)
GO

-- STEP 3. Check and modify this dependent objects --
/*
northwind.dbo.[Orders Qry] /*View*/
northwind.dbo.[Quarterly Orders] /*View*/
northwind.dbo.Invoices /*View*/
northwind.dbo.CustOrderHist /*StoredProcedure*/
northwind.dbo.[Customer and Suppliers by City] /*View*/
northwind.dbo.[Sales Totals by Amount] /*View*/

*/
-- STEP 4. Drop column(s) --
-- (Uncomment or run separately this query)
/*
alter table dbo.Customer drop column Region
alter table dbo.Customer drop column Country
*/
GO
     

ALTER view dbo.[Sales Totals by Amount] AS
SELECT st.Subtotal AS SaleAmount, o.OrderID, c.CompanyName, o.ShippedDate
FROM dbo.CustomerV c 
JOIN (
	dbo.Orders o
	JOIN "Order Subtotals" st 
		ON o.OrderID = st.OrderID
)
	ON c.CustomerID = o.CustomerID
WHERE (st.Subtotal >2500) AND (o.ShippedDate BETWEEN '19970101' And '19971231')

GO

ALTER view dbo.[Quarterly Orders] 
AS
SELECT DISTINCT c.CustomerID, c.CompanyName, c.City, c.Country
FROM dbo.CustomerV c 
RIGHT JOIN dbo.Orders o 
	ON c.CustomerID = o.CustomerID
WHERE
	o.OrderDate BETWEEN '19970101' And '19971231'

GO

ALTER view dbo.[Orders Qry] 
AS
SELECT o.OrderID, o.CustomerID, o.EmployeeID, o.OrderDate, o.RequiredDate,
	o.ShippedDate, o.ShipVia, o.Freight, o.ShipName, o.ShipAddress, o.ShipCity,
	o.ShipRegion, o.ShipPostalCode, o.ShipCountry,
	c.CompanyName, c.Address, c.City, c.Region, c.PostalCode, c.Country
FROM dbo.CustomerV c 
INNER JOIN dbo.Orders o 
	ON c.CustomerID = o.CustomerID

GO

ALTER view dbo.Invoices AS
SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,
	Orders.ShipCountry, Orders.CustomerID, Customer.CompanyName AS CustomerName, Customer.Address, Customer.City,
	Customer.Region, Customer.PostalCode, Customer.Country,
	(FirstName + ' ' + LastName) AS Salesperson,
	Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName,
	"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity,
	"Order Details".Discount,
	(CONVERT(money,("Order Details".UnitPrice * Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
FROM 	Shippers INNER JOIN
		(Products INNER JOIN
			(
				(Employees INNER JOIN
					(dbo.CustomerV Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID)
				ON Employees.EmployeeID = Orders.EmployeeID)
			INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
		ON Products.ProductID = "Order Details".ProductID)
	ON Shippers.ShipperID = Orders.ShipVia

GO

ALTER view dbo.[Customer and Suppliers by City] AS
SELECT City, CompanyName, ContactName, 'Customers' AS Relationship
FROM dbo.CustomerV
UNION SELECT City, CompanyName, ContactName, 'Suppliers'
FROM Suppliers
--ORDER BY City, CompanyName

GO
     

IF (object_id(N'dbo.Customer_Create') IS NULL)
BEGIN
	EXEC ('create procedure dbo.Customer_Create as return 0')
END
GO
-- =============================================
--
-- 	dbo.Customer_Create
--
--	Create method.
--
-- 	Date: 07.09.2015, @HabraUser
--
-- =============================================
ALTER PROCEDURE dbo.Customer_Create 
 @CustomerID NCHAR(5)
,@CompanyName NVARCHAR(40)
,@ContactName NVARCHAR(30) = NULL
,@ContactTitle NVARCHAR(30) = NULL
,@Address NVARCHAR(60) = NULL
,@PostalCode NVARCHAR(10) = NULL
,@Phone NVARCHAR(24) = NULL
,@Fax NVARCHAR(24) = NULL
,@CityId INT = NULL
AS
BEGIN
SET NOCOUNT ON

INSERT INTO dbo.Customer (
	CustomerID
	,CompanyName
	,ContactName
	,ContactTitle
	,Address
	,PostalCode
	,Phone
	,Fax
	,CityId
)
VALUES (
	@CustomerID
	,@CompanyName
	,@ContactName
	,@ContactTitle
	,@Address
	,@PostalCode
	,@Phone
	,@Fax
	,@CityId
)

	RETURN 0
END
/*
declare
	@CustomerID NChar(5),	
	@CompanyName NVarChar(40),	
	@ContactName NVarChar(30),	
	@ContactTitle NVarChar(30),	
	@Address NVarChar(60),	
	@PostalCode NVarChar(10),	
	@Phone NVarChar(24),	
	@Fax NVarChar(24),	
	@CityId Int	

select
	@CustomerID = 'CustomerID',	
	@CompanyName = 'CompanyName',	
	@ContactName = 'ContactName',	
	@ContactTitle = 'ContactTitle',	
	@Address = 'Address',	
	@PostalCode = 'PostalCode',	
	@Phone = 'Phone',	
	@Fax = 'Fax',	
	@CityId = null	

exec  dbo.Customer_Create
	@CustomerID = @CustomerID,	
	@CompanyName = @CompanyName,	
	@ContactName = @ContactName,	
	@ContactTitle = @ContactTitle,	
	@Address = @Address,	
	@PostalCode = @PostalCode,	
	@Phone = @Phone,	
	@Fax = @Fax,	
	@CityId = @CityId	

*/
GO

IF (object_id(N'dbo.Customer_Get') IS NULL)
BEGIN
EXEC ('create procedure dbo.Customer_Get as return 0')
END
GO

-- =============================================
--
-- 	dbo.Customer_Get
--
--	Read method.
--
-- 	Date: 07.09.2015, @HabraUser
--
-- =============================================
ALTER PROCEDURE dbo.Customer_Get
	@CustomerID NCHAR(5)
AS
BEGIN
SET NOCOUNT ON

SELECT 
 CustomerID
,CompanyName
,ContactName
,ContactTitle
,Address
,PostalCode
,Phone
,Fax
,CityId
FROM dbo.Customer
WHERE
	CustomerID = @CustomerID

RETURN 0
END
/*
declare
	@CustomerID NChar(5)	

select
	@CustomerID = ?	

exec dbo.Customer_Get
	@CustomerID = @CustomerID	
*/
GO

IF (object_id(N'dbo.Customer_Save') IS NULL)
BEGIN
EXEC ('create procedure dbo.Customer_Save as return 0')
END
GO

-- =============================================
--
-- 	dbo.Customer_Save
--
--	Update method.
--
-- 	Date: 07.09.2015, @HabraUser
--
-- =============================================
ALTER PROCEDURE dbo.Customer_Save 
 @CustomerID NCHAR(5)
,@CompanyName NVARCHAR(40)
,@ContactName NVARCHAR(30) = NULL
,@ContactTitle NVARCHAR(30) = NULL
,@Address NVARCHAR(60) = NULL
,@PostalCode NVARCHAR(10) = NULL
,@Phone NVARCHAR(24) = NULL
,@Fax NVARCHAR(24) = NULL
,@CityId INT = NULL
AS
BEGIN
SET NOCOUNT ON

UPDATE t
	SET t.CompanyName = @CompanyName
	,t.ContactName = @ContactName
	,t.ContactTitle = @ContactTitle
	,t.Address = @Address
	,t.PostalCode = @PostalCode
	,t.Phone = @Phone
	,t.Fax = @Fax
	,t.CityId = @CityId
FROM dbo.Customer AS t
WHERE
	t.CustomerID = @CustomerID

RETURN 0
END
/*
	set nocount on
	set quoted_identifier, ansi_nulls, ansi_warnings, arithabort, concat_null_yields_null, ansi_padding on
	set numeric_roundabort off
	set transaction isolation level read uncommitted

	declare
		@CustomerID NChar(5),	
		@CompanyName NVarChar(40),	
		@ContactName NVarChar(30),	
		@ContactTitle NVarChar(30),	
		@Address NVarChar(60),	
		@PostalCode NVarChar(10),	
		@Phone NVarChar(24),	
		@Fax NVarChar(24),	
		@CityId Int	

	select
		@CustomerID = 'CustomerID',	
		@CompanyName = 'CompanyName',	
		@ContactName = 'ContactName',	
		@ContactTitle = 'ContactTitle',	
		@Address = 'Address',	
		@PostalCode = 'PostalCode',	
		@Phone = 'Phone',	
		@Fax = 'Fax',	
		@CityId = null	


	begin try
		begin tran
			exec dbo.Customer_Save
				@CustomerID = @CustomerID,	
				@CompanyName = @CompanyName,	
				@ContactName = @ContactName,	
				@ContactTitle = @ContactTitle,	
				@Address = @Address,	
				@PostalCode = @PostalCode,	
				@Phone = @Phone,	
				@Fax = @Fax,	
				@CityId = @CityId	
	
			select t.*
			from dbo.Customer as t
			where	
				t.CustomerID = @CustomerID

			if @@trancount > 0
				rollback tran

	end try
	begin catch
		if @@trancount > 0
			rollback tran
	
		declare
			@err nvarchar(2000)
		
		  set @err = 
			  'login: ' + suser_sname() + char(10)
			+ 'ErrorNumber: ' + cast(isnull(error_number(), 0) as varchar) + char(10)
			+ 'ErrorProcedure: ' + isnull(error_procedure(), '') + char(10)
			+ 'ErrorLine: ' + cast(isnull(error_line(), 0) as varchar) + char(10)
			+ 'ErrorMessage: ' + isnull(error_message(), '') + char(10)
			+ 'Date: ' + cast(getdate() as varchar) + char(10)
	
		print @err
		raiserror(@err, 16, 1)
	end catch
*/
GO

IF (object_id(N'dbo.Customer_Del') IS NULL)
BEGIN
EXEC ('create procedure dbo.Customer_Del as return 0')
END
GO

-- =============================================
--
-- 	dbo.Customer_Del
--
--	Delete method.
--
-- 	Date: 07.09.2015, @HabraUser
--
-- =============================================
ALTER PROCEDURE dbo.Customer_Del @CustomerID NCHAR(5)
AS
BEGIN
SET NOCOUNT ON

BEGIN TRY
	BEGIN TRAN

	/* uncomment if needed
		delete from dbo.CustomerCustomerDemo where CustomerID = ?
		delete from dbo.Orders where CustomerID = ?
 
	*/
	DELETE
	FROM dbo.Customer
	WHERE CustomerID = @CustomerID

	COMMIT TRAN
END TRY

BEGIN CATCH
IF @@trancount > 0
	ROLLBACK TRAN

-- catch exception (add you code here)
DECLARE @err NVARCHAR(2000)

SET @err = ERROR_MESSAGE()

RAISERROR (@err, 16, 1)
END CATCH

RETURN 0
END
/*
declare
	@CustomerID NChar(5)	

select
	@CustomerID = ?	

exec dbo.Customer_Del
	@CustomerID = @CustomerID	

*/
GO

IF (object_id(N'dbo.Customer_List') IS NULL)
BEGIN
EXEC ('create procedure dbo.Customer_List as return 0')
END
GO

-- =============================================
--
-- 	dbo.Customer_List
--
--	List method.
--
-- 	Date: 07.09.2015, @HabraUser
--
-- =============================================
ALTER PROCEDURE dbo.Customer_List
AS
BEGIN
SET NOCOUNT ON

SELECT 
	 CustomerID
	,CompanyName
	,ContactName
	,ContactTitle
	,Address
	,PostalCode
	,Phone
	,Fax
	,CityId
FROM dbo.Customer
/* uncomment if needed
	left join dbo.City as t1 on t1.CityId = t.CityId
*/
RETURN 0
END
/*
exec dbo.Customer_List
*/
GO
     

CREATE TABLE [dbo].[CustomerHistory] (
[id] [bigint] IDENTITY NOT NULL
,[action_type] [char](1) NOT NULL
,[modified_date] [datetime] CONSTRAINT [DF_CustomerHistory_modified_date] DEFAULT getdate()
,[modified_login] [sysname] CONSTRAINT [DF_CustomerHistory_modified_login] DEFAULT suser_sname()
,[host_name] [nvarchar](128) CONSTRAINT [DF_CustomerHistory_host_name] DEFAULT host_name()
,[program_name] [nvarchar](128) CONSTRAINT [DF_CustomerHistory_program_name] DEFAULT program_name()
,[CompanyName_old] [nvarchar](40)
,[CompanyName_new] [nvarchar](40)
,[ContactName_old] [nvarchar](30)
,[ContactName_new] [nvarchar](30)
,[ContactTitle_old] [nvarchar](30)
,[ContactTitle_new] [nvarchar](30)
,[Address_old] [nvarchar](60)
,[Address_new] [nvarchar](60)
,[PostalCode_old] [nvarchar](10)
,[PostalCode_new] [nvarchar](10)
,[Phone_old] [nvarchar](24)
,[Phone_new] [nvarchar](24)
,[Fax_old] [nvarchar](24)
,[Fax_new] [nvarchar](24)
,[CityId_old] [int]
,[CityId_new] [int]
,[CustomerID_old] [nchar](5)
,[CustomerID_new] [nchar](5)
,CONSTRAINT [PK_CustomerHistory] PRIMARY KEY ([id])
)
GO

CREATE TRIGGER [dbo].[trg_CustomerHistory] ON [dbo].[Customer]
AFTER INSERT, DELETE, UPDATE
AS
SET NOCOUNT ON

DECLARE @action_type CHAR(1)

IF EXISTS (SELECT *FROM inserted) AND EXISTS (SELECT * FROM deleted)
	SET @action_type = 'U'
ELSE IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
	SET @action_type = 'I'
ELSE IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
	SET @action_type = 'D'

INSERT INTO dbo.CustomerHistory (
	 action_type
	,CompanyName_old
	,CompanyName_new
	,ContactName_old
	,ContactName_new
	,ContactTitle_old
	,ContactTitle_new
	,Address_old
	,Address_new
	,PostalCode_old
	,PostalCode_new
	,Phone_old
	,Phone_new
	,Fax_old
	,Fax_new
	,CityId_old
	,CityId_new
	,CustomerID_old
	,CustomerID_new
)
SELECT 
	 @action_type
	,d.CompanyName
	,i.CompanyName
	,d.ContactName
	,i.ContactName
	,d.ContactTitle
	,i.ContactTitle
	,d.Address
	,i.Address
	,d.PostalCode
	,i.PostalCode
	,d.Phone
	,i.Phone
	,d.Fax
	,i.Fax
	,d.CityId
	,i.CityId
	,d.CustomerID
	,i.CustomerID
FROM inserted i
FULL OUTER JOIN deleted d 
	ON (i.CustomerID = d.CustomerID)
GO