Database	Proc	Application	Created	Links
sybsystemprocs	sp_drv_bcpmetadata		31 Aug 14	Defects Dependencies

1     /** SECTION END: CLEANUP **/
2     
3     
4     CREATE PROCEDURE sp_drv_bcpmetadata(
5         @table_name varchar(771),
6         @table_owner varchar(32) = null,
7         @table_qualifier varchar(32) = null
8     )
9     AS
10    
11    
12        declare @msg varchar(250)
13        declare @full_table_name varchar(1542)
14        declare @table_id int
15        declare @sysstat2 int
16        declare @table_lock_scheme bit
17        declare @allow_wide_dol bit
18        declare @checktemptable int
19        declare @dbnameconflict int
20        declare @max_rowlen int
21    
22        /* this will make sure that all rows are sent even if
23        ** the client "set rowcount" is differect
24        */
25    
26        set rowcount 0
27    
28    
29        if @table_qualifier is not null
30        begin
31            if db_name() != @table_qualifier
32            begin
33                select @dbnameconflict = 1
34            end
35        end
36    
37        SELECT @checktemptable = charindex('#', @table_name)
38        /*
39        **  if its a #temp table, assign @table_qualifier to the
40        **  the temp db assigned to the user
41        */
42    
43        if @checktemptable = 1
44        begin
45            select @table_qualifier = db_name(@@tempdbid)
46        end
47    
48        if @table_owner is null
49        begin /* If unqualified table name */
50            SELECT @full_table_name = @table_qualifier + '..' + @table_name
51            select @table_owner = '%'
52        end
53        else
54        begin /* Qualified table name */
55            SELECT @full_table_name = @table_qualifier + '.' + @table_owner + '.' + @table_name
56        end
57    
58        select @table_id = object_id(@full_table_name)
59        if @checktemptable = 1 or @dbnameconflict = 1
60        begin
61            declare @cmd varchar(1500)
62            select @cmd = 'SELECT @sysstat2 = (sysstat2 & 57344) from ' + @table_qualifier + '..sysobjects where id = @table_id'
63            execute (@cmd)
64            select @cmd = 'select @max_rowlen = maxlen from ' + @table_qualifier + '..sysindexes  where id = @table_id and indid in (0,1)'
65            execute (@cmd)
66        end
67        else
68        begin
69            SELECT @sysstat2 = (sysstat2 & 57344) from sysobjects where id = @table_id
70            select @max_rowlen = maxlen from sysindexes where id = @table_id and indid in (0, 1)
71        end
72    
73        SELECT @allow_wide_dol = CASE WHEN ((status4 & 524288) != 0) THEN 1 ELSE 0 END FROM master.dbo.sysdatabases WHERE dbid = DB_ID(@table_qualifier)
74    
75        if (@sysstat2 = 8192 or @sysstat2 = 0)
76        begin
77            /* Lock scheme is Allpages */
78            select @table_lock_scheme = 0
79        end
80        if (@sysstat2 = 16384 or @sysstat2 = 32768)
81        begin
82            /* Lock scheme is Data only */
83            select @table_lock_scheme = 1
84        end
85    
86    
87    
88        if (@table_id != 0)
89        begin
90            if @checktemptable = 1 or @dbnameconflict = 1
91            begin
92                select @cmd = 'select count(*),@table_qualifier from ' + @table_qualifier + '..syscolumns where id=@table_id '
93                select @cmd = @cmd + ' SELECT 
94                COLID=colid,
95                COLUMN_NAME=name, 
96                DATA_TYPE=(case when type=189 then 187
97                            when type=190 then 188
98                            else
99                                type
100                           end),
101               USERTYPE=usertype,
102               COLUMN_SIZE=length,
103               PRECISION=isnull(prec,0),
104               SCALE=isnull(scale,0),
105               NULLABLE=convert(bit, (status & 8)),
106               COLUMN_DEF=(select text from syscomments where id=c.cdefault),
107               IDENTITY_COL=convert(bit, (status & 0x80)),
108               TABLE_LOCK_SCHEME=@table_lock_scheme,
109               PAGESIZE=@@maxpagesize, 
110               ALLOW_WIDE_DOL=@allow_wide_dol,
111               MAX_ROWLEN = @max_rowlen,
112               COL_OFFSET = offset,
113               TRUNCATE_VARBINARY_ZEROS = case when (status2 & 2097152) > 0 then 0
114                                               else 1
115                                          end,
116               IS_INROWLOB = case when (status2 & 262144) > 0 then 1
117                                  else 0
118                             end,
119               INROWLOB_LEN = inrowlen
120           FROM ' + @table_qualifier + '..syscolumns c 
121           where id=@table_id
122           ORDER BY colid'
123               execute (@cmd)
124           end
125           else
126           begin
127               select count(*), @table_qualifier from syscolumns where id = @table_id
128               SELECT
129                   COLID = colid,
130                   COLUMN_NAME = name,
131                   DATA_TYPE = (case when type = 189 then 187
132                       when type = 190 then 188
133                       else
134                           type
135                   end),
136                   USERTYPE = usertype,
137                   COLUMN_SIZE = length,
138                   'PRECISION' = isnull(prec, 0),
139                   SCALE = isnull(scale, 0),
140                   NULLABLE = convert(bit, (status & 8)),
141                   COLUMN_DEF = (select text from syscomments where id = c.cdefault),
142                   IDENTITY_COL = convert(bit, (status & 0x80)),
143                   TABLE_LOCK_SCHEME = @table_lock_scheme,
144                   PAGESIZE = @@maxpagesize,
145                   ALLOW_WIDE_DOL = @allow_wide_dol,
146                   MAX_ROWLEN = @max_rowlen,
147                   COL_OFFSET = offset,
148                   TRUNCATE_VARBINARY_ZEROS = case when (status2 & 2097152) > 0 then 0
149                       else 1
150                   end,
151                   IS_INROWLOB = case when (status2 & 262144) > 0 then 1
152                       else 0
153                   end,
154                   INROWLOB_LEN = inrowlen
155               FROM syscolumns c
156               where id = @table_id
157               ORDER BY colid
158           end
159       end
160       return (0)
161   

exec sp_procxmode 'sp_drv_bcpmetadata', 'AnyMode'
go

Grant Execute on sp_drv_bcpmetadata to public
go

DEFECTS
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	73
QTYP 4 Comparison type mismatch smallint = int	73
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	131
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	132
MBLI 3 Integer Value of Binary Literal is Platform Dependant	142
MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain	4
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..syscomments
MGTP 3 Grant to public sybsystemprocs..sysindexes
MGTP 3 Grant to public sybsystemprocs..sysobjects
MUCO 3 Useless Code Useless Brackets in create proc	4
MUCO 3 Useless Code Useless Brackets	73
MUCO 3 Useless Code Useless Brackets	75
MUCO 3 Useless Code Useless Brackets	80
MUCO 3 Useless Code Useless Brackets	88
MUCO 3 Useless Code Useless Brackets	131
MUCO 3 Useless Code Useless Brackets	160
QCRS 3 Conditional Result Set	127
QCRS 3 Conditional Result Set	128
QNAM 3 Select expression has no name @table_qualifier	127
QNAM 3 Select expression has no name count(*)	127
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id}	127
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id}	156
VUNU 3 Variable is not used @msg	12
MDYS 2 Dynamic SQL Marker	63
MDYS 2 Dynamic SQL Marker	65
MDYS 2 Dynamic SQL Marker	123
MRST 2 Result Set Marker	127
MRST 2 Result Set Marker	128
MSUC 2 Correlated Subquery Marker	141
MTR1 2 Metrics: Comments Ratio Comments: 6%	4
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 14dec - 1exi + 2	4
MTR3 2 Metrics: Query Complexity Complexity: 74	4
PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, c2=sybsystemprocs..syscolumns} 0	141

DEPENDENCIES

PROCS AND TABLES USED reads table sybsystemprocs..syscolumns   reads table sybsystemprocs..syscomments   reads table sybsystemprocs..sysindexes   reads table master..sysdatabases (1)   reads table sybsystemprocs..sysobjects