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
| CREATE TRIGGER E_CREATE_TABLE
ON DATABASE
FOR CREATE_TABLE
AS
SET NOCOUNT ON;
IF NOT (IS_ROLEMEMBER('role_allowed_to_create_table', USER) = 1)
RETURN;
DECLARE @DDL_DATA XML = EVENTDATA(),
@SCH sysname,
@TBL sysname,
@SQL NVARCHAR(max),
@FG sysname,
@ISFG bit,
@IDX INT,
@OID INT;
SELECT @SCH = @DDL_DATA.value('(EVENT_INSTANCE/SchemaName)[1]', 'sysname'),
@TBL = @DDL_DATA.value('(EVENT_INSTANCE/ObjectName)[1]', 'sysname');
SELECT @FG = fg.name, @IDX = i.index_id, @OID = i.object_id,
@ISFG = CASE partition_number WHEN 1 THEN 1 ELSE 0 END
FROM sys.indexes AS i
JOIN sys.objects AS o
ON i.object_id = o.object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
JOIN sys.partitions p
ON p.object_id = i.object_id
AND p.index_id = i.index_id
JOIN sys.allocation_units a
ON a.container_id = p.partition_id
JOIN sys.filegroups AS fg
ON a.data_space_id = fg.data_space_id
WHERE i.index_id IN (0, 1)
AND o.name = @TBL
AND s.name = @SCH;
IF @FG = 'MonFileGroup'
RETURN
IF @ISFG = 0
BEGIN
RAISERROR('la table %s.%s ne peut pas être créée sur une partition. Elle va être détruite.', 16, 1, @SCH, @TBL);
RETURN;
END
ELSE
RAISERROR('la table %s.%s ne peut pas être créée sur le groupe de fichier %s. Elle va être migré sur le groupe de fichiers adéquat.', 16, 1, @SCH, @TBL, @FG);
ROLLBACK;
IF @IDX = 0
BEGIN
-- pas d'index cluer ton déplace la table en en créant un puis en le supprimant
SET @SQL = 'ALTER TABLE [' + @SCH + '].[' + @TBL + '] ADD _UID UNIQUEIDENTIFIER DEFAULT NEWID() CONSTRAINT _PK PRIMARY KEY ON MonFileGroup';
EXEC (@SQL);
SET @SQL = 'ALTER TABLE [' + @SCH + '].[' + @TBL + '] DROP CONSTRAINT _PK, COLUMN _UID';
EXEC (@SQL);
END
ELSE
BEGIN
-- index cluster on le reconstruit à l'identique sur le filegroup de destination
WITH
idc AS (SELECT ic.object_id, index_id,
ROW_NUMBER() OVER(PARTITION BY ic.object_id, index_id, is_included_column
ORDER BY index_column_id) AS index_column_id,
is_included_column,
c.name, CASE WHEN is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END AS ord
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.index_id = 1
AND ic.object_id = @OID
),
idk AS (SELECT object_id, index_id, index_column_id, 1 AS cols,
CAST('[' + name + '] ' + ord AS NVARCHAR(MAX)) AS KEY_DEF
FROM idc
WHERE is_included_column = 0
AND index_column_id = 1
UNION ALL
SELECT idc.object_id, idc.index_id, idc.index_column_id, cols + 1,
KEY_DEF + ', ' + '[' + idc.name + '] ' + ord
FROM idc
INNER JOIN idk
ON idc.object_id = idk.object_id AND
idc.index_id = idk.index_id AND
idc.index_column_id = idk.index_column_id + 1
WHERE idc.is_included_column = 0),
dfi AS (SELECT idk.*,
ROW_NUMBER() OVER(PARTITION BY idk.object_id, idk.index_id
ORDER BY cols DESC) AS N
FROM idk
)
SELECT @SQL = 'CREATE '
+ CASE WHEN is_unique = 1 THEN ' UNIQUE ' ELSE '' END
+ ' CLUSTERED '+
' INDEX [' + i.name + '] ON [' + s.name +'].[' + o.name
+'] (' + KEY_DEF +')' + ' WITH (DROP_EXISTING = ON) ON MonFileGroup;'
FROM dfi
INNER JOIN sys.indexes AS i
ON dfi.object_id = i.object_id
AND dfi.index_id = i.index_id
INNER JOIN sys.objects AS o
ON i.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE o."type" = 'U' AND N = 1;
EXEC (@SQL);
END;
GO |
