Sp autoformat

The sp_autoformat stored procedure, supplied by Sybase, can be used to format the output of a query into a more readable resultset. Here are a few example how you might use it, and a list of some common issues.

Sample 1

When you run this query on ASE 15 it will produce really wide output.

select type,name from sysobjects

Now try this and the result is much better to read:

select type,name into #temp from sysobjects
exec sp_autoformat "#temp"
drop table #temp

Sample 2

You can also have sp_autoformat work on a subset of the #table you created:

select type,name into #temp from sybsystemprocs..sysobjects
exec sp_autoformat "#temp",@whereclause="where type = 'U'"
drop table #temp

This sample in itself does not make much sense, but it demonstrates that you can use sp_autoformat easily into existing code that creates temporary tables.

Some notes

• You must supply the word "where" into the @whereclause variable, otherwise sp_autoformat will crash. (This is not properly shown in the ISUG article of Q4 2006).
• All columns for the #temp table should be named (strictly spoken not an sp_autoformat issue). This sample will fail:

-- Will generate error
select type,count(*) into #temp from sysobjects group by type
exec sp_autoformat "#temp"
drop table #temp

-- Will work
select type,count(*) as cnt into #temp from sysobjects group by type
exec sp_autoformat "#temp"
drop table #temp

• Besides using the @whereclause variable, there is also the possiblity to use the @selectlist (to specify output columns) and @orderby (for sorting).
• Can create xml output like this:

select min(name) as name into #name from sysobjects
exec sp_autoformat "#name",@fmtspecifier="for xml"
drop table #name

Please note that this requires that xml has been enabled (sp_configure "enable xml") and that the output becomes wide again!