DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drv_bcpmetadata  14 déc. 14Defects Propagation Dependencies

1     /*Stored procedure to support BCP implementation ADDPOINT_BULK_INSERT*/
2     CREATE OR REPLACE PROCEDURE sp_drv_bcpmetadata(@table_name VARCHAR(771),
3         @table_owner VARCHAR(32) = NULL,
4         @table_qualifier VARCHAR(32) = NULL)
5     AS
6         DECLARE @use_table_qualifier INT
7         DECLARE @table_id INT
8         DECLARE @table_lock_scheme INT
9         DECLARE @dbid INT
10        DECLARE @number_of_columns INT
11        DECLARE @allow_wide_dol INT
12        DECLARE @max_rowlen INT
13        DECLARE @table_compressed INT
14    
15        /*This will make sure that we will not emit any
16        **done count tokens associated with setting a variable*/
17        SET NOCOUNT ON
18    
19        /*this will make sure that all rows are sent even if
20        **the client 'set rowcount' is differect*/
21        SET ROWCOUNT 0
22    
23        IF CHARINDEX('#', @table_name) = 1
24        BEGIN
25            SELECT @table_qualifier = DB_NAME(@@tempdbid)
26        END
27        ELSE IF @table_qualifier IS NULL
28        BEGIN
29            SELECT @table_qualifier = DB_NAME()
30        END
31    
32        SELECT @dbid = DB_ID(@table_qualifier)
33        SELECT @use_table_qualifier = CASE DB_NAME() WHEN @table_qualifier THEN 0 ELSE 1 END
34        SELECT @table_id = OBJECT_ID(@table_qualifier + '.' + @table_owner + '.' + @table_name)
35        SELECT @allow_wide_dol = CASE status4 & 524288 WHEN 0 THEN 0 ELSE 1 END FROM master.dbo.sysdatabases WHERE dbid = @dbid
36    
37        IF @use_table_qualifier = 1
38        BEGIN
39            DECLARE @command VARCHAR(16384)
40            SELECT @command = 'SELECT @table_lock_scheme = CASE WHEN sysstat2 & 57344 IN (16384, 32768) THEN 1 ELSE 0 END FROM ' + @table_qualifier + '..sysobjects WHERE id = @table_id'
41            EXECUTE (@command)
42            SELECT @command = 'SELECT @max_rowlen = maxlen FROM ' + @table_qualifier + '..sysindexes WHERE id = @table_id AND indid IN (0, 1)'
43            EXECUTE (@command)
44            SELECT @command = 'SELECT @table_compressed = CASE WHEN (sysstat3 & 16384 = 16384) THEN 1 ELSE 0 END FROM ' + @table_qualifier + '..sysobjects WHERE id = @table_id'
45            EXECUTE (@command)
46            SELECT @command = '
47            SELECT @number_of_columns = MAX(value) FROM
48                (SELECT COUNT(*) AS value FROM ' + @table_qualifier + '..syscolumns WHERE id = @table_id
49                UNION SELECT NODC_GET_DROPCOL_ATTR(@dbid, @table_id, 1, ''numcols'') AS value
50                ) AS column_counts'
51            EXECUTE (@command)
52            SELECT @number_of_columns, @table_qualifier
53                , @table_compressed
54            SELECT @command = '
55            SELECT
56                COLID,
57                COLUMN_NAME,
58                DATA_TYPE,
59                USERTYPE,
60                COLUMN_SIZE,
61                PRECISION,
62                SCALE,
63                NULLABLE,
64                COLUMN_DEF,
65                IDENTITY_COL,
66                @table_lock_scheme AS TABLE_LOCK_SCHEME,
67                @@maxpagesize AS PAGESIZE
68                ,@allow_wide_dol AS ALLOW_WIDE_DOL,
69                @max_rowlen AS MAX_ROWLEN,
70                COL_OFFSET,
71                TRUNCATE_VARBINARY_ZEROS,
72                IS_INROWLOB,
73                INROWLOB_LEN,
74                ENCRYPTED_COL
75            FROM
76                (SELECT 
77                    colid AS COLID, 
78                    name AS COLUMN_NAME,
79                    DATA_TYPE = 
80                        CASE type
81                            WHEN 189 THEN 187
82                            WHEN 190 THEN 188
83                            ELSE type
84                        END,
85                    usertype AS USERTYPE,
86                    length AS COLUMN_SIZE,
87                    ISNULL(prec, 0) AS PRECISION,
88                    ISNULL(scale, 0) AS SCALE,
89                    NULLABLE = 
90                        CASE status & 0x08
91                            WHEN 0x08 THEN 1
92                            ELSE 0
93                        END,
94                    ' + @table_qualifier + '.dbo.sp_drv_getcomment(cdefault) AS COLUMN_DEF,
95                    IDENTITY_COL = 
96                        CASE status & 0x80
97                            WHEN 0x80 THEN 1
98                            ELSE 0
99                        END
100                   ,offset AS COL_OFFSET,
101                   TRUNCATE_VARBINARY_ZEROS = 
102                       CASE 
103                           WHEN (status2 & 2097152) > 0 THEN 0
104                           ELSE 1
105                       END,
106                   IS_INROWLOB = 
107                       CASE 
108                           WHEN (status2 & 262144) > 0 THEN 1
109                           ELSE 0
110                       END,
111                   inrowlen AS INROWLOB_LEN,
112                   ENCRYPTED_COL =
113                       CASE WHEN status2 & 0x80 > 0
114                           THEN 1
115                           ELSE 0
116                       END
117               FROM 
118                   ' + @table_qualifier + '..syscolumns 
119               WHERE 
120                   id = @table_id
121               UNION
122               SELECT 
123                   colid AS COLID, 
124                   '' '' AS COLUMN_NAME,
125                   DATA_TYPE = 
126                       CASE type
127                           WHEN 135 THEN 225
128                           WHEN 155 THEN 225
129                           WHEN 189 THEN 187
130                           WHEN 190 THEN 188
131                           ELSE type
132                       END,
133                   USERTYPE = 
134                       CASE type
135                           WHEN 135 THEN 34
136                           WHEN 155 THEN 35
137                           ELSE 0
138                       END,
139                   length AS COLUMN_SIZE,
140                   precision AS PRECISION,
141                   0 AS SCALE,
142                   nullable AS NULLABLE,
143                   COLUMN_DEF = 
144                       CASE 
145                           WHEN nullable = 1 THEN NULL
146                           WHEN type IN (49, 58, 123) THEN ''2000-01-01''  --TDS_DATE, TDS_SHORTDATE, TDS_DATEN
147                           WHEN type IN (51, 147, 188, 190) THEN ''01:01:01'' --TDS_TIME, TDS_TIMEN, TDS_BIGTIMEN, TDS_BIGTIMEN(ALT)
148                           WHEN type IN (61, 111, 187, 189) THEN ''2000-01-01 01:01:01'' --TDS_DATETIME, TDS_DATETIMN, TDS_BIGDATETIMEN, TDS_BIGDATETIMEN(ALT)
149                           ELSE ''0''
150                           END,
151                   0 AS IDENTITY_COL,
152                   offset AS COL_OFFSET,
153                   0 AS TRUNCATE_VARBINARY_ZEROS,
154                   0 AS IS_INROWLOB,
155                   0 AS INROWLOB_LEN,
156                   0 AS ENCRYPTED_COL
157               FROM 
158                   (SELECT  
159                       colid,
160                       offset,
161                       nullable = 
162                           CASE 
163                               WHEN offset < 0 THEN 1
164                               ELSE 0
165                           END,
166                       NODC_GET_DROPCOL_ATTR(@dbid, @table_id, colid, ''type'') AS type,
167                       NODC_GET_DROPCOL_ATTR(@dbid, @table_id, colid, ''length'') AS length,
168                       NODC_GET_DROPCOL_ATTR(@dbid, @table_id, colid, ''precision'') AS precision
169                   FROM 
170                       (SELECT 
171                           number AS colid,
172                           NODC_GET_DROPCOL_ATTR(@dbid, @table_id, number, ''offset'') AS offset
173                       FROM
174                           (SELECT
175                               d0 + d1 + d2 + d3 + d4 + d5 + d6 + d7 + d8 + d9 + dA AS number
176                           FROM
177                               (SELECT 0 UNION SELECT 1) AS tp0(d0), (SELECT 0 UNION SELECT 2) AS tp1(d1), (SELECT 0 UNION SELECT 4) AS tp2(d2), (SELECT 0 UNION SELECT 8) AS tp3(d3), (SELECT 0 UNION SELECT 16) AS tp4(d4), (SELECT 0 UNION SELECT 32) AS tp5(d5), (SELECT 0 UNION SELECT 64) AS tp6(d6), (SELECT 0 UNION SELECT 128) AS tp7(d7), (SELECT 0 UNION SELECT 256) AS tp8(d8), (SELECT 0 UNION SELECT 512) AS tp9(d9), (SELECT 0 UNION SELECT 1024) AS tpA(dA)
178                           ) AS numbers 
179                       WHERE 
180                           number BETWEEN 1 AND @number_of_columns AND 
181                           number NOT IN (SELECT colid FROM ' + @table_qualifier + '..syscolumns WHERE id = @table_id)) AS COLUMNOFFSETS) AS DROPEDCOLUMNS
182               ) AS COLUMNS
183               
184           ORDER BY
185               COLID'
186           EXECUTE (@command)
187       END
188       ELSE
189       BEGIN
190           SELECT @table_lock_scheme = CASE WHEN sysstat2 & 57344 IN (16384, 32768) THEN 1 ELSE 0 END FROM sysobjects WHERE id = @table_id
191           SELECT @max_rowlen = maxlen FROM sysindexes WHERE id = @table_id AND indid IN (0, 1)
192           SELECT @table_compressed = CASE WHEN (sysstat3 & 16384 = 16384) THEN 1 ELSE 0 END FROM sysobjects WHERE id = @table_id
193           SELECT @number_of_columns = MAX(value) FROM
194               (SELECT COUNT(*) AS value FROM syscolumns WHERE id = @table_id
195               UNION SELECT NODC_GET_DROPCOL_ATTR(@dbid, @table_id, 1, 'numcols') AS value
196               ) AS column_counts
197           SELECT @number_of_columns, @table_qualifier
198               , @table_compressed
199           SELECT
200               COLID,
201               COLUMN_NAME,
202               DATA_TYPE,
203               USERTYPE,
204               COLUMN_SIZE,
205               PRECISION,
206               SCALE,
207               NULLABLE,
208               COLUMN_DEF,
209               IDENTITY_COL,
210               @table_lock_scheme AS TABLE_LOCK_SCHEME,
211               @@maxpagesize AS PAGESIZE
212               , @allow_wide_dol AS ALLOW_WIDE_DOL,
213               @max_rowlen AS MAX_ROWLEN,
214               COL_OFFSET,
215               TRUNCATE_VARBINARY_ZEROS,
216               IS_INROWLOB,
217               INROWLOB_LEN,
218               ENCRYPTED_COL
219           FROM
220               (SELECT
221                   colid AS COLID,
222                   name AS COLUMN_NAME,
223                   DATA_TYPE =
224                   CASE type
225                       WHEN 189 THEN 187
226                       WHEN 190 THEN 188
227                       ELSE type
228                   END,
229                   usertype AS USERTYPE,
230                   length AS COLUMN_SIZE,
231                   ISNULL(prec, 0) AS PRECISION,
232                   ISNULL(scale, 0) AS SCALE,
233                   NULLABLE =
234                   CASE status & 0x08
235                       WHEN 0x08 THEN 1
236                       ELSE 0
237                   END,
238                   dbo.sp_drv_getcomment(cdefault) AS COLUMN_DEF,
239                   IDENTITY_COL =
240                   CASE status & 0x80
241                       WHEN 0x80 THEN 1
242                       ELSE 0
243                   END
244                   , offset AS COL_OFFSET,
245                   TRUNCATE_VARBINARY_ZEROS =
246                   CASE
247                       WHEN (status2 & 2097152) > 0 THEN 0
248                       ELSE 1
249                   END,
250                   IS_INROWLOB =
251                   CASE
252                       WHEN (status2 & 262144) > 0 THEN 1
253                       ELSE 0
254                   END,
255                   inrowlen AS INROWLOB_LEN,
256                   ENCRYPTED_COL =
257                   CASE WHEN status2 & 0x80 > 0
258                       THEN 1
259                       ELSE 0
260                   END
261               FROM
262                   syscolumns
263               WHERE
264                   id = @table_id
265               UNION
266               SELECT
267                   colid AS COLID,
268                   ' ' AS COLUMN_NAME,
269                   DATA_TYPE =
270                   CASE type
271                       WHEN 135 THEN 225
272                       WHEN 155 THEN 225
273                       WHEN 189 THEN 187
274                       WHEN 190 THEN 188
275                       ELSE type
276                   END,
277                   USERTYPE =
278                   CASE type
279                       WHEN 135 THEN 34
280                       WHEN 155 THEN 35
281                       ELSE 0
282                   END,
283                   length AS COLUMN_SIZE,
284                   precision AS PRECISION,
285                   0 AS SCALE,
286                   nullable AS NULLABLE,
287                   COLUMN_DEF =
288                   CASE
289                       WHEN nullable = 1 THEN NULL
290                       WHEN type IN (49, 58, 123) THEN '2000-01-01' --TDS_DATE, TDS_SHORTDATE, TDS_DATEN
291                       WHEN type IN (51, 147, 188, 190) THEN '01:01:01' --TDS_TIME, TDS_TIMEN, TDS_BIGTIMEN, TDS_BIGTIMEN(ALT)
292                       WHEN type IN (61, 111, 187, 189) THEN '2000-01-01 01:01:01' --TDS_DATETIME, TDS_DATETIMN, TDS_BIGDATETIMEN, TDS_BIGDATETIMEN(ALT)
293                       ELSE '0'
294                   END,
295                   0 AS IDENTITY_COL,
296                   offset AS COL_OFFSET,
297                   0 AS TRUNCATE_VARBINARY_ZEROS,
298                   0 AS IS_INROWLOB,
299                   0 AS INROWLOB_LEN,
300                   0 AS ENCRYPTED_COL
301               FROM
302                   (SELECT
303                       colid,
304                       offset,
305                       nullable =
306                       CASE
307                           WHEN offset < 0 THEN 1
308                           ELSE 0
309                       END,
310                       NODC_GET_DROPCOL_ATTR(@dbid, @table_id, colid, 'type') AS type,
311                       NODC_GET_DROPCOL_ATTR(@dbid, @table_id, colid, 'length') AS length,
312                       NODC_GET_DROPCOL_ATTR(@dbid, @table_id, colid, 'precision') AS precision
313                   FROM
314                       (SELECT
315                           number AS colid,
316                           NODC_GET_DROPCOL_ATTR(@dbid, @table_id, number, 'offset') AS offset
317                       FROM
318                           (SELECT
319                               d0 + d1 + d2 + d3 + d4 + d5 + d6 + d7 + d8 + d9 + dA AS number
320                           FROM
321                               (SELECT 0 UNION SELECT 1) AS tp0(d0), (SELECT 0 UNION SELECT 2) AS tp1(d1), (SELECT 0 UNION SELECT 4) AS tp2(d2), (SELECT 0 UNION SELECT 8) AS tp3(d3), (SELECT 0 UNION SELECT 16) AS tp4(d4), (SELECT 0 UNION SELECT 32) AS tp5(d5), (SELECT 0 UNION SELECT 64) AS tp6(d6), (SELECT 0 UNION SELECT 128) AS tp7(d7), (SELECT 0 UNION SELECT 256) AS tp8(d8), (SELECT 0 UNION SELECT 512) AS tp9(d9), (SELECT 0 UNION SELECT 1024) AS tpA(dA)
322                           ) AS numbers
323                       WHERE
324                           number BETWEEN 1 AND @number_of_columns AND
325                           number NOT IN (SELECT colid FROM syscolumns WHERE id = @table_id)) AS COLUMNOFFSETS) AS DROPEDCOLUMNS
326               ) AS COLUMNS
327   
328           ORDER BY
329               COLID
330       END
331       RETURN (0)
332   


exec sp_procxmode 'sp_drv_bcpmetadata', 'AnyMode'
go

Grant Execute on sp_drv_bcpmetadata to public
go
RESULT SETS
sp_drv_bcpmetadata_rset_003
sp_drv_bcpmetadata_rset_001
sp_drv_bcpmetadata_rset_002

DEFECTS
 QCAR 6 Cartesian product between tables tempdb..tp0_D2 tp0 and [tempdb..tpA_D12 tpA, tempdb..tp2_D4 tp2, tempdb..tp8_D10 tp8,... 320
 QJWI 5 Join or Sarg Without Index 325
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 35
 QTYP 4 Comparison type mismatch smallint = int 35
 TNOI 4 Table with no index tempdb..COLUMNOFFSETS_D14 tempdb..COLUMNOFFSETS_D14
 TNOI 4 Table with no index tempdb..COLUMNS_D16 tempdb..COLUMNS_D16
 TNOI 4 Table with no index tempdb..DROPEDCOLUMNS_D15 tempdb..DROPEDCOLUMNS_D15
 TNOI 4 Table with no index tempdb..numbers_D13 tempdb..numbers_D13
 TNOI 4 Table with no index tempdb..tp0_D2 tempdb..tp0_D2
 TNOI 4 Table with no index tempdb..tp1_D3 tempdb..tp1_D3
 TNOI 4 Table with no index tempdb..tp2_D4 tempdb..tp2_D4
 TNOI 4 Table with no index tempdb..tp3_D5 tempdb..tp3_D5
 TNOI 4 Table with no index tempdb..tp4_D6 tempdb..tp4_D6
 TNOI 4 Table with no index tempdb..tp5_D7 tempdb..tp5_D7
 TNOI 4 Table with no index tempdb..tp6_D8 tempdb..tp6_D8
 TNOI 4 Table with no index tempdb..tp7_D9 tempdb..tp7_D9
 TNOI 4 Table with no index tempdb..tp8_D10 tempdb..tp8_D10
 TNOI 4 Table with no index tempdb..tp9_D11 tempdb..tp9_D11
 TNOI 4 Table with no index tempdb..tpA_D12 tempdb..tpA_D12
 VRUN 4 Variable is read and not initialized @number_of_columns 52
 VRUN 4 Variable is read and not initialized @table_compressed 53
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 190
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 191
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 192
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 194
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 264
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 325
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 234
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 235
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 240
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 241
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 257
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 2
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_drv_bcpmetadata  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MUCO 3 Useless Code Useless Brackets in create proc 2
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 331
 QCRS 3 Conditional Result Set 52
 QCRS 3 Conditional Result Set 197
 QCRS 3 Conditional Result Set 199
 QGWO 3 Group by/Distinct/Union without order by 194
 QGWO 3 Group by/Distinct/Union without order by 220
 QGWO 3 Group by/Distinct/Union without order by 321
 QMTB 3 From clause with many tables 11 320
 QNAM 3 Select expression has no name @number_of_columns 52
 QNAM 3 Select expression has no name @table_qualifier 52
 QNAM 3 Select expression has no name @table_compressed 53
 QNAM 3 Select expression has no name @number_of_columns 197
 QNAM 3 Select expression has no name @table_qualifier 197
 QNAM 3 Select expression has no name @table_compressed 198
 QNUA 3 Should use Alias: Column d0 should use alias tp0 319
 QNUA 3 Should use Alias: Column d1 should use alias tp1 319
 QNUA 3 Should use Alias: Column d2 should use alias tp2 319
 QNUA 3 Should use Alias: Column d3 should use alias tp3 319
 QNUA 3 Should use Alias: Column d4 should use alias tp4 319
 QNUA 3 Should use Alias: Column d5 should use alias tp5 319
 QNUA 3 Should use Alias: Column d6 should use alias tp6 319
 QNUA 3 Should use Alias: Column d7 should use alias tp7 319
 QNUA 3 Should use Alias: Column d8 should use alias tp8 319
 QNUA 3 Should use Alias: Column d9 should use alias tp9 319
 QNUA 3 Should use Alias: Column dA should use alias tpA 319
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
194
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
264
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
325
 QUNI 3 Check Use of 'union' vs 'union all' 194
 QUNI 3 Check Use of 'union' vs 'union all' 220
 QUNI 3 Check Use of 'union' vs 'union all' 321
 MDRV 2 Derived Table Marker 194
 MDRV 2 Derived Table Marker 220
 MDRV 2 Derived Table Marker 302
 MDRV 2 Derived Table Marker 314
 MDRV 2 Derived Table Marker 318
 MDRV 2 Derived Table Marker 321
 MDYS 2 Dynamic SQL Marker 41
 MDYS 2 Dynamic SQL Marker 43
 MDYS 2 Dynamic SQL Marker 45
 MDYS 2 Dynamic SQL Marker 51
 MDYS 2 Dynamic SQL Marker 186
 MRST 2 Result Set Marker 52
 MRST 2 Result Set Marker 197
 MRST 2 Result Set Marker 199
 MSUB 2 Subquery Marker 325
 MTR1 2 Metrics: Comments Ratio Comments: 3% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 5dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 86 2

DATA PROPAGATION detailed
ColumnWritten To
@table_qualifiersp_drv_bcpmetadata_rset_001.sqlbNoName33 sp_drv_bcpmetadata_rset_003.COLUMN_NAME

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..syscolumns  
writes table sybsystemprocs..sp_drv_bcpmetadata_rset_002 
reads table sybsystemprocs..sysindexes  
writes table sybsystemprocs..sp_drv_bcpmetadata_rset_001 
reads table sybsystemprocs..sysobjects  
reads table master..sysdatabases (1)  
writes table sybsystemprocs..sp_drv_bcpmetadata_rset_003