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

fulltabname

specifies the name of table from which data is being selected. Use owner names if the object owner is not the user running the command.

selectlist

specifies the comma-separated list of columns to be selected in the result set. Columns in the table can be renamed using the <name> = <column> notation. See examples. If selectlist is not provided, all columns in the table specified are output in column ID order.

whereclause

is a search predicate, specified as a where clause, that filters out rows from the table being selected.

orderby

is an optional order by clause that specifies the order in which the output result set is presented.

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


Return codes


Restrictions

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:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect