sp_autoformat

Description

A utility stored procedure that produces readable result set data, sp_autoformat reformats the width of variable-length character data to display only non-blank characters. Trailing blanks are truncated in the output.

Syntax

sp_autoformat fulltabname[, selectlist, whereclause, orderby]

Parameters

Examples

Example 1

Returns a result set from a select statement similar to select id, colid, name from syscolumns where id = 3, where the character columns are autoformatted:

1> sp_autoformat "syscolumns", "id, colid, name", "where id = 3"
2> go

id           colid  name
------------ ------ ----------- 
           3      1 id
           3      2 number
           3      3 colid
           3      4 status
           3      5 type
           3      6 length
           3      7 offset
           3      8 usertype
           3      9 cdefault
           3     10 domain
           3     11 name
           3     12 printfmt
           3     13 prec
           3     14 scale
           3     15 remote_type
           3     16 remote_name
           3     17 xstatus
           3     18 xtype
           3     19 xdbid
           3     21 accessrule
           3     22 status2

Example 2

Renames the output columns using the following syntax:

[ < AS-Name label of Column> ][ ]*=[ ]*<column name>

<AS-Name label of Column> is optional, and you can use white spaces around the = separator:

1> sp_autoformat syscolumns, "'Object Id' = id, 'Column Name'=name, 
     'Column ID'=colid", "where id = 3"
2> go

 Object Id   Column Name Column ID 
------------ ----------- --------- 
           3 id                  1 
           3 number              2 
           3 colid               3 
           3 status              4 
           3 type                5 
           3 length              6 
           3 offset              7 
           3 usertype            8 
           3 cdefault            9 
           3 domain             10 
           3 name               11 
           3 printfmt           12 
           3 prec               13 
           3 scale              14 
           3 remote_type        15 
           3 remote_name        16 
           3 xstatus            17 
           3 xtype              18 
           3 xdbid              19 
           3 accessrule         21 
           3 status2            22 

(1 row affected)

Example 3

Uses the orderby parameter to specify an ordering in the result output:

sp_autoformat @fulltabname = 'syscolumns',
              @selectlist = "id, name",
              @orderby = "ORDER BY name"

Example 4

Generates an autoformatted result when you select from multiple tables, or if you have a complex SQL select statement with expressions in the select list, you must:

1. Use temporary tables to generate the result set:

   The following generates the list of the columns with the highest column ID on all system catalogs:

   select o.id, o.name, c.colid, c.name
   from sysobjects o, syscolumns c
   where o.id < 100 and o.id = c.id
     and c.colid = (select max(c2.colid) from syscolumns c2
                   where c2.id = c.id)
   order by o.name
   

   The following generates the same result set with auto-formatting of character data using a temporary table to produce readable output, and includes minor changes to provide column names in the temporary table:

   select o.id, ObjectName = o.name, c.colid, ColumnName = c.name
   into #result
   from sysobjects o, syscolumns c
   where o.id < 100 and o.id = c.id
     and c.colid = (select max(c2.colid) from syscolumns c2
                    where c2.id = c.id)
   

2. Use sp_autoformat on that temporary table to produce formatted output:

   The order by clause in the original select statement is skipped when generating the temporary table, and is instead added to the call to sp_autoformat when generating the output result.

   1> exec sp_autoformat @fulltabname = #result, @orderby = "order by 
      ObjectName"
   2> go
   

   id       ObjectName        colid ColumnName
   -------- ----------------- ------ -------------      11 sysalternates         2 altsuid 
         21 sysattributes        13 comments 
         55 syscertificates       6 suid 
         45 syscharsets           8 sortfile 
          3 syscolumns           22 status2 
          6 syscomments           8 status 
         37 sysconfigures         9 value4 
         17 sysconstraints        7 spare2 
         38 syscurconfigs        15 type 
         30 sysdatabases         19 status4 
         12 sysdepends           10 readobj 
         35 sysdevices            7 mirrorname 
         43 sysengines           12 starttime 
   
         ...
   
   (1 row affected)
   (return status = 0)
   

   You can further process the temporary table to report only on the required output for selected tables, as shown below:

   1> exec sp_autoformat #result, "id, 'Object Name' = ObjectName, 
      'Column Name' = ColumnName", "where id < 5"
   2> go
   

   id      Object Name Column Name 
   ------- ----------- ----------- 
         1 sysobjects  loginame
         2 sysindexes  crdate
         3 syscolumns  status2
         4 systypes    accessrule
   
   

Usage

• In Adaptive Server version 15.0.3 and higher, sp_autoformat accepts columns of datatypes int (smallint, bigint, tinyint, unsigned int), numeric, money, date/time, and float, real, and double precision.

• sp_autoformat looks for an object only in the current database. To use sp_autoformat on temporary tables, issue the procedure from tempdb.

• sp_autoformat does not validate that the columns referenced in any of the parameters actually exist in the table specified by the fulltabname parameter. sp_autoformat fails if you reference any nonexistent columns.

• Provide only one instance of a column in the select list.

• 0 – successful completion

• 1 – internal error, or usage error in invocation

• Other – any other errors raised by Adaptive Server during the execution of the generated SQL statement are returned back to the caller.

• sp_autoformat uses internal SQL variables to generate SQL statements that are then executed using execute immediate. The length of the generated SQL statement is limited to 2K bytes. Auto-formatting result sets for a large column list, or columns with long names can sometimes cause an error due to insufficient size of the buffer for the generated SQL statement.

• Quoted identifiers are not supported for either the table or column names. If you have result sets that use quoted idenfiers and that need autoformatting:

  1. Generate the required data in a temporary table, where the columns in the temporary table do not have any quoted identifiers.

  2. Use sp_autoformat to produce the required output using the temporary table.

  3. Rename the columns in the selectlist in the desired output format.

Permissions

Any user can execute sp_autoformat. However, users selecting from the tables must have appropriate select privileges.

Auditing

Values in event and extrainfo columns from the sysaudits table are: