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.
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
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.
- 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!