-
Notifications
You must be signed in to change notification settings - Fork 110
Expand file tree
/
Copy pathtSQLt.Private_GetDropItemCmd.sfn.sql
More file actions
86 lines (82 loc) · 2.59 KB
/
Copy pathtSQLt.Private_GetDropItemCmd.sfn.sql
File metadata and controls
86 lines (82 loc) · 2.59 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.Private_GetDropItemCmd') IS NOT NULL DROP FUNCTION tSQLt.Private_GetDropItemCmd;
GO
---Build+
GO
CREATE FUNCTION tSQLt.Private_GetDropItemCmd
(
/*SnipParamStart: CreateDropClassStatement.ps1*/
@FullName NVARCHAR(MAX),
@ItemType NVARCHAR(MAX)
/*SnipParamEnd: CreateDropClassStatement.ps1*/
)
RETURNS TABLE
AS
RETURN
/*SnipStart: CreateDropClassStatement.ps1*/
SELECT
CASE @ItemType
WHEN 'F' THEN 'ALTER TABLE '+(SELECT QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(OBJECT_NAME(parent_object_id)) FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@FullName))+' '
ELSE ''
END+
'DROP ' +
CASE @ItemType
WHEN 'F' THEN 'CONSTRAINT'
WHEN 'IF' THEN 'FUNCTION'
WHEN 'TF' THEN 'FUNCTION'
WHEN 'FN' THEN 'FUNCTION'
WHEN 'FT' THEN 'FUNCTION'
WHEN 'P' THEN 'PROCEDURE'
WHEN 'PC' THEN 'PROCEDURE'
WHEN 'SN' THEN 'SYNONYM'
WHEN 'U' THEN 'TABLE'
WHEN 'V' THEN 'VIEW'
WHEN 'type' THEN 'TYPE'
WHEN 'xml_schema_collection' THEN 'XML SCHEMA COLLECTION'
WHEN 'schema' THEN 'SCHEMA'
END+
' ' +
CASE @ItemType
WHEN 'F' THEN QUOTENAME(OBJECT_NAME(OBJECT_ID(@FullName)))
ELSE @FullName
END+
';' AS cmd
/*SnipEnd: CreateDropClassStatement.ps1*/
GO
---Build-
/*
Object type:
AF = Aggregate function (CLR)
- C = CHECK constraint
- D = DEFAULT (constraint or stand-alone)
+ F = FOREIGN KEY constraint
+ FN = SQL scalar function
FS = Assembly (CLR) scalar-function
+ FT = Assembly (CLR) table-valued function
+ IF = SQL inline table-valued function
IT = Internal table
+ P = SQL Stored Procedure
+ PC = Assembly (CLR) stored-procedure
- PG = Plan guide
- PK = PRIMARY KEY constraint
? R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
- S = System base table
SN = Synonym
SO = Sequence object
+ U = Table (user-defined)
+ V = View
- EC = Edge constraint
Applies to: SQL Server 2012 (11.x) and later.
SQ = Service queue
- TA = Assembly (CLR) DML trigger
+ TF = SQL table-valued-function
- TR = SQL DML trigger
TT = Table type
- UQ = UNIQUE constraint
? X = Extended stored procedure
Applies to: SQL Server 2014 (12.x) and later, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (PDW).
? ST = STATS_TREE
Applies to: SQL Server 2016 (13.x) and later, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (PDW).
ET = External Table
Also think about schema bound objects (an exercise in sorting?? because they need to be dropped in the correct order so that you don't drop parent objects before the child objects)
*/