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.
| Author |
Topic |
|
sqldba20
Posting Yak Master
183 Posts |
 Posted - 2007-10-03 : 19:24:06
|
| Folks:I would like to know the total length of data type in a table. I ran the following query. Will this give me the correct information? I also ran sp_columns <table name> and it too give the length. But There is a difference in the numbers. Am I doing something wrong and which is the correct the query or sp_column.select sum(length) from syscolumns where id in (select id from sysobjectswhere name = 'XYZABC')sp_columns XYZABCThanks ! |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-10-03 : 19:30:34
|
| And how do you intend to use the info?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2007-10-03 : 20:49:02
|
| To find whether we have reached MAX on the LIMITS... |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-10-03 : 21:07:03
|
| Can you explain more.. MAX LIMITS of what?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2007-10-03 : 21:31:30
|
| max row size limits? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-03 : 22:04:35
|
| For varchars, you can use DATALENGTH function and then do a MAX on that. You would do this against the actual table and not against syscolumns like in your example. I typically just use the varchar size rather than the actual data size though, since you are supposed to plan the sizes according to what data you'd expect.You'll also need to look up the different data types in BOL to see what the storage size. For instance, int is 4 and bigint is 8. Once you have all of this information, add them up to see what the row size is.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
Total Length of Data Type in a Table