Total length of columns ina table.

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

select * from information_schema.columns where table_name = 'Customers'

will give you the max length of the values, but the issue is that each row can have different data. What exactly are you planning on using this for?

ishaan99 SSCertifiable Points: 6261 More actions · Answer 2

Basically i want to know how much total bytes(by all columns) is occupied in a table . i woked it out anyway. Thanks so much for the help..

select sum(length) from syscolumns

where id in (select id from sysobjects

where name = )

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

Space is row length * rows.

So you can work out the max for a row, but it doesn't give you space used by a table. There are other scripts that can help you calculate that.

noeld SSC Guru Points: 96590 More actions · Answer 4

Hopefully this is what you want:

SELECT db_name(database_id) as db

, object_name(object_id,database_id) as table_name

, min_record_size_in_bytes

, avg_record_size_in_bytes

, max_record_size_in_bytes

FROM

sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL , 'DETAILED')

WHERE db_name(database_id) NOT IN ('master','model','msdb','tempdb','distribution','ReportServer', 'ReportServerTempDB')

AND index_id = 1 -- clustered;

ORDER BY max_record_size_in_bytes desc

* Noel