| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_special_columns | | 31 Aug 14 | Defects 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 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 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..systypes reads table sybsystemprocs..sysindexes reads table sybsystemprocs..syscolumns reads table sybsystemprocs..spt_datatype_info |
