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.
sp_autoformat fulltabname [, selectlist , whereclause , orderby ]
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.
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.
is a search predicate, specified as a where clause, that filters out rows from the table being selected.
is an optional order by clause that specifies the order in which the output result set is presented.
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
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)
Uses the orderby parameter to specify an ordering in the result output:
sp_autoformat @fulltabname = 'syscolumns', @selectlist = "id, name", @orderby = "ORDER BY name"
To generate 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:
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)
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
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, you must:
Generate the required data in a temporary table, where the columns in the temporary table do not have any quoted identifiers.
Use sp_autoformat to produce the required output using the temporary table.
Rename the columns in the selectlist in the desired output format.
Any user can execute sp_autoformat. However, users selecting from the tables must have appropriate select privileges.
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 |
|
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
