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

No permission checks are performed for sp_autoformat. Permission checks do not differ based on the granular permissions settings. Users selecting from the tables must have appropriate select privileges.

Auditing

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