DatabaseProcApplicationCreatedLinks
sybsystemprocssp_special_columns  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /*	10.0	1.0	9 JUL 93	sproc/src/special_columns */
4     
5     /* 
6     ** Messages for "sp_special_columns"
7     **
8     ** 17863, "There is no table named %1! in the current database."
9     ** 18039, "Table qualifier must be name of current database."
10    ** 18042, "Illegal value for 'col_type' argument. Legal values are 'V' or 'R'."
11    **
12    */
13    
14    create or replace procedure sp_special_columns(
15        @table_name varchar(257),
16        @table_owner varchar(257) = null,
17        @table_qualifier varchar(257) = null,
18        @col_type char(1) = 'R')
19    as
20        declare @indid int
21        declare @table_id int
22        declare @dbname char(255)
23        declare @full_table_name char(520)
24    
25        if @@trancount = 0
26        begin
27            set chained off
28        end
29    
30        set transaction isolation level 1
31    
32        /* get database name */
33        select @dbname = db_name()
34    
35        /* we don't want a temp table unless we're in tempdb */
36        if @table_name like "#%" and @dbname != db_name(tempdb_id())
37        begin
38            /* 17863, "There is no table named %1! in the current database." */
39            raiserror 17863, @table_name
40            return (1)
41        end
42    
43        if @table_qualifier is not null
44        begin
45            if @dbname != @table_qualifier
46            begin
47                /* 18039, "Table qualifier must be name of current database." */
48                raiserror 18039
49                return (1)
50            end
51        end
52    
53        if @table_owner is null
54        begin /* if unqualified table name */
55            select @full_table_name = @table_name
56        end
57        else
58        begin /* qualified table name */
59            select @full_table_name = @table_owner + '.' + @table_name
60        end
61    
62        /* get object ID */
63        select @table_id = object_id(@full_table_name)
64    
65        if @col_type = 'V'
66        begin /* if ROWVER, just run that query */
67            select
68                scope = convert(smallint, 0),
69                column_name = c.name,
70                data_type = d.data_type + convert(smallint,
71                isnull(d.aux,
72                    ascii(substring("666AAA@@@CB??GG",
73                            2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
74                    - 60)),
75                type_name = t.name,
76                "precision" = isnull(d.data_precision,
77                    convert(int, c.length))
78                + isnull(d.aux, convert(int,
79                    ascii(substring("???AAAFFFCKFOLS",
80                            2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
81                    - 60)),
82                length = isnull(d.length, convert(int, c.length))
83                + convert(int,
84                isnull(d.aux,
85                    ascii(substring("AAA<BB<DDDHJSPP",
86                            2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
87                    - 64)),
88                scale = d.numeric_scale + convert(smallint,
89                isnull(d.aux,
90                    ascii(substring("<<<<<<<<<<<<<<?",
91                            2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
92                    - 60))
93            from
94                systypes t, syscolumns c, sybsystemprocs.dbo.spt_datatype_info d
95            where
96                c.id = @table_id
97                and c.type = d.ss_dtype
98                and c.usertype = 80 /* TIMESTAMP */
99                and t.usertype = 80 /* TIMESTAMP */
100           return (0)
101       end
102   
103       if @col_type != 'R'
104       begin
105           /* 18042, "Illegal value for 'col_type' argument. Legal values are 'V' or 'R'." */
106   
107           raiserror 18042
108           return (1)
109       end
110   
111       /* An identity column is the most optimal unique identifier */
112       if exists (select colid from syscolumns
113               where id = @table_id and (status & 128) = 128)
114       begin
115           select
116               scope = convert(smallint, 0),
117               column_name = c.name,
118               data_type = d.data_type + convert(smallint,
119               isnull(d.aux,
120                   ascii(substring("666AAA@@@CB??GG",
121                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
122                   - 60)),
123               type_name = rtrim(substring(d.type_name,
124                       1 + isnull(d.aux,
125                           ascii(substring("III<<<MMMI<<A<A",
126                                   2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
127                           - 60), 18)),
128               "precision" = isnull(d.data_precision, convert(int, c.length))
129               + isnull(d.aux, convert(int,
130                   ascii(substring("???AAAFFFCKFOLS",
131                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
132                   - 60)),
133               length = isnull(d.length, convert(int, c.length))
134               + convert(int, isnull(d.aux,
135                   ascii(substring("AAA<BB<DDDHJSPP",
136                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
137                   - 64)),
138               scale = d.numeric_scale + convert(smallint,
139               isnull(d.aux,
140                   ascii(substring("<<<<<<<<<<<<<<?",
141                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
142                   - 60))
143           from
144               syscolumns c,
145               sybsystemprocs.dbo.spt_datatype_info d,
146               systypes t
147           where
148               c.id = @table_id
149               and (c.status & 128) = 128
150               and c.type = d.ss_dtype
151               and c.usertype *= t.usertype
152           return (0)
153       end
154   
155       /* ROWID, now find the id of the 'best' index for this table */
156   
157       select @indid = (
158               select min(indid)
159               from sysindexes
160               where
161                   status & 2 = 2 /* if unique index */
162                   and id = @table_id
163                   and indid > 0) /* eliminate table row */
164   
165       select
166           scope = convert(smallint, 0),
167           column_name = index_col(@full_table_name, indid, c.colid),
168           data_type = d.data_type + convert(smallint,
169           isnull(d.aux,
170               ascii(substring("666AAA@@@CB??GG",
171                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
172               - 60)),
173           type_name = rtrim(substring(d.type_name,
174                   1 + isnull(d.aux,
175                       ascii(substring("III<<<MMMI<<A<A",
176                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
177                       - 60), 18)),
178           "precision" = isnull(d.data_precision, convert(int, c2.length))
179           + isnull(d.aux, convert(int,
180               ascii(substring("???AAAFFFCKFOLS",
181                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
182               - 60)),
183           length = isnull(d.length, convert(int, c2.length))
184           + convert(int, isnull(d.aux,
185               ascii(substring("AAA<BB<DDDHJSPP",
186                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
187               - 64)),
188           scale = d.numeric_scale + convert(smallint,
189           isnull(d.aux,
190               ascii(substring("<<<<<<<<<<<<<<?",
191                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
192               - 60))
193       from
194           sysindexes x,
195           syscolumns c,
196           sybsystemprocs.dbo.spt_datatype_info d,
197           systypes t,
198           syscolumns c2 /* self-join to generate list of index
199       ** columns and to extract datatype names */
200       where
201           x.id = @table_id
202           and c2.name = index_col(@full_table_name, @indid, c.colid)
203           and c2.id = x.id
204           and c.id = x.id
205           and c.colid < keycnt + (x.status & 16) / 16
206           and x.indid = @indid
207           and c2.type = d.ss_dtype
208           and c2.usertype *= t.usertype
209       return (0)
210   


exec sp_procxmode 'sp_special_columns', 'AnyMode'
go

Grant Execute on sp_special_columns to public
go
RESULT SETS
sp_special_columns_rset_003
sp_special_columns_rset_002
sp_special_columns_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 97
 QJWI 5 Join or Sarg Without Index 150
 QJWI 5 Join or Sarg Without Index 151
 QJWI 5 Join or Sarg Without Index 207
 QJWI 5 Join or Sarg Without Index 208
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..systypes t and [sybsystemprocs..syscolumns c, sybsystemprocs..spt_dat... 67
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid}
Uncovered: [id, number]
202
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 98
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 99
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 163
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 205
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 206
 QTYP 4 Comparison type mismatch smallint = int 206
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info sybsystemprocs..spt_datatype_info
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 96
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 113
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 148
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 162
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 201
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 203
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 203
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 204
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 204
 MGTP 3 Grant to public sybsystemprocs..sp_special_columns  
 MGTP 3 Grant to public sybsystemprocs..spt_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MLCH 3 Char type with length>30 char(255) 22
 MLCH 3 Char type with length>30 char(520) 23
 MUCO 3 Useless Code Useless Brackets in create proc 14
 MUCO 3 Useless Code Useless Brackets 40
 MUCO 3 Useless Code Useless Brackets 49
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 209
 QCRS 3 Conditional Result Set 67
 QCRS 3 Conditional Result Set 115
 QISO 3 Set isolation level 30
 QNAJ 3 Not using ANSI Inner Join 93
 QNAO 3 Not using ANSI Outer Join 143
 QNAO 3 Not using ANSI Outer Join 193
 QNUA 3 Should use Alias: Column indid should use alias x 167
 QNUA 3 Should use Alias: Column keycnt should use alias x 205
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
96
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
113
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
148
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
Uncovered: [number, colid]
203
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
Uncovered: [number, colid]
204
 QTJ1 3 Table only appears in inner join clause 146
 QTJ1 3 Table only appears in inner join clause 197
 MRST 2 Result Set Marker 67
 MRST 2 Result Set Marker 115
 MRST 2 Result Set Marker 165
 MSUB 2 Subquery Marker 112
 MSUB 2 Subquery Marker 157
 MTR1 2 Metrics: Comments Ratio Comments: 17% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 13dec - 6exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 75 14
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, sdi=sybsystemprocs..spt_datatype_info} 0 67

DATA PROPAGATION detailed
ColumnWritten To
@table_namesp_special_columns_rset_001.column_name
@table_ownersp_special_columns_rset_001.column_name

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_special_columns_rset_002 
writes table sybsystemprocs..sp_special_columns_rset_003 
reads table sybsystemprocs..systypes  
reads table sybsystemprocs..spt_datatype_info  
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..sysindexes  
writes table sybsystemprocs..sp_special_columns_rset_001