DatabaseProcApplicationCreatedLinks
sybsystemprocssp_autoformat  31 Aug 14Defects Dependencies

1     
2     /*
3     ** Generated by spgenmsgs.pl on Fri Jul 25 17:36:44 2003 
4     */
5     /*
6     ** raiserror Messages for autoformat [Total 5]
7     **
8     ** 17870, "Table '%1!' does not exist in database '%2!'. %3!"
9     ** 18088, "The target database does not exist."
10    ** 18588, "Column '%1!' does not exist in table '%2!'."
11    ** 19206, "%1!: Execute immediate SQL failed. SQL statement is: %2!"
12    ** 19237, "No columns were found in syscolumns in database '%1!' for table '%2!'."
13    */
14    /*
15    ** sp_getmessage Messages for autoformat [Total 0]
16    */
17    /*
18    ** End spgenmsgs.pl output.
19    */
20    /*
21    ** **************************************************************************
22    ** sp_autoformat
23    **
24    ** Generic stored procedure to select data from any table and auto-format
25    ** column's that are of [var]char (string) type (except text) so that these
26    ** column's appear only wide as the maximum-sized data in the table being
27    ** selected from.
28    **
29    ** We also provide for a way to ONLY select a subset of the columns in the
30    ** table. This list can be passed via the @selectlist argument, and should
31    ** be a comma-separated list like what would be used by the user if they
32    ** were doing a normal SELECT from this table. This list should ONLY contain
33    ** the raw column names, and no further expressions around it.
34    **
35    ** The output will be generated in the order that the columns are requested
36    ** via this @selectlist argument.
37    **
38    ** Restrictions:
39    ** =============
40    **
41    **  . Does not fully support use of quoted identfiers for table and
42    **    column names.
43    **
44    **  . The @selectlist is expected to be comma-separated list.
45    **
46    **  . This proc does *not* validate that the columns in the WHERE clause,
47    **    ORDER BY provided by the user actually exist in the table being
48    **    selected from. These are just tacked on to the final SELECT statement.
49    **    If there are errors in these arguments, you will just get a run-time
50    **    error from execute immediate.
51    **
52    **  . The length of the generated SQL statement is max of 2048 bytes. For
53    **    tables with large # of columns, this might overflow. (Provision can
54    **    be made for the caller to supply a SQL buffer in the future.)
55    **
56    **  . There is some support for multi-byte character types but the
57    **    actual reporting of the data is untested.
58    **
59    ** Parameters:
60    **	@fulltabname	- Full table name, possibly 2-, 3-part
61    **	@selectlist	- SELECT list of columns to output.
62    **	@whereclause	- Any WHERE predicates on table.
63    **	@orderby	- Any ORDER BY clause for output.
64    **	@fmtspecifier	- Format specifier to display output, say in XML.
65    **			  (Future extension. Currently only for: 'for xml')
66    **	@trace		- Tracing levels.
67    **
68    ** NOTE(s):
69    ** =========
70    **
71    **  - If the @selectlist is provided, then only these columns will be
72    **    projected out. If not, columns are projected out in colid order,
73    **    just like a 'select *' output.
74    **
75    ** - @selectlist should be formatted as:
76    **
77    **	[  ][ ]*=[ ]*<
78    **
79    **  	. The  is optional.
80    **  	. There can be white spaces around the '=' separator.
81    **
82    ** - This procedure creates a temporary table to store the column information
83    **   for the table being processed. The table is small, but causes space
84    **   requirements on tempdb. Column names of this table has a bit cryptic 
85    **   names - this is to avoid potential conflict with column names of a table
86    **   to process.
87    **
88    ** - If the table being reported on is large-ish, and there are multiple
89    **   'char'-type columns, then the processing could be slightly slow as we
90    **   make as many passes over the input table as there are char-type columns
91    **   to determine each column's max-length.
92    **
93    ** Examples:
94    ** =========
95    **
96    	sp_autoformat systypes
97    
98    	sp_autoformat syscolumns, 'id, colid, name'
99    
100   	sp_autoformat syscolumns, "TableID = id, 'Column ID'=colid, name"
101   			, "where id = 3"
102   ** Trace-levels:
103   ** ==============
104   **	0	- Default; no tracing.
105   **	1	- High-level tracing of major phases.
106   **	2	- Detailed tracing for each phase.
107   **	3	- Dump select-list, as-list at end of each iteration.
108   **	4	- Dump contents of #temp table as it is processed.
109   **
110   **  Trace messages are intentionally left in-line as they are meant only
111   **  for debugging and internal uses.
112   **
113   ** Returns:
114   **	0	- If all processing went ok.
115   **	1	- For usage errors/internal errors.
116   **		- The @@error value that was received when an error occured.
117   **		  This could be the return value from execute immediate.
118   **		  Client can trap this and do further processing based on
119   **		  the error number.
120   {
121   */
122   create procedure sp_autoformat(
123       @fulltabname varchar(767)
124       , @selectlist varchar(8192) = NULL
125       , @whereclause varchar(4096) = NULL
126       , @orderby varchar(4096) = NULL
127       , @fmtspecifier varchar(32) = NULL
128       , @trace int = 0
129   ) as
130       begin
131           declare @whoami varchar(255)
132               , @whoami_sp varchar(258)
133               , @currdbname varchar(30)
134               , @sitename varchar(30) -- unused.
135               , @dbname varchar(30)
136               , @dbid int -- @dbname's dbid.
137               , @owner varchar(30)
138               , @tablename varchar(255)
139               , @lcl_tablename varchar(767)
140               , @colname varchar(510)
141               , @asname varchar(275) -- Could be longer than
142               -- column name
143               , @asname_valid int
144               , @sqlstr varchar(8000)
145               , @dot_loc int -- location of '.' in names
146               , @comma_loc int -- location of ',' in lists
147               , @equal_loc int -- location of '=' in name.
148               , @objid int
149               , @maxlength int -- really in # of chars.
150               , @autoformat tinyint
151               , @sel_colnum int -- column number
152               , @as_colnum int -- as-list column number
153               , @retval int
154               , @mbyte_char_str varchar(30)
155               , @mbyte int -- whether column is multi-byte
156               , @sellist_given tinyint -- Boolean
157               , @colid int -- colid
158   
159               , @this_col_spec_len int
160               , @trace_highlevel int
161               , @trace_detailed int
162               , @trace_dumplists int
163               , @trace_temptable int
164               , @setrowcount int
165               , @type int
166               , @collength int
167   
168           select @setrowcount = @@setrowcount
169   
170           select @whoami = object_name(@@procid, db_id('sybsystemprocs'))
171               , @currdbname = db_name()
172   
173               -- Get rid of any leading spaces in input arguments.
174               , @fulltabname = ltrim(@fulltabname)
175               , @whereclause = ltrim(@whereclause)
176               , @orderby = ltrim(@orderby)
177   
178           -- Initially assume that it is a one-part name.
179           --
180           select @dbname = NULL
181               , @owner = user_name() -- current user.
182               , @tablename = @fulltabname
183               , @objid = object_id(@fulltabname)
184               , @sellist_given = 0
185   
186               , @trace_highlevel = 1
187               , @trace_detailed = 2
188               , @trace_dumplists = 3
189               , @trace_temptable = 4
190   
191           if (@trace >= @trace_highlevel)
192           begin
193               print "%1!: (Nesting Level: %2!) fulltabname: '%3!'"
194                   , @whoami, @@nestlevel, @fulltabname
195           end
196   
197           /*
198           ** If we are under a user defined xact, disallow this since we may
199           ** we create tables via select-into and that is not allowed in a
200           ** multi-statement xact.
201           */
202           if @@trancount > 0
203           begin
204               /*
205               ** 17260, "Can't run %1! from within a transaction."
206               */
207               raiserror 17260, "sp_autoformat"
208               select @retval = 1
209               goto error_exit
210           end
211   
212           set transaction isolation level 1
213           set chained off
214   
215           -- Extract out the 1st part of a 3-part or 2-part name
216           if (charindex(".", @fulltabname) > 0)
217           begin
218               exec sp_namecrack @fulltabname
219                   , @sitename output
220                   , @dbname output
221                   , @owner output
222                   , @tablename output
223           end
224           else
225           begin
226               select @tablename = @fulltabname,
227                   @sitename = null, @dbname = null, @owner = null
228           end
229   
230           -- Now that we have the basename for the table, do special processing
231           -- to see if it is a #temp table. And manage the db-context for the
232           -- temp table. User -might- be asking for a #temp table that is not
233           -- the currently-assigned tempdb. If so, let the passed-in dbname be.
234           -- Otherwise, set the dbname to be the user's currently assigned temp
235           -- dbname.
236           --
237           if ((@dbname is NULL) and (substring(@tablename, 1, 1) = "#"))
238           begin
239               select @dbname = db_name(@@tempdbid)
240           end
241   
242           -- By now if @dbname is NULL, use current db to search for table.
243           --
244           if (@dbname IS NULL)
245               select @dbname = db_name()
246   
247           -- ******************************************************************
248           -- Error checking for invalid dbname, object name etc.
249           --
250           select @dbid = db_id(@dbname)
251           if (@dbid IS NULL)
252           begin
253               raiserror 18088
254               select @retval = 1
255               goto error_exit
256           end
257   
258           -- select @objid = object_id(@dbname + "." + @owner + "." + @tablename)
259           select @lcl_tablename = @dbname + "." + @owner + "." + @tablename
260           select @objid = object_id(@lcl_tablename)
261           if (@objid IS NULL)
262           begin
263               -- Table does not exist in database. The last arg is NULL,
264               -- as we don't need to further describe the action of what
265               -- failed.
266               --
267               raiserror 17870, @fulltabname, @dbname, NULL
268               select @retval = 1
269               goto error_exit
270           end
271   
272           if (@trace >= @trace_highlevel)
273           begin
274               print "%1!: (Nesting Level: %2!) %3! dbname: '%4!' owner: '%5!' tablename: '%6!' Objid: %7!"
275                   , @whoami, @@nestlevel
276                   , @currdbname, @dbname, @owner, @tablename
277                   , @objid
278           end
279   
280           -- ******************************************************************
281           -- Create a #temp table that will be used to further process the
282           -- column list for the table being selected from.
283           -- All we are doing here is to create a template table using      
284           -- datatypes from syscolumns, systypes. Use the source tables from
285           -- master to avoid deadlocking on syscolumns in tempdb.           
286           --
287           select c.colid as '_clid#af' -- colid
288               , c.name as '_clname#af' -- column name
289               , t.usertype as '_usrtype#af' -- usrtype
290               , t.type as '_type#af' -- system type
291               , t.name as '_typnm#af' -- type name
292               , c.length as '_colen#af' -- length
293               , convert(int not null, 0) as '_maxlen#af' -- max data length
294               , convert(tinyint, 0) as '_auto#af' -- autoformat bit
295               , convert(tinyint, 0) as '_slc#af' -- appears in the select list
296               , convert(int, 0) as '_sorder#af' -- position in the select list
297               , convert(varchar(50), c.name) as '_asname#af'
298               , convert(tinyint, 0) as '_mbyte#af'
299           into #colinfo_af lock allpages
300           from master..syscolumns c, master..systypes t
301           where 1 = 0
302   
303           set rowcount 0
304   
305           -- Insert column's datatype info and other book-keeping information
306           -- for all columns in the table into the above #temp table.
307           --
308           if (@dbname != 'tempdb')
309           begin
310               select @sqlstr =
311                   "INSERT #colinfo_af"
312                   + "(_clid#af,_clname#af,_usrtype#af,_type#af,_typnm#af,_colen#af"
313                   + ",_maxlen#af,_auto#af,_slc#af,_sorder#af"
314                   + ",_asname#af,_mbyte#af" + ")"
315   
316                   -- Initially all columns are considered unselected,
317                   -- Initial load of the data is in colid order for
318                   -- the SELECT. This allows the @selectlist to be NULL
319                   -- and we still get the right results.
320                   -- And initial selectorder is 0 as we have not
321                   -- processed the select list, yet.
322                   --
323                   + " SELECT c.colid,c.name,t.usertype,t.type,t.name"
324                   + ",case when c.length < 80 then 80 else c.length end"
325                   + ",0" -- maxlength
326                   + ",0" -- autoformat
327                   + ",1" -- selected
328                   + ",c.colid" -- selectorder
329                   + ",c.name" -- asname
330                   + ",0" -- mbyte (multi-byte char column)
331   
332                   + " FROM " + @dbname + ".dbo.syscolumns c,"
333                   + @dbname + ".dbo.systypes t"
334                   + " WHERE c.id=" + convert(varchar, @objid)
335                   + " AND c.usertype=t.usertype"
336   
337               select @whoami_sp = @whoami + '(1)'
338   
339               exec (@sqlstr)
340               select @retval = @@error
341               if (@retval != 0)
342               begin
343                   raiserror 19206, @whoami_sp, @sqlstr
344                   goto error_exit
345               end
346   
347           end
348           else
349           begin
350               INSERT #colinfo_af
351               (_clid#af, _clname#af, _usrtype#af, _type#af, _typnm#af,
352                   _colen#af, _maxlen#af, _auto#af, _slc#af, _sorder#af,
353                   _asname#af, _mbyte#af)
354               SELECT c.colid, c.name, t.usertype, t.type, t.name
355                   , case when c.length < 80 then 80 else c.length end
356                   , 0 -- maxlength
357                   , 0 -- autoformat
358                   , 1 -- selected, assume by default all columns
359                   , c.colid -- selectorder 
360                   , c.name -- asname
361                   , 0 -- mbyte (multi-byte char column)
362               FROM tempdb.dbo.syscolumns c, tempdb.dbo.systypes t
363               WHERE c.id = @objid AND c.usertype = t.usertype
364   
365           end
366   
367           -- *************************************************************
368           -- Initially we assume that all columns in the table are being
369           -- selected.
370           --
371           select @sel_colnum = count(*) from #colinfo_af
372   
373           -- If table's columns were not found in the syscolumns of the
374           -- db specified (or chosen by this script), then there is an
375           -- error somewhere. Flag that and bail.
376           --
377           if (@sel_colnum = 0)
378           begin
379               raiserror 19237, @dbname, @fulltabname
380               select @retval = 0
381               goto error_exit
382           end
383   
384           if (@trace >= @trace_temptable)
385           begin
386               exec sp_autoformat #colinfo_af, @trace = @trace
387   
388               /* Adaptive Server has expanded all '*' elements in the following statement */ select #colinfo_af._clid#af, #colinfo_af._clname#af, #colinfo_af._usrtype#af, #colinfo_af._type#af, #colinfo_af._typnm#af, #colinfo_af._colen#af, #colinfo_af._maxlen#af, #colinfo_af._auto#af, #colinfo_af._slc#af, #colinfo_af._sorder#af, #colinfo_af._asname#af, #colinfo_af._mbyte#af from #colinfo_af
389   
390               print ""
391               if (@trace >= @trace_highlevel)
392               begin
393                   print "%1!: (Nesting Level: %2!) %3! dbname: '%4!' owner: '%5!' tablename: '%6!' Objid: %7!"
394                       , @whoami, @@nestlevel
395                       , @currdbname, @dbname, @owner, @tablename
396                       , @objid
397               end
398           end
399   
400           /*
401           ** *************************************************************
402           ** Parse the @selectlist, if any, and update the #temp table to
403           ** record the order that the user has requested in which the
404           ** columns should appear in the final output.
405           */
406           if (@selectlist is NOT NULL)
407           begin -- {
408   
409               -- Mark all columns as not being selected as we have
410               -- user-specified SELECT list.
411               --
412               update #colinfo_af
413               set _slc#af = 0 -- all columns are not selected
414                   , _sorder#af = 0 -- no colid order
415   
416               if (@@error != 0)
417               begin
418                   select @retval = @@error
419                   goto error_exit
420               end
421   
422               if (@trace >= @trace_highlevel)
423               begin
424                   print "Processing select list: %1!", @selectlist
425               end
426               -- Remember that user provided a select-list,
427               -- which will soon be parsed away to become NULL.
428               select @sellist_given = 1
429   
430                   -- Reset the select-list column #; will be recomputed.
431                   , @sel_colnum = 0
432   
433               while (@selectlist is NOT NULL)
434               begin
435                   if (@trace >= @trace_dumplists)
436                   begin
437                       print "  Processing 'SELECT' list: %1!"
438                           , @selectlist
439                   end
440   
441                   select @comma_loc = charindex(',', @selectlist)
442   
443                   -- Extract out the current column.
444                   --
445                   select @colname = case @comma_loc
446                           when 0 then @selectlist
447                           else substring(@selectlist, 1,
448                                   (@comma_loc - 1))
449                       end
450   
451                   -- Remember length of this entire pattern, with
452                   -- possibly embedded '=', as we need that length
453                   -- to move past this column specifier.
454                   --
455                   select @this_col_spec_len = datalength(@colname)
456   
457                   select @sel_colnum = @sel_colnum + 1
458   
459                   -- Parse the column specifier which can be one of:
460                   --
461                   --	select id
462                   --	select 'This ID' = id
463                   --
464                   -- (Column's AS-name is optional.)
465                   --
466                   select @equal_loc = charindex('=', @colname)
467   
468                   if (@equal_loc != 0)
469                   begin
470                       -- L.h.s of '=' is label, AS-name
471                       select @asname = substring(@colname, 1,
472                               (@equal_loc - 1))
473   
474                       -- R.h.s of '=' is column name.
475                       select @colname = substring(@colname,
476                               (@equal_loc + 1),
477                               (@this_col_spec_len
478                               - datalength(@asname)
479                               - 1))
480   
481                       select @asname = rtrim(ltrim(@asname))
482                           , @colname = rtrim(ltrim(@colname))
483                   end
484                   else
485                   begin
486                       select @colname = rtrim(ltrim(@colname))
487                       select @asname = @colname
488                   end
489   
490                   select @asname_valid = valid_name(@asname)
491                   if (@trace >= @trace_detailed)
492                   begin
493                       print "    %1! Column: '%2!', AS-name: '%3!' valid ID: %4!"
494                           , @sel_colnum, @colname, @asname
495                           , @asname_valid
496                   end
497   
498                   -- Filter out duplicate columns in select-list
499                   -- If column exists but selected = 1, then it
500                   -- has been seen already. Raise an error.
501                   --
502                   if ((select count(*) from #colinfo_af
503                               where _clname#af = @colname
504                                   and _slc#af = 1) != 0)
505                   begin
506                       /*
507                       ** This is a possibly temporary restriction,
508                       ** so no raiserror is being done. This error
509                       ** might go away in the future.
510                       */
511                       print "Column '%1!' specified multiple times in the SELECT list. This is currently unsupported."
512                           , @colname
513                       select @retval = 1
514                       goto error_exit
515                   end
516   
517                   -- Record the column's order # in the #temp table.
518                   -- And store its AS-name for the output.
519                   --
520                   update #colinfo_af
521                   set _sorder#af = @sel_colnum
522                       , _slc#af = 1
523                       , _asname#af = @asname
524                   where _clname#af = @colname
525   
526                   -- User is expected to be honest and provide a
527                   -- select-list that is exactly contained in the
528                   -- table's column-list. If we did not update
529                   -- exactly one row, it is an error. (0 rows
530                   -- updated means column was not found in the list
531                   -- of columns. More than one row updated means that
532                   -- the same column appeared twice in the select list.)
533                   --
534                   if (@@rowcount != 1)
535                   begin
536                       raiserror 18588, @colname, @fulltabname
537                       if (@trace >= @trace_temptable)
538                       begin
539   
540                           /* Adaptive Server has expanded all '*' elements in the following statement */ select #colinfo_af._clid#af, #colinfo_af._clname#af, #colinfo_af._usrtype#af, #colinfo_af._type#af, #colinfo_af._typnm#af, #colinfo_af._colen#af, #colinfo_af._maxlen#af, #colinfo_af._auto#af, #colinfo_af._slc#af, #colinfo_af._sorder#af, #colinfo_af._asname#af, #colinfo_af._mbyte#af from #colinfo_af
541                           order by _clid#af
542   
543                       end
544                       select @retval = 1
545                       goto error_exit
546                   end
547   
548                   if (@@error != 0)
549                   begin
550                       select @retval = @@error
551                       goto error_exit
552                   end
553   
554                   -- Move past the previous column specifier (which
555                   -- possibly includes an embedded '=') and (any)
556                   -- ','-separator
557                   --
558                   select @selectlist =
559                       case @comma_loc
560                           when 0 then NULL
561                           else substring(@selectlist, (@comma_loc + 1),
562                                   (datalength(@selectlist)
563                                   - @this_col_spec_len
564                                   - 1)
565                               )
566                       end
567   
568                   -- If the separator was the last item, and there is
569                   -- no more data after that (i.e. 'id, name,') flag
570                   -- that condition and raise an error. We should keep
571                   -- the behaviour consistent with that of
572                   -- 'id, name, '; where there are trailing spaces
573                   -- after the last comma.
574                   --
575                   if ((@comma_loc != 0) and (@selectlist IS NULL))
576                   begin
577                       raiserror 18588, @selectlist, @fulltabname
578                       select @retval = 1
579                       goto error_exit
580                   end
581               end
582   
583           end -- }	if (@selectlist is NOT NULL)
584   
585           if (@trace >= @trace_temptable)
586           begin
587               /* Adaptive Server has expanded all '*' elements in the following statement */ select #colinfo_af._clid#af, #colinfo_af._clname#af, #colinfo_af._usrtype#af, #colinfo_af._type#af, #colinfo_af._typnm#af, #colinfo_af._colen#af, #colinfo_af._maxlen#af, #colinfo_af._auto#af, #colinfo_af._slc#af, #colinfo_af._sorder#af, #colinfo_af._asname#af, #colinfo_af._mbyte#af from #colinfo_af
588           end
589   
590           /*
591           ** *************************************************************
592           ** Process the #temp table looking for columns that need auto-
593           ** -formatting.
594           **
595           **  . Scan each row of the #temp table.
596           **  . Identify columns of [var]char, uni[var]char, and sysname
597           **    types. (See below for complete list of types that need 
598           **    auto-formatting.)
599           **  . Update the #temp table w/max lengths for each column which
600           **    is of this type.
601           **  . Set a bit in table tagging this column, to avoid re-work.
602           */
603           if (@trace >= @trace_highlevel)
604           begin
605               print "Processing #temp table looking for columns needing auto-formatting."
606           end
607   
608           -- List of usertype values for char-type data that are multi-byte:
609           -- 24 -- nchar
610           -- 25 -- nvarchar
611           -- 34 -- unichar
612           -- 35 -- univarchar
613           select @mbyte_char_str = "24, 25, 34, 35"
614   
615   
616           /*
617           ** Note about the datatypes being handled here:
618           **  . [var]binary is being skipped. We dont' want to deal with
619           **    truncation of trailing 0s business right now.
620           **  . TEXT (image) is being skipped as it is too much processing.
621           */
622           declare curcolumns cursor for
623           select _clname#af, _type#af, _colen#af, _clid#af
624           from #colinfo_af
625           where _type#af in (38 -- intn, bigint null
626                   , 39 -- varchar, [long]sysname, nvarchar
627                   , 47 -- char, nchar
628                   , 48 -- tinyint
629                   , 49 -- date
630                   , 50 -- bit
631                   , 51 -- time
632                   , 52 -- smallint
633                   , 55 -- decimal
634                   , 56 -- int
635                   , 58 -- smalldatetime
636                   , 59 -- real
637                   , 60 -- money
638                   , 61 -- datetime
639                   , 62 -- float
640                   , 63 -- numeric
641                   , 65 -- usmallint
642                   , 66 -- uint
643                   , 67 -- ubigint
644                   , 68 -- uintn
645                   , 106 -- decimaln
646                   , 108 -- numericn
647                   , 109 -- floatn
648                   , 110 -- moneyn
649                   , 111 -- datetimn
650                   , 122 -- smallmoney	
651                   , 123 -- daten
652                   , 135 -- unichar
653                   , 147 -- timen
654                   , 155 -- univarchar
655                   , 187 -- bigdatetimen
656                   , 188 -- bigtimen   
657                   , 189 -- bigdatetime
658                   , 190 -- bigtime
659                   , 191 -- bigint
660               )
661               and _sorder#af > 0
662   
663           /*
664           ** Reuse existing table instead of creating a new one.
665           ** Later a self-join may be done as well as two tables join.
666           ** Those duplicate rows will have columns: colname, maxlength
667           ** set and colid = -1.
668           */
669           select @sqlstr = "INSERT #colinfo_af(_clid#af,_clname#af,_usrtype#af,
670   			_type#af,_typnm#af,_colen#af,_auto#af,_slc#af,_sorder#af,_asname#af,_mbyte#af,_maxlen#af)
671   		SELECT -1,_clname#af,0,0,'',0,0,0,0,'',0 , "
672           open curcolumns
673   
674           fetch curcolumns into @colname, @type, @collength, @colid
675   
676           while (@@sqlstatus = 0)
677           begin -- {
678   
679               if (@trace >= @trace_detailed)
680               begin
681                   print "    Column '%1!'", @colname
682               end
683   
684               -- Some background: the first isnull() caters
685               -- for a case where we have 0 rows at all,
686               -- so the minimum length is 1. The second
687               -- isnull() enforces a minimum length of 4
688               -- when NULL values occur in the column, to
689               -- accomodate 'NULL'. The convert() to varchar
690               -- is used to determine the length of
691               -- non-varchar data.
692               --
693               --  if(int1==int2 and int1,int2 > 0)
694               -- 	=>  sign(int1/int2) * sign (int2/int1) = 1
695   
696               select @sqlstr = @sqlstr +
697                   "isnull(max(isnull(char_length("
698                   + "convert(varchar("
699                   + convert(varchar, @collength) + "),"
700                   + @colname + ")" + "),4)),1)"
701                   + " *sign(_clid#af/" + convert(varchar(4), @colid) +
702                   ")*sign (" + convert(varchar(4), @colid) + "/_clid#af) +"
703   
704               fetch curcolumns into @colname, @type, @collength, @colid
705   
706   
707           end -- }	while (@@sqlstatus = 0)
708   
709           close curcolumns
710   
711           deallocate cursor curcolumns
712   
713           select @whoami_sp = @whoami + '(2)'
714   
715           -- finalize the INSERT for EI call
716           select @sqlstr = @sqlstr + "0" + " FROM #colinfo_af,"
717               + @fulltabname + " " + @whereclause
718   
719           if (@trace >= @trace_dumplists)
720           begin
721               select @sqlstr
722           end
723   
724           exec (@sqlstr)
725   
726           select @retval = @@error
727           if (@retval != 0)
728           begin
729               raiserror 19206, @whoami_sp, @sqlstr
730               goto error_exit
731           end
732   
733           /*
734           ** temp table has duplicate set of rows now. fix maxlength values
735           ** and get rid of duplicate part of the table.
736           */
737           update #colinfo_af
738           set _maxlen#af = c2._maxlen#af,
739               _auto#af = 1,
740               _mbyte#af = sign(charindex(#colinfo_af._usrtype#af, @mbyte_char_str))
741           from #colinfo_af, #colinfo_af c2
742           where #colinfo_af._clname#af = c2._clname#af
743               and c2._maxlen#af > 0 -- rows inserted by EI
744               and #colinfo_af._maxlen#af = 0 -- initial set or rows
745   
746           delete #colinfo_af where _clid#af < 0
747   
748           -- Numeric values need to be right-justified. At this point, we have
749           -- determined the maximum length for each column based on the value contents,
750           -- but that will still lead to incorrect formatting when the column name 
751           -- is longer than this maximum length. Therefore, set the max. length of
752           -- the formatted column to the column name length if the latter is larger.
753           update #colinfo_af
754           set _maxlen#af = char_length(_asname#af)
755           where _maxlen#af < char_length(_asname#af)
756   
757           if (@trace >= @trace_temptable)
758           begin
759               /* Adaptive Server has expanded all '*' elements in the following statement */ select #colinfo_af._clid#af, #colinfo_af._clname#af, #colinfo_af._usrtype#af, #colinfo_af._type#af, #colinfo_af._typnm#af, #colinfo_af._colen#af, #colinfo_af._maxlen#af, #colinfo_af._auto#af, #colinfo_af._slc#af, #colinfo_af._sorder#af, #colinfo_af._asname#af, #colinfo_af._mbyte#af from #colinfo_af
760           end
761   
762           /*
763           ** ****************************************************************
764           ** Go back and process the #temp table, extracting out each column
765           ** and generate a SQL statement to account for the max-lengths of
766           ** columns that need auto-formatting. If the user had given us a
767           ** SELECT list, then only pick those columns. Otherwise, pick all
768           ** columns (select *).
769           */
770           select @sqlstr = "SELECT"
771               , @sel_colnum = 1
772   
773           declare curcolumns cursor for
774           select _clname#af, _maxlen#af, _auto#af, _asname#af, _mbyte#af, _type#af
775               , _colen#af
776           from #colinfo_af
777           where _slc#af = 1
778           order by _sorder#af
779   
780           open curcolumns
781   
782           fetch curcolumns into @colname, @maxlength, @autoformat
783               , @asname, @mbyte, @type, @collength
784   
785           while (@@sqlstatus = 0)
786           begin
787               select @sqlstr = @sqlstr
788                   + case @sel_colnum when 1 then "" else ", " end
789   
790               select @sel_colnum = @sel_colnum + 1
791   
792               if (@autoformat = 1)
793               begin
794                   select @sqlstr = @sqlstr
795                       + @asname + '=' +
796                       case when @type in (39, -- char, nchar                          
797                                   47, -- varchar, [long]sysname, nvarchar     
798                                   135, -- unichar                              
799                                   155) -- univarchar                           
800                           then
801                           -- Expect to handle all potentially long, character
802                           -- datatypes here
803                           "SUBSTRING(convert(varchar(" + convert(varchar, @collength) + "),"
804                           + @colname
805                           + "),1,"
806                           + case @mbyte
807                               when 0
808                               then convert(varchar, @maxlength)
809                               else convert(nvarchar, @maxlength)
810                           end
811                           + ")"
812                           else
813                               -- We need to right-adjust the integer datatypes; since this
814                               -- will not display NULL values as 'NULL' anymore due to the 
815                               -- string concatenation, we need to explicitly stick in 'NULL'.
816                               -- Note that 80 characters is enough for all integer datatypes.
817                               "right(space(80)+isnull(convert(varchar(80),"
818                               + @colname
819                               + "),'NULL')," + convert(varchar, @maxlength) + ")"
820                       end
821               end
822               else
823               begin
824                   -- Only generate an AS-name clause when the AS-name
825                   -- is known to be different than the column name.
826                   --
827                   select @sqlstr = @sqlstr
828                       + case @asname
829                           when @colname then NULL
830                           else @asname + '='
831                       end
832                       + @colname
833               end
834   
835               fetch curcolumns into @colname, @maxlength, @autoformat
836                   , @asname, @mbyte, @type, @collength
837           end
838   
839           close curcolumns
840   
841           deallocate cursor curcolumns
842   
843           -- Append the FROM list for the select from the table.
844           --
845           select @sqlstr = @sqlstr
846   
847               -- Go back to using the full table name as the
848               -- user passed to us. #temp tables were processed
849               -- earlier to generate the name that the user
850               -- should have been using.
851               --
852               + " FROM " + @fulltabname
853               + " " + @whereclause
854               + " " + @orderby
855               + " " + @fmtspecifier
856   
857           select @whoami_sp = @whoami + '(3)'
858           if (@trace = 0)
859           begin
860               set rowcount @setrowcount
861           end
862   
863           exec (@sqlstr)
864           select @retval = @@error
865           if (@retval != 0)
866           begin
867               raiserror 19206, @whoami_sp, @sqlstr
868               goto error_exit
869           end
870   
871           return 0
872   
873   error_exit:
874           return @retval
875   
876       end
877   


exec sp_procxmode 'sp_autoformat', 'AnyMode'
go

Grant Execute on sp_autoformat to public
go
RESULT SETS
sp_autoformat_rset_005
sp_autoformat_rset_004
sp_autoformat_rset_003
sp_autoformat_rset_002
sp_autoformat_rset_001

DEFECTS
 QCAR 6 Cartesian product between tables master..syscolumns c and [master..systypes t] 300
 MEST 4 Empty String will be replaced by Single Space 390
 MEST 4 Empty String will be replaced by Single Space 788
 MTYP 4 Assignment type mismatch @tablename: varchar(255) = varchar(767) 182
 MTYP 4 Assignment type mismatch @tablename: varchar(255) = varchar(767) 226
 MTYP 4 Assignment type mismatch _asname#af: varchar(50) = longsysname(255) 360
 MTYP 4 Assignment type mismatch @asname: varchar(275) = varchar(510) 487
 MTYP 4 Assignment type mismatch _asname#af: varchar(50) = varchar(275) 523
 MULT 4 Using literal database 'tempdb' tempdb..syscolumns 362
 MULT 4 Using literal database 'tempdb' tempdb..systypes 362
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 386
 QCSC 4 Costly 'select count()', use 'exists()' 502
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 504
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 746
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 777
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause curcolumns 623
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause curcolumns 774
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 122
 MGTP 3 Grant to public master..syscolumns  
 MGTP 3 Grant to public master..systypes  
 MGTP 3 Grant to public sybsystemprocs..sp_autoformat  
 MGTP 3 Grant to public tempdb..syscolumns  
 MGTP 3 Grant to public tempdb..systypes  
 MNER 3 No Error Check should check return value of exec 218
 MNER 3 No Error Check should check @@error after select into 287
 MNER 3 No Error Check should check @@error after insert 350
 MNER 3 No Error Check should check return value of exec 386
 MNER 3 No Error Check should check @@error after update 520
 MNER 3 No Error Check should check @@error after update 737
 MNER 3 No Error Check should check @@error after delete 746
 MNER 3 No Error Check should check @@error after update 753
 MUCO 3 Useless Code Useless Brackets in create proc 122
 MUCO 3 Useless Code Useless Begin-End Pair 130
 MUCO 3 Useless Code Useless Brackets 191
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 237
 MUCO 3 Useless Code Useless Brackets 244
 MUCO 3 Useless Code Useless Brackets 251
 MUCO 3 Useless Code Useless Brackets 261
 MUCO 3 Useless Code Useless Brackets 272
 MUCO 3 Useless Code Useless Brackets 308
 MUCO 3 Useless Code Useless Brackets 341
 MUCO 3 Useless Code Useless Brackets 377
 MUCO 3 Useless Code Useless Brackets 384
 MUCO 3 Useless Code Useless Brackets 391
 MUCO 3 Useless Code Useless Brackets 406
 MUCO 3 Useless Code Useless Brackets 416
 MUCO 3 Useless Code Useless Brackets 422
 MUCO 3 Useless Code Useless Brackets 433
 MUCO 3 Useless Code Useless Brackets 435
 MUCO 3 Useless Code Useless Brackets 468
 MUCO 3 Useless Code Useless Brackets 491
 MUCO 3 Useless Code Useless Brackets 502
 MUCO 3 Useless Code Useless Brackets 534
 MUCO 3 Useless Code Useless Brackets 537
 MUCO 3 Useless Code Useless Brackets 548
 MUCO 3 Useless Code Useless Brackets 575
 MUCO 3 Useless Code Useless Brackets 585
 MUCO 3 Useless Code Useless Brackets 603
 MUCO 3 Useless Code Useless Brackets 676
 MUCO 3 Useless Code Useless Brackets 679
 MUCO 3 Useless Code Useless Brackets 719
 MUCO 3 Useless Code Useless Brackets 727
 MUCO 3 Useless Code Useless Brackets 757
 MUCO 3 Useless Code Useless Brackets 785
 MUCO 3 Useless Code Useless Brackets 792
 MUCO 3 Useless Code Useless Brackets 858
 MUCO 3 Useless Code Useless Brackets 865
 QCRS 3 Conditional Result Set 388
 QCRS 3 Conditional Result Set 540
 QCRS 3 Conditional Result Set 587
 QCRS 3 Conditional Result Set 721
 QCRS 3 Conditional Result Set 759
 QISO 3 Set isolation level 212
 QJWT 3 Join or Sarg Without Index on temp table 742
 QNAJ 3 Not using ANSI Inner Join 300
 QNAJ 3 Not using ANSI Inner Join 362
 QNAJ 3 Not using ANSI Inner Join 741
 QNAM 3 Select expression has no name @sqlstr 721
 QPNC 3 No column in condition 301
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
363
 VNRD 3 Variable is not read @sitename 227
 VNRD 3 Variable is not read @sellist_given 428
 VUNU 3 Variable is not used @dot_loc 145
 VUNU 3 Variable is not used @as_colnum 152
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 774
 CUPD 2 Updatable Cursor Marker (updatable by default) 623
 MDYS 2 Dynamic SQL Marker 339
 MDYS 2 Dynamic SQL Marker 724
 MDYS 2 Dynamic SQL Marker 863
 MRST 2 Result Set Marker 388
 MRST 2 Result Set Marker 540
 MRST 2 Result Set Marker 587
 MRST 2 Result Set Marker 721
 MRST 2 Result Set Marker 759
 MSUB 2 Subquery Marker 502
 MTR1 2 Metrics: Comments Ratio Comments: 49% 122
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 41 = 40dec - 1exi + 2 122
 MTR3 2 Metrics: Query Complexity Complexity: 229 122
 PRED_QUERY_COLLECTION 2 {c=tempdb..syscolumns, t=tempdb..systypes} 0 354

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_namecrack  
reads table tempdb..systypes (1)  
reads table tempdb..syscolumns (1)  
reads table master..systypes (1)  
calls proc sybsystemprocs..sp_autoformat  
read_writes table tempdb..#colinfo_af (1) 
reads table master..syscolumns (1)  

CALLERS
called by proc sybsystemprocs..sp_remoteoption  
called by proc sybsystemprocs..sp_helpexternlogin  
called by proc sybsystemprocs..sp_help_resource_limit  
called by proc sybsystemprocs..sp_helpcomputedcolumn  
   called by proc sybsystemprocs..sp_help  
   called by proc sybsystemprocs..sp_helpindex  
      called by proc sybsystemprocs..sp_help  
called by proc sybsystemprocs..sp_replication_path  
called by proc sybsystemprocs..sp_ddlgen_database  
called by proc sybsystemprocs..sp_spaceusage_object_genoutput  
   called by proc sybsystemprocs..sp_spaceusage_object  
      called by proc sybsystemprocs..sp_spaceusage  
called by proc sybsystemprocs..sp_helpapptrace  
called by proc sybsystemprocs..sp_pkeys  
called by proc sybsystemprocs..sp_addthreshold  
called by proc sybsystemprocs..sp_securityprofile  
called by proc sybsystemprocs..sp_setreptable  
called by proc sybsystemprocs..sp_monitor_list  
called by proc sybsystemprocs..sp_helpkey  
   called by proc sybsystemprocs..sp_help  
called by proc sybsystemprocs..sp_cacheconfig  
   called by proc sybsystemprocs..sp_do_poolconfig  
      called by proc sybsystemprocs..sp_poolconfig  
called by proc sybsystemprocs..sp_passwordpolicy  
   called by proc sybsystemprocs..sp_downgrade  
called by proc sybsystemprocs..sp_helpcache  
called by proc sybsystemprocs..sp_dbrecovery_order  
called by proc sybsystemprocs..sp_setrepcol  
called by proc sybsystemprocs..sp_help_qplan  
   called by proc sybsystemprocs..sp_cmp_all_qplans  
called by proc sybsystemprocs..sp_helpserver  
called by proc sybsystemprocs..sp_helpextendedproc  
called by proc sybsystemprocs..sp_helprotect  
called by proc sybsystemprocs..sp_helpjoins  
called by proc sybsystemprocs..sp_version  
called by proc sybsystemprocs..sp_help_rep_agent  
called by proc sybsystemprocs..sp_options  
called by proc sybsystemprocs..sp_monitor_procedure  
called by proc sybsystemprocs..sp_help_params  
   called by proc sybsystemprocs..sp_help  
   called by proc sybsystemprocs..sp_showtext_sproc_tagline  
      called by proc sybsystemprocs..sp_showtext_output  
         called by proc sybsystemprocs..sp_showtext  
            called by proc sybsystemprocs..sp_helptext  
            called by proc sybsystemprocs..sp_helptext_usage  
               called by proc sybsystemprocs..sp_helptext  
called by proc sybsystemprocs..sp_aux_computeprivs  
   called by proc sybsystemprocs..sp_column_privileges  
   called by proc sybsystemprocs..sp_table_privileges  
called by proc sybsystemprocs..sp_monitor_list_montypes  
   called by proc sybsystemprocs..sp_monitor_list  
called by proc sybsystemprocs..sp_monitor_procstack  
called by proc sybsystemprocs..sp_helpdevice  
called by proc sybsystemprocs..sp_helpconfig  
called by proc sybsystemprocs..sp_spaceused  
called by proc sybsystemprocs..sp_monitor_statement  
called by proc sybsystemprocs..sp_encryption  
   called by proc sybsystemprocs..sp_downgrade  
called by proc sybsystemprocs..sp_setreplicate  
called by proc sybsystemprocs..sp_helpjava  
called by proc sybsystemprocs..sp_autoformat  
called by proc sybsystemprocs..sp_column_privileges  
called by proc sybsystemprocs..sp_config_rep_agent  
called by proc sybsystemprocs..sp_droplogin  
called by proc sybsystemprocs..sp_indsuspect  
called by proc sybsystemprocs..sp_configure  
   called by proc sybsystemprocs..sp_configure  
   called by proc sybsystemprocs..sp_setlockpromote  
      called by proc sybsystemprocs..sp_setrowlockpromote  
      called by proc sybsystemprocs..sp_setpglockpromote  
called by proc sybsystemprocs..sp_estspace  
called by proc sybsystemprocs..sp_helpthread  
called by proc sybsystemprocs..sp_helpconstraint  
called by proc sybsystemprocs..sp_fkeys  
called by proc sybsystemprocs..sp_helpgroup  
   called by proc sybsystemprocs..sp_helpuser  
called by proc sybsystemprocs..sp_helpsegment  
called by proc sybsystemprocs..sp_monitor_connection  
called by proc sybsystemprocs..sp_dboption_flmode  
   called by proc sybsystemprocs..sp_dboption  
called by proc sybsystemprocs..sp_who  
called by proc sybsystemprocs..sp_displaylogin  
called by proc sybsystemprocs..sp_helpindex  
called by proc sybsystemprocs..sp_helpmaplogin  
called by proc sybsystemprocs..sp_showcontrolinfo  
called by proc sybsystemprocs..sp_helpdb  
called by proc sybsystemprocs..sp_spaceusage_tranlog_genoutput  
   called by proc sybsystemprocs..sp_spaceusage_tranlog  
      called by proc sybsystemprocs..sp_spaceusage  
called by proc sybsystemprocs..sp_help  
called by proc sybsystemprocs..sp_help_qpgroup  
called by proc sybsystemprocs..sp_lookup  
called by proc sybsystemprocs..sp_logintrigger  
   called by proc sybsystemprocs..sp_modifylogin  
called by proc sybsystemprocs..sp_helpthreshold  
called by proc sybsystemprocs..sp_helplanguage  
called by proc sybsystemprocs..sp_multdb_show  
   called by proc sybsystemprocs..sp_tempdb  
called by proc sybsystemprocs..sp_objectsegment  
called by proc sybsystemprocs..sp_ijdbc_aux_computeprivs  
called by proc sybsystemprocs..sp_helpobjectdef  
called by proc sybsystemprocs..sp_monitor_deadlock_count_by  
   called by proc sybsystemprocs..sp_monitor_deadlock  
      called by proc sybsystemprocs..sp_monitor  
called by proc sybsystemprocs..sp_helpuser  
called by proc sybsystemprocs..sp_setrepproc  
called by proc sybsystemprocs..sp_depends  
called by proc sybsystemprocs..sp_helpremotelogin  
called by proc sybsystemprocs..sp_helpartition  
   called by proc sybsystemprocs..sp_help  
called by proc sybsystemprocs..sp_downgrade  
called by proc sybsystemprocs..sp_monitor_event