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 |
|
ds9
Starting Member
38 Posts |
 Posted - 2007-12-10 : 10:53:23
|
| HiI have a database with 150 tables. I want to make a query ranking those tables by table size and/or number of rows.Is this possible?Many thanksds9 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-10 : 10:55:09
|
Yes.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
ds9
Starting Member
38 Posts |
Posted - 2007-12-10 : 11:01:39
|
Ok Ok my post was incomplete, fair enough  So let me add to my post:Can any one (ie, Peso  ) please tell me how to do it?CheersJR |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-10 : 11:29:41
|
[code]SELECT o.NAME AS TableName, i.ROWS AS Items, SUM(COALESCE(NULLIF(c.LENGTH, -1), 16)) AS RowSizeFROM SYS.SYSOBJECTS AS oINNER JOIN SYS.SYSINDEXES AS i ON i.ID = o.IDINNER JOIN SYS.SYSCOLUMNS AS c ON c.ID = o.IDWHERE o.XTYPE = 'u' AND i.INDID < 2GROUP BY o.NAME, i.ROWSORDER BY o.NAME[/code]The trick here is that columns with size -1 are stored externally (IMAGE, TEXT, NTEXT) and only a pointer of 16 bytes is stored in the table.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
ds9
Starting Member
38 Posts |
Posted - 2007-12-11 : 11:50:48
|
| Thanks!The rowsize is in Bytes? The row count matches the properties field of the table (the one I've tested), but I can't match the table size showing in that properties window.Regarding the -1 sized columns it's ok, as i don't have those data types in the tables.Thanks againds9 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
sqlsquirrel
Starting Member
21 Posts |
|
|
|
|
|
Query DB for table sizes/number of rows