Adjusting column width
-
hai,
In Query Analyzer how to adjust the column width.
suppose i have a table tableA(a varchar(100),b varchar(100) )and if i do
select * from tableA (with execute mode 'Results in text' in query analyser),
the column 'a' is taking a width of 100 and column b is if width 100 eventhough the actual data is of width 5 to 20... is there anyway to adjust the column width in query analyzer so that it will fit to the actual data length
subban -
If you view the results in QA using the GRID option (Menu bar Query > Results in Grid) OR CTRL+D this will show you just space used
OR
You can CAST/CONVERT the columns in your query i.e. CONVERT(VARCHAR(20), fieldA) OR CAST(fieldA AS VARCHAR(20))
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Ok, first of all i may not be able to decide the size (eg. varchar(20)) in advance, it depends on the user inputs in the table.
on more thing is command like dbcc sqlperf(logspace) also giving wide columns with 'result in text' mode
subban -
and finally i need to take this output to a file (query analyzer, execute mode -- results to file) also and in that case results in grid may not work
subban -
did you take a look at DTS to export to file ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I guess your only recourse would be. Send the SELECT statement to a #TEMP table, gather the MAX(LEN) of all the various columns, build another #TEMP table using DYNAMIC SQL and then SELECT from that.....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Sometimes I use the following technique to create a "temp" table which is actually a real table in the current database. I then drop the table when I'm done. This avoids the scope issues when using dynamic SQL. Maybe you can use this code as a starting point, or to trigger other ideas.
DROP TABLE tblData
GO
CREATE TABLE tblData
(
id int IDENTITY(1,1),
vcdata varchar(200)
)
SET NOCOUNT ON
INSERT tblData (vcdata) VALUES ('ABCDEF')
INSERT tblData (vcdata) VALUES ( NULL )
INSERT tblData (vcdata) VALUES ('ABCDEFGHIJKLMN')
INSERT tblData (vcdata) VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ')
INSERT tblData (vcdata) VALUES ('ABCDEF')
INSERT tblData (vcdata) VALUES ('ABC')
INSERT tblData (vcdata) VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGH')
INSERT tblData (vcdata) VALUES ('')
INSERT tblData (vcdata) VALUES ('ABCDEFGHI')
INSERT tblData (vcdata) VALUES ('ABCDEFGHIJKLMNOPQRSTU')
SET NOCOUNT OFF
------------------------------------------------------------------------------------------
DECLARE @maxlen int, @sql varchar(8000)
DECLARE @tblBaseName varchar(100), @tblseq int, @tblName varchar(100)
SELECT @maxlen = Max(Len(vcdata)) FROM tblData WHERE vcdata IS NOT NULL
---------------------------------------------------------------
-- Create a new table to hold the data with the reduced size
---------------------------------------------------------------
-- Loop until an available table name is found
SET @tblBaseName = 'myTemp'
SET @tblseq = 0
WHILE 1=1
BEGIN
SET @tblName = @tblBaseName + CONVERT(varchar(10), @tblseq)
IF NOT EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].' + @tblName)
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
BREAK -- found an available table name, so exit the loop
END
SET @tblSeq = @tblSeq + 1
END --WHILE
-- PRINT 'Using table ' + @tblName
SET NOCOUNT ON
SET @sql = 'CREATE TABLE ' + @tblName + ' ( id int PRIMARY KEY, vcdata_' + CONVERT(varchar(5), @maxlen) + ' varchar( ' + CONVERT(varchar(5), @maxlen) + ' ) )'
--PRINT @sql
EXEC (@sql)
SET @sql = 'INSERT ' + @tblName + ' SELECT id, Left(vcdata, ' + CONVERT(varchar(5), @maxlen) + ') FROM tblData '
--PRINT @sql
EXEC (@sql)
SET NOCOUNT OFF
SET @sql = 'SELECT * FROM ' + @tblName
--PRINT @sql
EXEC (@sql)
SET @sql = 'DROP TABLE ' + @tblName
EXEC (@sql)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply