Table width script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Table width script

Author

Topic

scottpt
Posting Yak Master

186 Posts

Posted - 2005-04-26 : 09:40:38

Does anyone have a script to run through a DB and figure out the max width of a table? Yes this is to clean up another well designed app:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-04-26 : 09:57:17

select object_name(id) from sysindexes where used in (select max(used) from sysindexes )

Madhivanan

Failing to plan is Planning to fail

scottpt
Posting Yak Master

186 Posts

Posted - 2005-04-26 : 10:13:24

I am looking for a width script, not size. IE making sure all rows are less than 8060. THe main issue I am having is how do you calculate the amount of space a numeric,decimal uses up?

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-26 : 11:11:47

Does this help

USE Northwind
GO

CREATE TABLE Test(Col1 varchar(8000),Col2 varchar (1000))
GO

SELECT o.name AS TableName, SUM(length) AS RowSize
FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id
WHERE o.xtype = 'U'
GROUP BY o.name
GO

DROP TABLE Test
GO

Andy

Beauty is in the eyes of the beerholder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources