DatabaseProcApplicationCreatedLinks
sybsystemprocssp_checknames  14 déc. 14Defects Propagation 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 or replace 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
RESULT SETS
sp_checknames_rset_013
sp_checknames_rset_012
sp_checknames_rset_011
sp_checknames_rset_010
sp_checknames_rset_009
sp_checknames_rset_008
sp_checknames_rset_007
sp_checknames_rset_006
sp_checknames_rset_005
sp_checknames_rset_004
sp_checknames_rset_003
sp_checknames_rset_002
sp_checknames_rset_001
sp_checknames_rset_014

DEFECTS
 QJWI 5 Join or Sarg Without Index 269
 MINU 4 Unique Index with nullable columns master..sysremotelogins master..sysremotelogins
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered
(remoteserverid, remoteusername)
Intersection: {remoteusername}
206
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered
(remoteserverid, remoteusername)
Intersection: {remoteusername}
221
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
287
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 288
 TNOI 4 Table with no index sybsystemprocs..syssegments sybsystemprocs..syssegments
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 283
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 283
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 301
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysdevices  
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public master..sysremotelogins  
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public sybsystemprocs..sp_checknames  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..syssegments  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MUCO 3 Useless Code Useless Brackets 27
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 60
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 126
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 144
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 189
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 209
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 246
 MUCO 3 Useless Code Useless Brackets 247
 MUCO 3 Useless Code Useless Brackets 271
 MUCO 3 Useless Code Useless Brackets 272
 MUCO 3 Useless Code Useless Brackets 290
 MUCO 3 Useless Code Useless Brackets 291
 MUCO 3 Useless Code Useless Brackets 308
 MUCO 3 Useless Code Useless Brackets 309
 MUCO 3 Useless Code Useless Brackets 326
 MUCO 3 Useless Code Useless Brackets 327
 MUCO 3 Useless Code Useless Brackets 344
 MUCO 3 Useless Code Useless Brackets 345
 MUCO 3 Useless Code Useless Brackets 362
 MUCO 3 Useless Code Useless Brackets 363
 MUCO 3 Useless Code Useless Brackets 377
 MUCO 3 Useless Code Useless Brackets 384
 QCRS 3 Conditional Result Set 119
 QCRS 3 Conditional Result Set 137
 QCRS 3 Conditional Result Set 156
 QCRS 3 Conditional Result Set 175
 QCRS 3 Conditional Result Set 201
 QCRS 3 Conditional Result Set 220
 QCRS 3 Conditional Result Set 239
 QCRS 3 Conditional Result Set 258
 QCRS 3 Conditional Result Set 282
 QCRS 3 Conditional Result Set 301
 QCRS 3 Conditional Result Set 319
 QCRS 3 Conditional Result Set 337
 QCRS 3 Conditional Result Set 355
 QCRS 3 Conditional Result Set 373
 QISO 3 Set isolation level 23
 QNAJ 3 Not using ANSI Inner Join 282
 QNAJ 3 Not using ANSI Inner Join 319
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
306
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
320
 MRST 2 Result Set Marker 119
 MRST 2 Result Set Marker 137
 MRST 2 Result Set Marker 156
 MRST 2 Result Set Marker 175
 MRST 2 Result Set Marker 201
 MRST 2 Result Set Marker 220
 MRST 2 Result Set Marker 239
 MRST 2 Result Set Marker 258
 MRST 2 Result Set Marker 282
 MRST 2 Result Set Marker 301
 MRST 2 Result Set Marker 319
 MRST 2 Result Set Marker 337
 MRST 2 Result Set Marker 355
 MRST 2 Result Set Marker 373
 MSUB 2 Subquery Marker 105
 MSUB 2 Subquery Marker 123
 MSUB 2 Subquery Marker 141
 MSUB 2 Subquery Marker 160
 MSUB 2 Subquery Marker 185
 MSUB 2 Subquery Marker 205
 MSUB 2 Subquery Marker 224
 MSUB 2 Subquery Marker 243
 MSUB 2 Subquery Marker 268
 MSUB 2 Subquery Marker 286
 MSUB 2 Subquery Marker 305
 MSUB 2 Subquery Marker 323
 MSUB 2 Subquery Marker 341
 MSUB 2 Subquery Marker 359
 MTR1 2 Metrics: Comments Ratio Comments: 9% 6
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 24 = 38dec - 16exi + 2 6
 MTR3 2 Metrics: Query Complexity Complexity: 329 6
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects} 0 282
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, u=sybsystemprocs..sysusers} 0 319

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysdatabases (1)  
writes table sybsystemprocs..sp_checknames_rset_012 
writes table sybsystemprocs..sp_checknames_rset_011 
reads table master..sysdevices (1)  
reads table master..sysservers (1)  
reads table master..syslogins (1)  
writes table sybsystemprocs..sp_checknames_rset_007 
writes table sybsystemprocs..sp_checknames_rset_003 
reads table master..sysremotelogins (1)  
reads table sybsystemprocs..sysusers  
writes table sybsystemprocs..sp_checknames_rset_009 
reads table sybsystemprocs..syssegments  
writes table sybsystemprocs..sp_checknames_rset_008 
reads table sybsystemprocs..sysobjects  
writes table sybsystemprocs..sp_checknames_rset_004 
writes table sybsystemprocs..sp_checknames_rset_013 
reads table sybsystemprocs..syscolumns  
writes table sybsystemprocs..sp_checknames_rset_002 
writes table sybsystemprocs..sp_checknames_rset_010 
reads table sybsystemprocs..systypes  
writes table sybsystemprocs..sp_checknames_rset_006 
reads table sybsystemprocs..sysindexes  
writes table sybsystemprocs..sp_checknames_rset_005 
writes table sybsystemprocs..sp_checknames_rset_001 
writes table sybsystemprocs..sp_checknames_rset_014