Psap at 18:59, 17 July 2007

2007-07-17T18:59:31Z

New page

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!

[[Category:ASE]]

Sp autoformat - Revision history

Psap at 18:59, 17 July 2007