-
Notifications
You must be signed in to change notification settings - Fork 5
/
I__create_procs.sql
114 lines (104 loc) · 4.31 KB
/
I__create_procs.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
-- Initial script to create stored procedures etc for sqlserver platform
-- create table-value-parameters
if not exists (select name from sys.types where name = 'ebean_bigint_tvp')
create type ebean_bigint_tvp as table (c1 bigint);
if not exists (select name from sys.types where name = 'ebean_float_tvp')
create type ebean_float_tvp as table (c1 float);
if not exists (select name from sys.types where name = 'ebean_bit_tvp')
create type ebean_bit_tvp as table (c1 bit);
if not exists (select name from sys.types where name = 'ebean_date_tvp')
create type ebean_date_tvp as table (c1 date);
if not exists (select name from sys.types where name = 'ebean_time_tvp')
create type ebean_time_tvp as table (c1 time);
if not exists (select name from sys.types where name = 'ebean_uniqueidentifier_tvp')
create type ebean_uniqueidentifier_tvp as table (c1 uniqueidentifier);
if not exists (select name from sys.types where name = 'ebean_nvarchar_tvp')
create type ebean_nvarchar_tvp as table (c1 nvarchar(max));
delimiter $$
--
-- PROCEDURE: usp_ebean_drop_indices TABLE, COLUMN
-- deletes all indices referring to TABLE.COLUMN
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_indices @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
declare @indexName nvarchar(255)
BEGIN
DECLARE index_cursor CURSOR FOR SELECT i.name from sys.indexes i
join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
join sys.columns c on c.object_id = ic.object_id and c.column_id = ic.column_id
where i.object_id = OBJECT_ID(@tableName) AND c.name = @columnName;
OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @indexName
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'drop index ' + @indexName + ' on ' + @tableName;
EXECUTE(@sql);
FETCH NEXT FROM index_cursor INTO @indexName
END;
CLOSE index_cursor;
DEALLOCATE index_cursor;
END
$$
delimiter $$
--
-- PROCEDURE: usp_ebean_drop_default_constraint TABLE, COLUMN
-- deletes the default constraint, which has a random name
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_default_constraint @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @tmp nvarchar(1000)
BEGIN
select @tmp = t1.name from sys.default_constraints t1
join sys.columns t2 on t1.object_id = t2.default_object_id
where t1.parent_object_id = OBJECT_ID(@tableName) and t2.name = @columnName;
if @tmp is not null EXEC('alter table ' + @tableName +' drop constraint ' + @tmp);
END
$$
delimiter $$
--
-- PROCEDURE: usp_ebean_drop_constraints TABLE, COLUMN
-- deletes constraints and foreign keys refering to TABLE.COLUMN
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_constraints @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
declare @constraintName nvarchar(255)
BEGIN
DECLARE name_cursor CURSOR FOR
SELECT cc.name from sys.check_constraints cc
join sys.columns c on c.object_id = cc.parent_object_id and c.column_id = cc.parent_column_id
where parent_object_id = OBJECT_ID(@tableName) AND c.name = @columnName
UNION SELECT fk.name from sys.foreign_keys fk
join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
and fkc.parent_object_id = fk.parent_object_id
join sys.columns c on c.object_id = fkc.parent_object_id and c.column_id = fkc.parent_column_id
where fkc.parent_object_id = OBJECT_ID(@tableName) AND c.name = @columnName;
OPEN name_cursor
FETCH NEXT FROM name_cursor INTO @constraintName
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'alter table ' + @tableName + ' drop constraint ' + @constraintName;
EXECUTE(@sql);
FETCH NEXT FROM name_cursor INTO @constraintName
END;
CLOSE name_cursor;
DEALLOCATE name_cursor;
END
$$
delimiter $$
--
-- PROCEDURE: usp_ebean_drop_column TABLE, COLUMN
-- deletes the column annd ensures that all indices and constraints are dropped first
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_column @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
BEGIN
EXEC usp_ebean_drop_indices @tableName, @columnName;
EXEC usp_ebean_drop_default_constraint @tableName, @columnName;
EXEC usp_ebean_drop_constraints @tableName, @columnName;
set @sql = 'alter table ' + @tableName + ' drop column ' + @columnName;
EXECUTE(@sql);
END
$$