Formatting Results in Query Analyzer 1

[JPMorgan]

Below are the results from a query that I run in Query Analyzer. Is there a way to dynamically resize the width of a field so that, for instance, POOL_NAME is only as wide as the largest word that is returned. In this case POOL_NAME would be only 10 characters wide instead of the default of 25 since Prosperity is the largest word returned.

ASSET_NO  POOL_NAME                 DATE_ACQ                    
--------- ------------------------- --------------------------- 
330180010 Prosperity                NULL
330180041 Prosperity                NULL
640060446 DFSI NY2                  NULL
640090024 NULL                      NULL

This would be epecially helpful for those queries where multiple fields are returned. I appreciate the help!

 

[roccorocks]

Which version of SQL Server are you using? It looks like you are exporting the data to text, instead of the to grid?

The only options you have are: Column Aligned, Comma Delimited, Tab Delimited, Space Delimited, and Custom Delimited.

These options are available in the Tools, Options, Results tab in the query analyzer. Also, you can set the number of characters per column (1 - 256), but this will not help with what you need (static).

What are trying to achieve by do this? Is it for reports?

Let me know

Rocco

 

[JPMorgan]

Thanks, Rocco, for your reply:

I am using SQL Server 7 with SP1. You are correct that I am exporting to text instead of a grid. I didn't think that I could save a grid as a txt file.

My goal is to setup a job in Enterprise Mgr that executes every morning. The job will execute several queries and export the results to a txt file on my desktop. The reason is for automated DB maintenance. I want to constantly check for NULLs and other values in the database and this is really the only option that I have explored.

If you have an alternative way to monitor these types of
issues I am all ears.

Thanks for your help.

 

[SQLSister]

SQL Server does not excel at formatting. That is generally left up to the report generator or user interface.

I'm curious as to why you feel you need to check for null values daily.

 

[tlbroadbent]

JPMorgan,

It may not be worth all the effort but here is a script that does what you want using dynamic SQL. Note that two passes of the table are required.

Declare @len int, @sql varchar(200)

--Find the max length of the column
Select @len=max(len(POOL_NAME)) From TableName

--Create the SQL statement
Select @sql=
'Select ASSET_NO, POOL_NAME=Convert(char(' +
str(@len,2) +
'), POOL_NAME), DATE_ACQ From TableName'

--Execute the SQL statement
Exec(@sql) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[SQLBill]

If you know the max length of a column (which in this case appears to be 10 characters), you can always use the substring function.

select substring(expression, start, length) as myname

so for you it would be similar to:

select asset_no, substring(pool_name, 1, 10) as Pool_Name, date_acq


-SQLBill

 

[JPMorgan]

Thanks very much for all your suggestions. Terry's works the best for my situation. Thank you Terry!