-
Notifications
You must be signed in to change notification settings - Fork 112
Expand file tree
/
Copy pathtSQLt.DropClass.ssp.sql
More file actions
86 lines (84 loc) · 2.79 KB
/
tSQLt.DropClass.ssp.sql
File metadata and controls
86 lines (84 loc) · 2.79 KB
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
IF OBJECT_ID('tSQLt.DropClass') IS NOT NULL DROP PROCEDURE tSQLt.DropClass;
GO
---Build+
CREATE PROCEDURE tSQLt.DropClass
@ClassName NVARCHAR(MAX)
AS
BEGIN
/*SnipStart: CreateDropClassStatement.ps1*/
DECLARE @Cmd NVARCHAR(MAX);
WITH SchemaInfo(FullName, ItemType, SchemaId) AS
(
SELECT
QUOTENAME(S.name),
'schema',
S.schema_id
FROM sys.schemas AS S
WHERE S.schema_id = ISNULL(SCHEMA_ID(@ClassName), SCHEMA_ID(PARSENAME(@ClassName,1)))
),
ConstraintInfo(FullName, ItemType) AS
(/*FOREIGN KEYS need to be dropped before their tables*/
SELECT
QUOTENAME(SCHEMA_NAME(O.schema_id))+'.'+QUOTENAME(O.name),
O.type
FROM sys.objects AS O
JOIN SchemaInfo SI ON SI.SchemaId = O.schema_id
AND O.type IN ('F')
),
ObjectInfo(FullName, ItemType) AS
(
SELECT
QUOTENAME(SCHEMA_NAME(O.schema_id))+'.'+QUOTENAME(O.name),
O.type
FROM sys.objects AS O
JOIN SchemaInfo SI ON SI.SchemaId = O.schema_id
AND O.type NOT IN ('F')
),
TypeInfo(FullName, ItemType) AS
(
SELECT
QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+QUOTENAME(T.name),
'type'
FROM sys.types AS T
JOIN SchemaInfo SI ON SI.SchemaId = T.schema_id
),
XMLSchemaInfo(FullName, ItemType) AS
(
SELECT
QUOTENAME(SCHEMA_NAME(XSC.schema_id))+'.'+QUOTENAME(XSC.name),
'xml_schema_collection'
FROM sys.xml_schema_collections AS XSC
JOIN SchemaInfo SI ON SI.SchemaId = XSC.schema_id
),
DropStatements(no,FullName,ItemType) AS
(
SELECT 10, FullName, ItemType
FROM ConstraintInfo
UNION ALL
SELECT 20, FullName, ItemType
FROM ObjectInfo
UNION ALL
SELECT 30, FullName, ItemType
FROM TypeInfo
UNION ALL
SELECT 40, FullName, ItemType
FROM XMLSchemaInfo
UNION ALL
SELECT 10000, FullName, ItemType
FROM SchemaInfo
),
StatementBlob(xml)AS
(
SELECT GDIC.cmd [text()]
FROM DropStatements DS
CROSS APPLY tSQLt.Private_GetDropItemCmd(DS.FullName, DS.ItemType) GDIC
ORDER BY no
FOR XML PATH(''), TYPE
)
SELECT @Cmd = xml.value('/', 'NVARCHAR(MAX)')
FROM StatementBlob;
EXEC(@Cmd);
/*SnipEnd: CreateDropClassStatement.ps1*/
END;
---Build-
GO