August 2, 2007 at 1:49 am
Hi guys,
I finally found some time and wrote the script I had initally in mind. Even though both your scripts provide valuable information none of them reports the correct MaxActualRowLength. so here we go:
--First create a testtable
IF
OBJECT_ID('TestTable') IS NOT NULL
DROP TABLE TestTable
go
CREATE
TABLE TestTable
(c1 nvarchar(2000),
c2 nvarchar(2000),
c3 nvarchar(3000))
GO
INSERT
INTO TestTable (c1,c2,c3)
Values('100 characters in c1................................................................................',
'40 characters...........................', '20 more characters..')
INSERT
INTO TestTable (c1,c2,c3)
Values('40 characters..........................', '100 characters in c2................................................................................','20 more characters..')
INSERT
INTO TestTable (c1,c2,c3)
Values('40 characters..........................', '20 more characters..','100 characters in c3................................................................................')
So the defined rowlength is 14000, and the max actual used is 320 ((40+20+100)*2)
but Lowell' script reports 600 and Jeffs even 13400.
I also fixed the problem with tables which had a different owner than dbo.
So here's my solution:
--===== If the result table already exists, drop it
IF
OBJECT_ID('TempDB..#tmp') IS NOT NULL
DROP TABLE #tmp
IF
OBJECT_ID('TempDB..#RowLength') IS NOT NULL
DROP TABLE #rowlength
SELECT
so. id as ObjectId,
so.name AS TableName,
sc.name AS ColumnName
INTO #tmp
FROM sysobjects so
INNER JOIN syscolumns sc
ON so.id = sc.id
WHERE OBJECTPROPERTY(sc.id,'IsTable') = 1
AND OBJECTPROPERTY(sc.id,'IsMSShipped') = 0
Update
#tmp
SET TableName = '['+ u.name+'].['+ o.name+']'
FROM sysobjects o JOIN sysusers u
ON u.uid = o.uid
where id = ObjectId
SELECT
c.id AS ObjectId,
OBJECT_NAME(c.id) AS TableName,
SUM(c.length) AS DefinedRowLength,
0 AS MaxActualLength
INTO #rowlength
FROM syscolumns c
WHERE OBJECTPROPERTY(c.id,'IsTable') = 1
AND OBJECTPROPERTY(c.id,'IsMSShipped') = 0
GROUP BY c.id
UPDATE
#rowlength
SET TableName = t.TableName
FROM #tmp t
WHERE #rowlength.ObjectId = t.ObjectId
DECLARE @isql NVARCHAR(4000),
@tbname VARCHAR(128),
@clname VARCHAR(128),
@len SMALLINT
DECLARE
c1 CURSOR FOR
SELECT DISTINCT tablename
FROM #tmp
OPEN
c1
FETCH NEXT FROM c1
INTO @tbname
WHILE
@@FETCH_STATUS <> -1
BEGIN
SET @isql = ''
DECLARE c2 CURSOR FOR
SELECT columnname
FROM #tmp
WHERE tablename = @tbname
OPEN c2
FETCH NEXT FROM c2
INTO @clname
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @isql = @isql + ' ISNULL(DATALENGTH(' + @clname + '),0)+'
FETCH NEXT FROM c2
INTO @clname
END
CLOSE c2
DEALLOCATE c2
SELECT @len = LEN(@isql)
SELECT @isql = 'UPDATE #rowlength SET MaxAactualLength = (SELECT ISNULL(MAX( ' + LEFT(@isql,@len - 1) + '),0) FROM ' + @tbname + ')WHERE TableName = ' + QUOTENAME(@tbname,'''')
-- PRINT @isql
EXEC sp_executesql @isql
FETCH NEXT FROM c1
INTO @tbname
END
CLOSE c1
DEALLOCATE c1
SELECT TableName, DefinedRowLength, MaxActualLength FROM #rowlength
Greeting Markus
[font="Verdana"]Markus Bohse[/font]
August 2, 2007 at 6:06 am
ok i see the difference.... I was concentrating on max field size, , so my version would simply find the largest used column size a for the MaxActualLength.each max size would obviously come from different rows of data.
Your script is identifying the largest single row of data, I see that now.
Lowell
October 1, 2007 at 3:15 am
It's working fine, but takes very long time to execute.
October 1, 2007 at 4:25 am
Yes,
on large databases it will take a while, to get the results. In my case that was not such an issue, but any improvements on the script(s) are welcome.
Markus
[font="Verdana"]Markus Bohse[/font]
October 1, 2007 at 9:27 pm
What were you testing Jeff?
October 1, 2007 at 9:31 pm
creating and deleting replys, it looks like from my forum emails.
Lowell
October 2, 2007 at 4:48 am
I should have quoted his message... we would have a proof of something :P.
No we are not hearing voices in our head... That's what they tell me :hehe:.
October 2, 2007 at 7:59 am
Not sure what's wrong here? Where are things crashing?
October 2, 2007 at 8:07 am
Jeff was just fiddling with it...add a reply, emails get sent, delete teh reply, the link in the email can't find the posted reply. expected behavior.
WHY he was testing, we dunno. just trying to break the site i guess.
Lowell
October 2, 2007 at 8:31 am
Nothing wrong here Steve... maybe this is the thread I used to contact you about the subquery error.
October 2, 2007 at 6:25 pm
No... not true...
1. Editing a post still triple spaces the entire post.
2. Trying to post with [Code] still results in a triple spaced mess that doesn't obey leading spaces.
3. Still need to be a rocket scientist to change color of code because the color button in the menu doesn't work.
4. Font's still don't work.
5. Preview still isn't WYSIWYG.
6. Still no listing of the post you're responding to on the same page and the Post Reply window.
Haven't bothered to look for other stuff... these things have been on the to-do list for quite a while... basically, no improvements have been made to editing/creating posts...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 2, 2007 at 6:29 pm
This one got me too Jeff : If you scroll down under the Edit attachements botton you will see a small + sign at the right on the screen. When you hit that, the posts will be visible.
As for the colors, I agree that the old editor was more friendly to use, but I still can make due with that for a while. The bugs that are now gone are already a big relief. There's still some work to be done but this is definitly a great start.
October 2, 2007 at 7:51 pm
the thing about color is that you MUST enter the double quotes to get it to work.
input = #0000FF is BAD
input = "#0000FF" is GOOD
Lowell
October 2, 2007 at 7:58 pm
Still not something we should be dealing with. When I click on the red color, I want to freaking thing to turn red. I don't want to have to find an hex color table and figure out that I need to put quotes around it to make it work. What happened to the simple point and click GUI :w00t:?
October 2, 2007 at 10:48 pm
Lowell (10/2/2007)
the thing about color is that you MUST enter the double quotes to get it to work.input = #0000FF is BAD
input = "#0000FF" is GOOD
Heh... thanks Lowell... but I knew that...
I talking about what Remi is talking about... the color palatte used to work... even on this new forum!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply