-
Notifications
You must be signed in to change notification settings - Fork 14
/
Copy pathschema_option.sql
201 lines (151 loc) · 13.4 KB
/
schema_option.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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
--******TRANSACTIONAL REPLICATION******
DECLARE @SchemaOption binary(8)
DECLARE @intermediate binary(8)
DECLARE @OptionsInText varchar(2000)
SET @OptionsInText = ' **SCHEMA OPTIONS HERE ARE** '
SET @OptionsInText = @OptionsInText + char(13) + '---------------------------------------'
----------------------------
--Set the schema_option value that you want to decrypt here
SET @schemaoption = <<<Your Schema Option here>>> ---Replace the value here
------------------------------
SET NOCOUNT ON
SET @intermediate= cast(cast(@schemaoption as int) & 0x01 as binary(8))
IF @intermediate = 0x0000000000000001
SET @optionsinText = @optionsinText + char(13) + '0x01 - Generates the object creation script (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x02 as binary(8))
IF @intermediate = 0x0000000000000002
SET @optionsinText = @optionsinText + char(13) + '0x02 - Generates the stored procedures that propagate changes for the article, if defined.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x04 as binary(8))
IF @intermediate = 0x0000000000000004
SET @optionsinText = @optionsinText + char(13) + '0x04 - Identity columns are scripted using the IDENTITY property.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x08 as binary(8))
IF @intermediate = 0x0000000000000008
SET @optionsinText = @optionsinText + char(13) + '0x08 - Replicate timestamp columns. If not set, timestamp columns are replicated as binary.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x10 as binary(8))
IF @intermediate = 0x0000000000000010
SET @optionsinText = @optionsinText + char(13) + '0x10 - Generates a corresponding clustered index. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x20 as binary(8))
IF @intermediate = 0x0000000000000020
SET @optionsinText = @optionsinText + char(13) + '0x20 - Converts user-defined data types (UDT) to base data types at the Subscriber. This option cannot be used when there is a CHECK or DEFAULT constraint on a UDT column, if a UDT column is part of the primary key, or if a computed column references a UDT column. Not supported for Oracle Publishers.'
SET @intermediate = cast(cast(@schemaoption as int) & 0x40 as binary(8))
IF @intermediate = 0x0000000000000040
SET @optionsinText = @optionsinText + char(13) + '0x40 - Generates corresponding nonclustered indexes. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.'
SET @intermediate = cast(cast(@schemaoption as int) & 0x80 as binary(8))
IF @intermediate = 0x0000000000000080
SET @optionsinText = @optionsinText + char(13) + '0x80 - Replicates primary key constraints. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enabled.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x100 as binary(8))
IF @intermediate = 0x0000000000000100
SET @optionsinText = @optionsinText + char(13) + '0x100 - Replicates user triggers on a table article, if defined. Not supported for Oracle Publishers.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x200 as binary(8))
IF @intermediate = 0x0000000000000200
SET @optionsinText = @optionsinText + char(13) + '0x200 - Replicates foreign key constraints. If the referenced table is not part of a publication, all foreign key constraints on a published table are not replicated. Not supported for Oracle Publishers.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x400 as binary(8))
IF @intermediate = 0x0000000000000400
SET @optionsinText = @optionsinText + char(13) + '0x400 - Replicates check constraints. Not supported for Oracle Publishers.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x800 as binary(8))
IF @intermediate = 0x0000000000000800
SET @optionsinText = @optionsinText + char(13) + '0x800 - Replicates defaults. Not supported for Oracle Publishers.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x1000 as binary(8))
IF @intermediate = 0x0000000000001000
SET @optionsinText = @optionsinText + char(13) + '0x1000 - Replicates column-level collation'
SET @intermediate= cast(cast(@schemaoption as int) & 0x2000 as binary(8))
IF @intermediate = 0x0000000000002000
SET @optionsinText = @optionsinText + char(13) + '0x2000 - Replicates extended properties associated with the published article source object. Not supported for Oracle Publishers'
SET @intermediate= cast(cast(@schemaoption as int) & 0x4000 as binary(8))
IF @intermediate = 0x0000000000004000
SET @optionsinText = @optionsinText + char(13) + '0x4000 - Replicates UNIQUE constraints. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enabled'
SET @intermediate= cast(cast(@schemaoption as int) & 0x8000 as binary(8))
IF @intermediate = 0x0000000000008000
SET @optionsinText = @optionsinText + char(13) + '0x8000 - This option is not valid for SQL Server 2005 Publishers'
SET @intermediate= cast(cast(@schemaoption as int) & 0x10000 as binary(8))
IF @intermediate = 0x0000000000010000
SET @optionsinText = @optionsinText + char(13) + '0x10000 - Replicates CHECK constraints as NOT FOR REPLICATION so that the constraints are not enforced during synchronization'
SET @intermediate= cast(cast(@schemaoption as int) & 0x20000 as binary(8))
IF @intermediate = 0x0000000000020000
SET @optionsinText = @optionsinText + char(13) + '0x20000 - Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so that the constraints are not enforced during synchronization'
SET @intermediate= cast(cast(@schemaoption as int) & 0x40000 as binary(8))
IF @intermediate = 0x0000000000040000
SET @optionsinText = @optionsinText + char(13) + '0x40000 - Replicates filegroups associated with a partitioned table or index'
SET @intermediate= cast(cast(@schemaoption as int) & 0x80000 as binary(8))
IF @intermediate = 0x0000000000080000
SET @optionsinText = @optionsinText + char(13) + '0x80000 - Replicates the partition scheme for a partitioned table'
SET @intermediate= cast(cast(@schemaoption as int) & 0x100000 as binary(8))
IF @intermediate = 0x0000000000100000
SET @optionsinText = @optionsinText + char(13) + '0x100000 - Replicates the partition scheme for a partitioned index'
SET @intermediate= cast(cast(@schemaoption as int) & 0x200000 as binary(8))
IF @intermediate = 0x0000000000200000
SET @optionsinText = @optionsinText + char(13) + '0x200000 - Replicates table statistics'
SET @intermediate= cast(cast(@schemaoption as int) & 0x400000 as binary(8))
IF @intermediate = 0x0000000000400000
SET @optionsinText = @optionsinText + char(13) + '0x400000 - Replicates default Bindings'
SET @intermediate= cast(cast(@schemaoption as int) & 0x800000 as binary(8))
IF @intermediate = 0x0000000000800000
SET @optionsinText = @optionsinText + char(13) + '0x800000 - Replicates rule Bindings'
SET @intermediate= cast(cast(@schemaoption as int) & 0x1000000 as binary(8))
IF @intermediate = 0x0000000001000000
SET @optionsinText = @optionsinText + char(13) + '0x1000000 - Replicates the full-text index'
SET @intermediate= cast(cast(@schemaoption as int) & 0x2000000 as binary(8))
IF @intermediate = 0x0000000002000000
SET @optionsinText = @optionsinText + char(13) + '0x2000000 - XML schema collections bound to xml columns are not replicated'
SET @intermediate= cast(cast(@schemaoption as int) & 0x4000000 as binary(8))
IF @intermediate = 0x0000000004000000
SET @optionsinText = @optionsinText + char(13) + '0x4000000 - Replicates indexes on xml columns'
SET @intermediate= cast(cast(@schemaoption as int) & 0x8000000 as binary(8))
IF @intermediate = 0x0000000008000000
SET @optionsinText = @optionsinText + char(13) + '0x8000000 - Creates any schemas not already present on the subscriber'
SET @intermediate= cast(cast(@schemaoption as int) & 0x10000000 as binary(8))
IF @intermediate = 0x0000000010000000
SET @optionsinText = @optionsinText + char(13) + '0x10000000 - Converts xml columns to ntext on the Subscriber'
SET @intermediate= cast(cast(@schemaoption as int) & 0x20000000 as binary(8))
IF @intermediate = 0x0000000020000000
SET @optionsinText = @optionsinText + char(13) + '0x20000000 - Converts large object data types introduced in SQL Server 2005 to data types supported on earlier versions of Microsoft SQL Server'
SET @intermediate= cast(cast(@schemaoption as int) & 0x40000000 as binary(8))
IF @intermediate = 0x0000000040000000
SET @optionsinText = @optionsinText + char(13) + '0x40000000 - Replicates permissions'
SET @intermediate= cast(cast(@schemaoption as int) & 0x80000000 as binary(8))
IF @intermediate = 0x0000000080000000
SET @optionsinText = @optionsinText + char(13) + '0x80000000 - Attempts to drop dependencies to any objects that are not part of the publication'
SET @intermediate= cast(cast(@schemaoption as int) & 0x100000000 as binary(8))
IF @intermediate = 0x0000000100000000
SET @optionsinText = @optionsinText + char(13) + '0x100000000 - Use this option to replicate the FILESTREAM attribute if it is specified on varbinary(max) columns. Do not specify this option if you are replicating tables to SQL Server 2005 Subscribers.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x200000000 as binary(8))
IF @intermediate = 0x0000000200000000
SET @optionsinText = @optionsinText + char(13) + '0x200000000 - Converts date and time data types (date, time, datetimeoffset, and datetime2) introduced in SQL Server 2008 to data types that are supported on earlier versions of SQL Server.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x400000000 as binary(8))
IF @intermediate = 0x0000000400000000
SET @optionsinText = @optionsinText + char(13) + '0x400000000 - Replicates the compression option for data and indexes. For more information, see Data Compression.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x800000000 as binary(8))
IF @intermediate = 0x0000000800000000
SET @optionsinText = @optionsinText + char(13) + '0x800000000 - Set this option to store FILESTREAM data on its own filegroup at the Subscriber. If this option is not set, FILESTREAM data is stored on the default filegroup. Replication does not create filegroups; therefore, if you set this option, you must create the filegroup before you apply the snapshot at the Subscriber.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x1000000000 as binary(8))
IF @intermediate = 0x0000001000000000
SET @optionsinText = @optionsinText + char(13) + '0x1000000000 - Converts common language runtime (CLR) user-defined types (UDTs) that are larger than 8000 bytes to varbinary(max) so that columns of type UDT can be replicated to Subscribers that are running SQL Server 2005.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x2000000000 as binary(8))
IF @intermediate = 0x0000002000000000
SET @optionsinText = @optionsinText + char(13) + '0x2000000000 - Converts the hierarchyid data type to varbinary(max) so that columns of type hierarchyid can be replicated to Subscribers that are running SQL Server 2005.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x4000000000 as binary(8))
IF @intermediate = 0x0000004000000000
SET @optionsinText = @optionsinText + char(13) + '0x4000000000 - Replicates any filtered indexes on the table.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x80000000 as binary(8))
IF @intermediate = 0x0000008000000000
SET @optionsinText = @optionsinText + char(13) + '0x8000000000 - Converts the geography and geometry data types to varbinary(max) so that columns of these types can be replicated to Subscribers that are running SQL Server 2005.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x10000000000 as binary(8))
IF @intermediate = 0x0000010000000000
SET @optionsinText = @optionsinText + char(13) + '0x10000000000 - Replicates indexes on columns of type geography and geometry.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x20000000000 as binary(8))
IF @intermediate = 0x0000020000000000
SET @optionsinText = @optionsinText + char(13) + '0x20000000000 - Replicates the SPARSE attribute for columns.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x40000000000 as binary(8))
IF @intermediate = 0x0000040000000000
SET @optionsinText = @optionsinText + char(13) + '0x40000000000 - Enable scripting by the snapshot agent to create memory-optimized table on the subscriber.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x80000000000 as binary(8))
IF @intermediate = 0x0000080000000000
SET @optionsinText = @optionsinText + char(13) + '0x80000000000 - Converts clustered index to nonclustered index for memory-optimized articles.'
SET @intermediate= cast(cast(@schemaoption as int) & 0x400000000000 as binary(8))
IF @intermediate = 0x0000400000000000
SET @optionsinText = @optionsinText + char(13) + '0x400000000000 - Replicates any non-clustered columnstore indexes on the table(s)'
SET @intermediate= cast(cast(@schemaoption as int) & 0x800000000000 as binary(8))
IF @intermediate = 0x0000800000000000
SET @optionsinText = @optionsinText + char(13) + '0x800000000000 - Replicates any flitered non-clustered columnstore indexes on the table(s).'
--Print the result now
PRINT @optionsinText