December 14, 2004 at 2:27 pm
Hi all,
Is there any stored procedure, view or other tool which shows the maximum record length for given table? "Maximum" means the number of bytes occupied by record when the variable length fields are totally filled. The obvious reason is to check the 8060 limit.
TIA
Kuba Jerzak
December 14, 2004 at 2:56 pm
I don't have a script that does that. But one thing you could do if this is a one time shot is to script out the table in QA.
Then take that script and change the beginning to : Declare @MyTable table (table script without the constraints and indexes) and execute the script. If you don't get any warning then you are ok.
December 15, 2004 at 3:38 am
A quick way to get this is to use sp_columns, and then copy the size column into excel
December 15, 2004 at 5:29 am
Do a search in the script section here. You should find something useful.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 15, 2004 at 6:13 am
If you just want to sum the maximum lengths of all the column in a table, you could something like this:
DECLARE @tableName varchar(128)
SET @tableName = '<your table>'
SELECT Sum(c.length) as MaxDataBytes
FROM sysobjects t JOIN syscolumns c ON t.id = c.id
WHERE t.name = @tableName
December 15, 2004 at 6:36 am
Just a variation on mkeast's answer (so you get all tables) :
SELECT O.Name, Sum(C.length) as MaxDataBytes
FROM dbo.sysobjects O JOIN dbo.syscolumns C ON O.id = C.id
where O.XType = 'U' and O.status >= 0
Group by O.id, O.Name
Order by MaxDataBytes Desc
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply