Help with Column Widths

How can I find out the sizes of the columns that are returned from a query?
I need to know so that I can pad each one to it's full size with spaces so
that everything will line up using a mono spaced font in a textbox .NET
control. If I fire off a query and get back a DataSet of results, then
when I loop through them and populate a large multiline textbox, each
column's contents are only as long as the data contained in it, not the
full width that the column is designed as in the schema. I'm using c# in
.NET. Is there a way to find out what the full width should be for each
column returned from a query?
-- Rob
Pull ThePlug to reply by email...this sounds like UI formatting again ...use the
Sring.PadRight(totalLength, char)
method to format it to the length yout want...
Message posted via http://www.webservertalk.com|||or if you really want the length of the text
select datalength(description),description from [table]
for each of the columns that you would like to "padright"
Message posted via http://www.webservertalk.com|||In a tiered architecture, the formatting in done in the front end and
not in the database. Doesn't C# have such functions?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in
news:1112972652.223954.175560@.g14g2000cwa.googlegroups.com:

> In a tiered architecture, the formatting in done in the front end and
> not in the database. Doesn't C# have such functions?
>
Yes, I want to do it in the front-end. I am going to use the pad()
function, but I don't know the column widths to pass to the pad() function.
This app is very similar to Query Analyzer in that it will allow you to
type in your query and then it will execute it and return the results. I'm
giving the users the option of viewing the results in a DataGrid, or in a
textbox so they can easily "copy and paste" from the text to some other
application. Query Analyzer does this too, and their "text" output mode
has all of the columns presented in a "padded" format, all neat and
aligned. If I fill a textbox control with the returned results from a
query, each column is trimmed of any extra spaces before I get it. I'm
trying to figure out how to put those spaces back before displaying the
results.
Thanks for your reply...
-- Rob
Pull ThePlug to reply by email...|||"baie dronk via webservertalk.com" <forum@.webservertalk.com> wrote in
news:892513b471ec4827844239655f3aabf0@.SQ
webservertalk.com:

> this sounds like UI formatting again ...use the
> Sring.PadRight(totalLength, char)
> method to format it to the length yout want...
>
This is exactly what I intend to do, but I have no idea how to tell what
the totalLength should be for each column. This app allows users to type
in their SQL query and then execute it. The results returned are displayed
either with a DataGrid, or in a textbox where they can "copy and paste" the
results to another app or whatever. This behaviour is *EXACTLY* like
Microsoft's Query Analyzer. Their "text" output is all padded and lined up
nicely, which is what I'm trying to mimic.
Thanks for your reply...
-- Rob
Pull ThePlug to email...

Help with a loop.

I am writing a script that will go through all the database files on a
server, collect the file sizes and return the values in a single
table. This script works for the most part, but there is an instance
when the script fails to collect the information properly. When there
are two or more data files the script only reports the first one twice.
Can someone take a look at this loop and tell me where the error is?
Thanks
-Matt-
/ ****************************************
**********
Script to calculate information about the Data Files
****************************************
**********/
DECLARE @.dbname varchar(50)
DECLARE @.string varchar(250)
SET @.string = ''
Declare @.rows int
CREATE TABLE #dbcc_showfilestats (
fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 decimal (28, 2),
UsedExtents1 decimal (28, 2),
Name varchar(50),
FileName sysname )
CREATE TABLE #dbstats (
DB_Name varchar(50),
DB_Total_Size_in_MB decimal (28, 2),
DB_Used_Size_in_MB decimal (28, 2),
DB_Free_Size_in_MB decimal (28, 2),
DB_Percent_Used decimal (28, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
-- Collects all the DB name
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @.dbname
WHILE (@.@.fetch_status = 0)
BEGIN
SET @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS'
INSERT #dbcc_showfilestats
EXEC (@.string)
SELECT * FROM #dbcc_showfilestats -- Debug
SELECT @.rows = count(*) from #dbcc_showfilestats
While @.rows > 0
BEGIN
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB, DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB = sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB = sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used = sum(UsedExtents1/TotalExtents1)*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
SET @.rows = @.rows - 1
END
TRUNCATE TABLE #dbcc_showfilestats
FETCH NEXT FROM dbnames_cursor INTO @.dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
SELECT * FROM #dbstats --Debug
DROP TABLE #dbstats --Debug
DROP TABLE #dbcc_showfilestats --DebugYou are selecting the same rows from #dbcc_showfilestats
every time through your 'while' loop.
Add
id int identity(1,1)
to your #dbcc_showfilestats table and change
FROM #dbcc_showfilestats
to
FROM #dbcc_showfilestats where id=@.rows|||Hi Matthew,
In addition to correctly adding a unique integer to distinguish rows in
your temp table as Mark has suggested, you may want to look at using
another temp table to loop through rather than using a cursor.
Cursors are very memory heavy in comparison to a looped through temp
table.
So instead your loop (in pseudo) would look more like:
-- SET UP 'CURSOR' TABLE
SELECT name INTO #databases FROM master..sysdatabases
-- DEFINE LOOPING PARAMETER
DECLARE @.unqName nvarchar(4000)
-- SELECT LOOPING PARAMETER
SELECT @.unqName = name FROM #databases
-- ENTER WHILE LOOP
WHILE LEN(@.unqName) > 0
BEGIN
-- PERFORM LOOP CODE
--DELETE ROW FROM LOOPING TABLE #databases
DELETE FROM #databases WHERE name = @.unqName
SELECT @.unqName = '' -- CLEAR VARIABLE
SELECT @.unqName = name FROM #databases
END
This will make a big difference in large looping scenarios - just try
it out.
Andrew La Grange
Business Artists
http://www.businessartists.co.za|||By doing the SUM(...), which is an aggregate function, you are only
saying you want 1 row.
What do you really want, the size and usage of each file? Or the size
of the entire database?
-Jeff|||If you want the entire database, then there is no need for a loop use
the following:
SELECT * FROM #dbcc_showfilestats -- Debug
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB,
DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB =
sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB =
sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used =
(sum(UsedExtents1)/sum(TotalExtents1))*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
TRUNCATE TABLE #dbcc_showfilestats