| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_checknames | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 /* 4.8 1.1 06/14/90 sproc/src/checknames */ 4 5 6 create procedure sp_checknames 7 @mode varchar(20) = NULL /* mode of operation; e.g. 'silent' */ 8 as 9 10 declare @msilent int /* set to 1 if 'silent' mode is on */ 11 declare @ret_val int /* set to 1 if we find funny char */ 12 declare @codepoint tinyint 13 declare @dbname varchar(30) /* holds database name */ 14 declare @msg varchar(255) /* used for messages to user */ 15 declare @pat varchar(255) /* holds the pattern to search for */ 16 17 18 if @@trancount = 0 19 begin 20 set chained off 21 end 22 23 set transaction isolation level 1 24 25 set nocount on 26 27 if (@mode like '%help%') 28 begin 29 print 'sp_checknames is used to search for non 7-bit ASCII characters' 30 print 'in several important columns of system tables. The following' 31 print 'columns are searched:' 32 print ' ' 33 print ' In "master":' 34 print ' sysdatabases.name' 35 print ' sysdevices.name' 36 print ' syslogins.name' 37 print ' syslogins.dbname' 38 print ' syslogins.password' 39 print ' sysremotelogins.remoteusername' 40 print ' sysservers.srvname' 41 print ' sysservers.srvnetname' 42 print ' ' 43 print ' In all databases:' 44 print ' syscolumns.name' 45 print ' sysindexes.name' 46 print ' sysobjects.name' 47 print ' syssegments.name' 48 print ' systypes.name' 49 print ' sysusers.name' 50 print ' ' 51 52 return (0) 53 end 54 55 /* 56 ** First, initialize return value, and set up mode variables: 57 */ 58 select @ret_val = 0 59 60 if (@mode like '%silent%') 61 select @msilent = 1 62 else 63 select @msilent = 0 64 65 66 /* 67 ** Now, initialize the pattern string we will search for: 68 ** @pat contains the pattern "NOT IN THE RANGE OF all 7-bit ASCII 69 ** characters, except the right bracket "]". (As of release 4.9.1, 70 ** it is not possible to represent the right bracket in the non- 71 ** inclusive matching pattern.) 72 */ 73 select @pat = '%[^- !"#$%&''()*+,./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\^_`abcdefghijklmnopqrstuvwxyz{|}~' 74 75 select @codepoint = 0 76 while (@codepoint < 32) 77 begin 78 select @pat = @pat + char(@codepoint) 79 select @codepoint = @codepoint + 1 80 end 81 82 select @pat = @pat + char(127) + ']%' 83 84 85 /* 86 ** Get the database name we are in: 87 */ 88 select @dbname = db_name() 89 90 if (@msilent = 0) 91 begin 92 print ' ' 93 select @msg = 'Looking for non 7-bit ASCII characters in the system ' + 94 'tables of database: "' + @dbname + '"' 95 print @msg 96 print ' ' 97 end 98 99 100 /* 101 ** Look through these only if in the master database: 102 */ 103 if (@dbname = 'master') 104 begin 105 if exists (select name from master.dbo.sysdatabases 106 where name like @pat) 107 begin 108 if (@msilent = 1) 109 return (1) 110 111 select @ret_val = 1 112 print ' ' 113 print '===============================================================' 114 print 'Table.Column name: "sysdatabases.name"' 115 print ' ' 116 print 'The following database names contain non 7-bit ASCII characters.' 117 print 'If you wish to change their names, use "sp_renamedb":' 118 print ' ' 119 select dbid, name from master.dbo.sysdatabases 120 where name like @pat 121 end 122 123 if exists (select name from master.dbo.sysdevices 124 where name like @pat) 125 begin 126 if (@msilent = 1) 127 return (1) 128 129 select @ret_val = 1 130 print ' ' 131 print '===============================================================' 132 print 'Table.Column name: "sysdevices.name"' 133 print ' ' 134 print 'The following device names contain non 7-bit ASCII characters.' 135 print 'If you wish to change their names, use "UPDATE":' 136 print ' ' 137 select name from master.dbo.sysdevices 138 where name like @pat 139 end 140 141 if exists (select name from master.dbo.syslogins 142 where name like @pat) 143 begin 144 if (@msilent = 1) 145 return (1) 146 147 select @ret_val = 1 148 print ' ' 149 print '===============================================================' 150 print 'Table.Column name: "syslogins.name"' 151 print ' ' 152 print 'The following login names contain non 7-bit ASCII characters.' 153 print 'If you wish to change these names, use "sp_droplogin" and' 154 print '"sp_addlogin":' 155 print ' ' 156 select suid, name from master.dbo.syslogins 157 where name like @pat 158 end 159 160 if exists (select dbname from master.dbo.syslogins 161 where dbname like @pat) 162 begin 163 if (@msilent = 1) 164 return (1) 165 166 select @ret_val = 1 167 print ' ' 168 print '===============================================================' 169 print 'Table.Column name: "syslogins.dbname"' 170 print ' ' 171 print 'The following logins have default database names that contain' 172 print 'non 7-bit ASCII characters. If you wish to change them use' 173 print '"sp_defaultdb":' 174 print ' ' 175 select suid, name, dbname from master.dbo.syslogins 176 where dbname like @pat 177 end 178 179 /* 180 ** @pat contains the pattern "NOT IN THE RANGE OF all 7-bit ASCII characters, 181 ** except the right bracket ("]"). Thus, if a password contains the right 182 ** bracket character, this non-inclusive matching pattern will not work, and 183 ** sp_checknames will think the password contains non 7-bit ASCII characters. 184 */ 185 if exists (select password from master.dbo.syslogins 186 where password like @pat) 187 begin 188 if (@msilent = 1) 189 return (1) 190 191 select @ret_val = 1 192 print ' ' 193 print '===============================================================' 194 print 'Table.Column name: "syslogins.password"' 195 print ' ' 196 print 'The following logins have passwords that contain non 7-bit' 197 print 'ASCII characters. If you wish to change them use "sp_password";' 198 print 'Remember, only the sa and the login itself may examine or change' 199 print 'the syslogins.password column:' 200 print ' ' 201 select suid, name from master.dbo.syslogins 202 where password like @pat 203 end 204 205 if exists (select remoteusername from master.dbo.sysremotelogins 206 where remoteusername like @pat) 207 begin 208 if (@msilent = 1) 209 return (1) 210 211 select @ret_val = 1 212 print ' ' 213 print '===============================================================' 214 print 'Table.Column name: "sysremotelogins.remoteusername"' 215 print ' ' 216 print 'The following remote login names contain non 7-bit ASCII' 217 print 'characters. If you wish to change these names, use' 218 print '"sp_dropremotelogin" and "sp_addremotelogin":' 219 print ' ' 220 select remoteserverid, remoteusername from master.dbo.sysremotelogins 221 where remoteusername like @pat 222 end 223 224 if exists (select srvname from master.dbo.sysservers 225 where srvname like @pat) 226 begin 227 if (@msilent = 1) 228 return (1) 229 230 select @ret_val = 1 231 print ' ' 232 print '===============================================================' 233 print 'Table.Column name: "sysservers.srvname"' 234 print ' ' 235 print 'The following server names contain non 7-bit ASCII characters.' 236 print 'If you wish to change their names, use "sp_dropserver" and' 237 print '"sp_addserver":' 238 print ' ' 239 select srvid, srvname from master.dbo.sysservers 240 where srvname like @pat 241 end 242 243 if exists (select srvnetname from master.dbo.sysservers 244 where srvnetname like @pat) 245 begin 246 if (@msilent = 1) 247 return (1) 248 249 select @ret_val = 1 250 print ' ' 251 print '===============================================================' 252 print 'Table.Column name: "sysservers.srvnetname"' 253 print ' ' 254 print 'The following servers have "interface file" names that contain' 255 print 'non 7-bit ASCII characters. If you wish to change these names,' 256 print 'use "UPDATE":' 257 print ' ' 258 select srvid, srvname, srvnetname from master.dbo.sysservers 259 where srvnetname like @pat 260 end 261 262 end 263 264 265 /* 266 ** For *ALL* databases, we want to look through these: 267 */ 268 if exists (select name from dbo.syscolumns 269 where name like @pat) 270 begin 271 if (@msilent = 1) 272 return (1) 273 274 select @ret_val = 1 275 print ' ' 276 print '===============================================================' 277 print 'Table.Column name: "syscolumns.name"' 278 print ' ' 279 print 'The following column and parameter names contain non 7-bit ASCII' 280 print 'characters. If you wish to change these names, use "sp_rename":' 281 print ' ' 282 select objname = o.name, colname = c.name from dbo.syscolumns c, dbo.sysobjects o 283 where c.name like @pat and o.id = c.id 284 end 285 286 if exists (select name from dbo.sysindexes 287 where name like @pat 288 and indid > 0) 289 begin 290 if (@msilent = 1) 291 return (1) 292 293 select @ret_val = 1 294 print ' ' 295 print '===============================================================' 296 print 'Table.Column name: "sysindexes.name"' 297 print ' ' 298 print 'The following index names contain non 7-bit ASCII characters.' 299 print 'If you wish to change these names, use "UPDATE":' 300 print ' ' 301 select id, indid, name from dbo.sysindexes 302 where name like @pat 303 end 304 305 if exists (select name from dbo.sysobjects 306 where name like @pat) 307 begin 308 if (@msilent = 1) 309 return (1) 310 311 select @ret_val = 1 312 print ' ' 313 print '===============================================================' 314 print 'Table.Column name: "sysobjects.name"' 315 print ' ' 316 print 'The following object names contain non 7-bit ASCII characters.' 317 print 'If you wish to change these names, use "sp_rename":' 318 print ' ' 319 select owner = u.name, o.name from dbo.sysobjects o, dbo.sysusers u 320 where o.name like @pat and o.uid = u.uid 321 end 322 323 if exists (select name from dbo.syssegments 324 where name like @pat) 325 begin 326 if (@msilent = 1) 327 return (1) 328 329 select @ret_val = 1 330 print ' ' 331 print '===============================================================' 332 print 'Table.Column name: "syssegments.name"' 333 print ' ' 334 print 'The following segment names contain non 7-bit ASCII characters.' 335 print 'If you wish to change these names, use "UPDATE":' 336 print ' ' 337 select segment, name from dbo.syssegments 338 where name like @pat 339 end 340 341 if exists (select name from dbo.systypes 342 where name like @pat) 343 begin 344 if (@msilent = 1) 345 return (1) 346 347 select @ret_val = 1 348 print ' ' 349 print '===============================================================' 350 print 'Table.Column name: "systypes.name"' 351 print ' ' 352 print 'The following datatype names contain non 7-bit ASCII characters.' 353 print 'If you wish to change these names, use "sp_rename":' 354 print ' ' 355 select name from dbo.systypes 356 where name like @pat 357 end 358 359 if exists (select name from dbo.sysusers 360 where name like @pat) 361 begin 362 if (@msilent = 1) 363 return (1) 364 365 select @ret_val = 1 366 print ' ' 367 print '===============================================================' 368 print 'Table.Column name: "sysusers.name"' 369 print ' ' 370 print 'The following user or group names contain non 7-bit ASCII' 371 print 'characters. If you wish to change these names, use "UPDATE":' 372 print ' ' 373 select suid, uid, name from dbo.sysusers 374 where name like @pat 375 end 376 377 if (@ret_val = 0 and @msilent = 0) 378 begin 379 select @msg = 'Good news! Database "' + @dbname + '" has no obj/user/etc.' 380 print @msg 381 print 'names that contain non 7-bit ASCII characters.' 382 end 383 384 return (@ret_val) 385
exec sp_procxmode 'sp_checknames', 'AnyMode' go Grant Execute on sp_checknames to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..systypes reads table master..sysremotelogins (1)  reads table master..syslogins (1) reads table master..sysdatabases (1) reads table sybsystemprocs..sysobjects reads table master..sysdevices (1) reads table sybsystemprocs..sysindexes reads table sybsystemprocs..syssegments reads table sybsystemprocs..sysusers reads table sybsystemprocs..syscolumns reads table master..sysservers (1) |
