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 createproceduresp_autoformat(123 @fulltabname varchar(767)124 ,@selectlist varchar(8192)=NULL125 ,@whereclause varchar(4096)=NULL126 ,@orderby varchar(4096)=NULL127 ,@fmtspecifier varchar(32)=NULL128 ,@trace int = 0
129 )as130 begin131 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 than142 -- column name143 ,@asname_valid int
144 ,@sqlstr varchar(8000)145 ,@dot_loc int -- location of '.' in names146 ,@comma_loc int -- location of ',' in lists147 ,@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 number152 ,@as_colnum int -- as-list column number153 ,@retval int
154 ,@mbyte_char_str varchar(30)155 ,@mbyte int -- whether column is multi-byte156 ,@sellist_given tinyint -- Boolean157 ,@colid int -- colid158 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=NULL181 ,@owner= user_name()-- current user.182 ,@tablename=@fulltabname183 ,@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 begin193 print "%1!: (Nesting Level: %2!) fulltabname: '%3!'"
194 ,@whoami, @@nestlevel,@fulltabname195 end196 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 begin204 /*
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 end211 212 settransactionisolationlevel 1
213 set chained off214 215 -- Extract out the 1st part of a 3-part or 2-part name216 if(charindex(".",@fulltabname)> 0)217 begin218 execsp_namecrack@fulltabname219 ,@sitenameoutput220 ,@dbnameoutput221 ,@owneroutput222 ,@tablenameoutput223 end224 else225 begin226 select@tablename=@fulltabname,227 @sitename=null,@dbname=null,@owner=null228 end229 230 -- Now that we have the basename for the table, do special processing231 -- to see if it is a #temp table. And manage the db-context for the232 -- temp table. User -might- be asking for a #temp table that is not233 -- the currently-assigned tempdb. If so, let the passed-in dbname be.234 -- Otherwise, set the dbname to be the user's currently assigned temp235 -- dbname.236 --237 if((@dbnameisNULL)and(substring(@tablename, 1, 1)= "#"))238 begin239 select@dbname= db_name(@@tempdbid)240 end241 242 -- By now if @dbname is NULL, use current db to search for table.243 --244 if(@dbnameISNULL)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(@dbidISNULL)252 begin253 raiserror 18088
254 select@retval= 1
255 goto error_exit
256 end257 258 -- select @objid = object_id(@dbname + "." + @owner + "." + @tablename)259 select@lcl_tablename=@dbname+ "." +@owner+ "." +@tablename260 select@objid= object_id(@lcl_tablename)261 if(@objidISNULL)262 begin263 -- Table does not exist in database. The last arg is NULL,264 -- as we don't need to further describe the action of what265 -- failed.266 --267 raiserror 17870,@fulltabname,@dbname,NULL268 select@retval= 1
269 goto error_exit
270 end271 272 if(@trace>=@trace_highlevel)273 begin274 print "%1!: (Nesting Level: %2!) %3! dbname: '%4!' owner: '%5!' tablename: '%6!' Objid: %7!"
275 ,@whoami, @@nestlevel
276 ,@currdbname,@dbname,@owner,@tablename277 ,@objid278 end279 280 -- ******************************************************************281 -- Create a #temp table that will be used to further process the282 -- 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 from285 -- master to avoid deadlocking on syscolumns in tempdb. 286 --287 selectc.colidas '_clid#af' -- colid288 ,c.nameas '_clname#af' -- column name289 ,t.usertypeas '_usrtype#af' -- usrtype290 ,t.typeas '_type#af' -- system type291 ,t.nameas '_typnm#af' -- type name292 ,c.lengthas '_colen#af' -- length293 ,convert(int notnull, 0)as '_maxlen#af' -- max data length294 ,convert(tinyint, 0)as '_auto#af' -- autoformat bit295 ,convert(tinyint, 0)as '_slc#af' -- appears in the select list296 ,convert(int, 0)as '_sorder#af' -- position in the select list297 ,convert(varchar(50),c.name)as '_asname#af'
298 ,convert(tinyint, 0)as '_mbyte#af'
299 into #colinfo_af lock allpages
300 frommaster..syscolumns c,master..systypes t
301 where 1 = 0
302 303 setrowcount 0
304 305 -- Insert column's datatype info and other book-keeping information306 -- for all columns in the table into the above #temp table.307 --308 if(@dbname!= 'tempdb')309 begin310 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 for318 -- the SELECT. This allows the @selectlist to be NULL319 -- and we still get the right results.320 -- And initial selectorder is 0 as we have not321 -- 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" -- maxlength326 + ",0" -- autoformat327 + ",1" -- selected328 + ",c.colid" -- selectorder329 + ",c.name" -- asname330 + ",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 begin343 raiserror 19206,@whoami_sp,@sqlstr344 goto error_exit
345 end346 347 end348 else349 begin350 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 SELECTc.colid,c.name,t.usertype,t.type,t.name355 ,casewhenc.length< 80 then 80 elsec.lengthend356 , 0 -- maxlength357 , 0 -- autoformat358 , 1 -- selected, assume by default all columns359 ,c.colid-- selectorder 360 ,c.name-- asname361 , 0 -- mbyte (multi-byte char column)362 FROMtempdb.dbo.syscolumns c,tempdb.dbo.systypes t
363 WHEREc.id=@objidANDc.usertype=t.usertype364 365 end366 367 -- *************************************************************368 -- Initially we assume that all columns in the table are being369 -- selected.370 --371 select@sel_colnum=count(*)from #colinfo_af
372 373 -- If table's columns were not found in the syscolumns of the374 -- db specified (or chosen by this script), then there is an375 -- error somewhere. Flag that and bail.376 --377 if(@sel_colnum= 0)378 begin379 raiserror 19237,@dbname,@fulltabname380 select@retval= 0
381 goto error_exit
382 end383 384 if(@trace>=@trace_temptable)385 begin386 execsp_autoformat #colinfo_af, @trace =@trace387 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 begin393 print "%1!: (Nesting Level: %2!) %3! dbname: '%4!' owner: '%5!' tablename: '%6!' Objid: %7!"
394 ,@whoami, @@nestlevel
395 ,@currdbname,@dbname,@owner,@tablename396 ,@objid397 end398 end399 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(@selectlistisNOTNULL)407 begin-- {408 409 -- Mark all columns as not being selected as we have410 -- user-specified SELECT list.411 --412 update #colinfo_af
413 set _slc#af = 0 -- all columns are not selected414 , _sorder#af = 0 -- no colid order415 416 if(@@error != 0)417 begin418 select@retval= @@error
419 goto error_exit
420 end421 422 if(@trace>=@trace_highlevel)423 begin424 print "Processing select list: %1!",@selectlist425 end426 -- 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(@selectlistisNOTNULL)434 begin435 if(@trace>=@trace_dumplists)436 begin437 print " Processing 'SELECT' list: %1!"
438 ,@selectlist439 end440 441 select@comma_loc= charindex(',',@selectlist)442 443 -- Extract out the current column.444 --445 select@colname=case@comma_loc446 when 0 then@selectlist447 else substring(@selectlist, 1,448 (@comma_loc- 1))449 end450 451 -- Remember length of this entire pattern, with452 -- possibly embedded '=', as we need that length453 -- 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 id462 -- select 'This ID' = id463 --464 -- (Column's AS-name is optional.)465 --466 select@equal_loc= charindex('=',@colname)467 468 if(@equal_loc!= 0)469 begin470 -- L.h.s of '=' is label, AS-name471 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_len478 - datalength(@asname)479 - 1))480 481 select@asname= rtrim(ltrim(@asname))482 ,@colname= rtrim(ltrim(@colname))483 end484 else485 begin486 select@colname= rtrim(ltrim(@colname))487 select@asname=@colname488 end489 490 select@asname_valid= valid_name(@asname)491 if(@trace>=@trace_detailed)492 begin493 print " %1! Column: '%2!', AS-name: '%3!' valid ID: %4!"
494 ,@sel_colnum,@colname,@asname495 ,@asname_valid496 end497 498 -- Filter out duplicate columns in select-list499 -- If column exists but selected = 1, then it500 -- has been seen already. Raise an error.501 --502 if((selectcount(*)from #colinfo_af
503 where _clname#af =@colname504 and _slc#af = 1)!= 0)505 begin506 /*
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 ,@colname513 select@retval= 1
514 goto error_exit
515 end516 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_colnum522 , _slc#af = 1
523 , _asname#af =@asname524 where _clname#af =@colname525 526 -- User is expected to be honest and provide a527 -- select-list that is exactly contained in the528 -- table's column-list. If we did not update529 -- exactly one row, it is an error. (0 rows530 -- updated means column was not found in the list531 -- of columns. More than one row updated means that532 -- the same column appeared twice in the select list.)533 --534 if(@@rowcount != 1)535 begin536 raiserror 18588,@colname,@fulltabname537 if(@trace>=@trace_temptable)538 begin539 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 orderby _clid#af
542 543 end544 select@retval= 1
545 goto error_exit
546 end547 548 if(@@error != 0)549 begin550 select@retval= @@error
551 goto error_exit
552 end553 554 -- Move past the previous column specifier (which555 -- possibly includes an embedded '=') and (any)556 -- ','-separator557 --558 select@selectlist=559 case@comma_loc560 when 0 thenNULL561 else substring(@selectlist,(@comma_loc+ 1),562 (datalength(@selectlist)563 -@this_col_spec_len564 - 1)565 )566 end567 568 -- If the separator was the last item, and there is569 -- no more data after that (i.e. 'id, name,') flag570 -- that condition and raise an error. We should keep571 -- the behaviour consistent with that of572 -- 'id, name, '; where there are trailing spaces573 -- after the last comma.574 --575 if((@comma_loc!= 0)and(@selectlistISNULL))576 begin577 raiserror 18588,@selectlist,@fulltabname578 select@retval= 1
579 goto error_exit
580 end581 end582 583 end-- } if (@selectlist is NOT NULL)584 585 if(@trace>=@trace_temptable)586 begin587 /* 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 end589 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 begin605 print "Processing #temp table looking for columns needing auto-formatting."
606 end607 608 -- List of usertype values for char-type data that are multi-byte:609 -- 24 -- nchar610 -- 25 -- nvarchar611 -- 34 -- unichar612 -- 35 -- univarchar613 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 cursorfor623 select _clname#af, _type#af, _colen#af, _clid#af
624 from #colinfo_af
625 where _type#af in(38 -- intn, bigint null626 , 39 -- varchar, [long]sysname, nvarchar627 , 47 -- char, nchar628 , 48 -- tinyint629 , 49 -- date630 , 50 -- bit631 , 51 -- time632 , 52 -- smallint633 , 55 -- decimal634 , 56 -- int635 , 58 -- smalldatetime636 , 59 -- real637 , 60 -- money638 , 61 -- datetime639 , 62 -- float640 , 63 -- numeric641 , 65 -- usmallint642 , 66 -- uint643 , 67 -- ubigint644 , 68 -- uintn645 , 106 -- decimaln646 , 108 -- numericn647 , 109 -- floatn648 , 110 -- moneyn649 , 111 -- datetimn650 , 122 -- smallmoney 651 , 123 -- daten652 , 135 -- unichar653 , 147 -- timen654 , 155 -- univarchar655 , 187 -- bigdatetimen656 , 188 -- bigtimen 657 , 189 -- bigdatetime658 , 190 -- bigtime659 , 191 -- bigint660 )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,@colid675 676 while(@@sqlstatus = 0)677 begin-- {678 679 if(@trace>=@trace_detailed)680 begin681 print " Column '%1!'",@colname682 end683 684 -- Some background: the first isnull() caters685 -- for a case where we have 0 rows at all,686 -- so the minimum length is 1. The second687 -- isnull() enforces a minimum length of 4688 -- when NULL values occur in the column, to689 -- accomodate 'NULL'. The convert() to varchar690 -- is used to determine the length of691 -- non-varchar data.692 --693 -- if(int1==int2 and int1,int2 > 0)694 -- => sign(int1/int2) * sign (int2/int1) = 1695 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,@colid705 706 707 end-- } while (@@sqlstatus = 0)708 709 close curcolumns
710 711 deallocatecursor curcolumns
712 713 select@whoami_sp=@whoami+ '(2)'
714 715 -- finalize the INSERT for EI call716 select@sqlstr=@sqlstr+ "0" + " FROM #colinfo_af,"
717 +@fulltabname+ " " +@whereclause718 719 if(@trace>=@trace_dumplists)720 begin721 select@sqlstr722 end723 724 exec(@sqlstr)725 726 select@retval= @@error
727 if(@retval!= 0)728 begin729 raiserror 19206,@whoami_sp,@sqlstr730 goto error_exit
731 end732 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 EI744 and #colinfo_af._maxlen#af = 0 -- initial set or rows745 746 delete #colinfo_af where _clid#af < 0
747 748 -- Numeric values need to be right-justified. At this point, we have749 -- 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 of752 -- 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 begin759 /* 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 end761 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 cursorfor774 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 orderby _sorder#af
779 780 open curcolumns
781 782 fetch curcolumns into@colname,@maxlength,@autoformat783 ,@asname,@mbyte,@type,@collength784 785 while(@@sqlstatus = 0)786 begin787 select@sqlstr=@sqlstr788 +case@sel_colnumwhen 1 then "" else ", " end789 790 select@sel_colnum=@sel_colnum+ 1
791 792 if(@autoformat= 1)793 begin794 select@sqlstr=@sqlstr795 +@asname+ '=' +796 casewhen@typein(39,-- char, nchar 797 47,-- varchar, [long]sysname, nvarchar 798 135,-- unichar 799 155)-- univarchar 800 then801 -- Expect to handle all potentially long, character802 -- datatypes here803 "SUBSTRING(convert(varchar(" +convert(varchar,@collength)+ "),"
804 +@colname805 + "),1,"
806 +case@mbyte807 when 0
808 thenconvert(varchar,@maxlength)809 elseconvert(nvarchar,@maxlength)810 end811 + ")"
812 else813 -- We need to right-adjust the integer datatypes; since this814 -- 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 +@colname819 + "),'NULL')," +convert(varchar,@maxlength)+ ")"
820 end821 end822 else823 begin824 -- Only generate an AS-name clause when the AS-name825 -- is known to be different than the column name.826 --827 select@sqlstr=@sqlstr828 +case@asname829 when@colnamethenNULL830 else@asname+ '='
831 end832 +@colname833 end834 835 fetch curcolumns into@colname,@maxlength,@autoformat836 ,@asname,@mbyte,@type,@collength837 end838 839 close curcolumns
840 841 deallocatecursor curcolumns
842 843 -- Append the FROM list for the select from the table.844 --845 select@sqlstr=@sqlstr846 847 -- Go back to using the full table name as the848 -- user passed to us. #temp tables were processed849 -- earlier to generate the name that the user850 -- should have been using.851 --852 + " FROM " +@fulltabname853 + " " +@whereclause854 + " " +@orderby855 + " " +@fmtspecifier856 857 select@whoami_sp=@whoami+ '(3)'
858 if(@trace= 0)859 begin860 setrowcount@setrowcount861 end862 863 exec(@sqlstr)864 select@retval= @@error
865 if(@retval!= 0)866 begin867 raiserror 19206,@whoami_sp,@sqlstr868 goto error_exit
869 end870 871 return 0
872 873 error_exit:
874 return@retval875 876 end877
exec sp_procxmode 'sp_autoformat', 'AnyMode'
go
Grant Execute on sp_autoformat to public
go
