January 16, 2014 at 9:15 pm
Comments posted to this topic are about the item List table space usage and row counts
January 17, 2014 at 5:46 am
Many thanks, this is much cleaner than my previous code.
I did add
AND INDEX_ID = 0
to the WHERE clause to keep my name list unique and not show indexes. I mainly needed the table info.
January 17, 2014 at 7:12 am
I would use AND INDEX_ID <= 1 when index space is not needed.
Index ID 0 is for heap tables and Index ID 1 is for tables with clustered index.
January 17, 2014 at 8:45 am
tskelley (1/17/2014)
Many thanks, this is much cleaner than my previous code.I did add
AND INDEX_ID = 0
to the WHERE clause to keep my name list unique and not show indexes. I mainly needed the table info.
I added
AND INDEX_ID in (0,1)
so it shows both tables with and without clustered indexes. (0=heap, 1=clustered index).
January 17, 2014 at 11:11 am
As mentioned here there are indexes shown in the results. Would it make sense if I were to group by the "Name" and do a SUM(reserved_page_count * 8) as space_used_kb to get the total space used for the table (including indexes), and do a MAX(row_count) as row_count to get the number of records?
January 17, 2014 at 7:25 pm
mikeg13 (1/17/2014)
As mentioned here there are indexes shown in the results. Would it make sense if I were to group by the "Name" and do a SUM(reserved_page_count * 8) as space_used_kb to get the total space used for the table (including indexes), and do a MAX(row_count) as row_count to get the number of records?
Yes, I can see the value of having sum. I had made another script and posted on my blog, check it out if you're interested. Thank you for your suggestion.
January 18, 2014 at 1:40 pm
The total space used in version 2 doesn't match any of the space used values in version 1. The values in version 1 seem to be considerably higher in all cases. Is this because something is being eliminated from the results calculations? The row counts match.
Dana
January 18, 2014 at 6:55 pm
danaanderson (1/18/2014)
The total space used in version 2 doesn't match any of the space used values in version 1. The values in version 1 seem to be considerably higher in all cases. Is this because something is being eliminated from the results calculations? The row counts match.Dana
Thanks Dana for pointing it out, I made a mistake. It has been fixed now.
January 18, 2014 at 10:29 pm
Louis&Sql (1/18/2014)
danaanderson (1/18/2014)
The total space used in version 2 doesn't match any of the space used values in version 1. The values in version 1 seem to be considerably higher in all cases. Is this because something is being eliminated from the results calculations? The row counts match.Dana
Thanks Dana for pointing it out, I made a mistake. It has been fixed now.
You're welcome. Ah, I should have noticed the missing [*8] in the calculations.
Dana
January 20, 2014 at 6:55 pm
Please excuse my ignorance.
I need to change this how?
I get the error
Incorrect syntax near 'o'.
I tried changing SCHEMA_NAME to the name of the schema for the particular database. That didn't help.
January 20, 2014 at 8:11 pm
Carolyn S. White (1/20/2014)
Please excuse my ignorance.I need to change this how?
I get the error
Incorrect syntax near 'o'.
I tried changing SCHEMA_NAME to the name of the schema for the particular database. That didn't help.
What happens if you run this script under admin privilege?
January 20, 2014 at 8:24 pm
The same thing.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'o'.
I'm trying this with sqlserver 2012
January 20, 2014 at 8:49 pm
January 20, 2014 at 8:53 pm
I literally copied and pasted the code verbatim from your post as a new query on the database I wanted to get the results on.
Does it work on an entire database? or is it for a single table?
I'm literally ignorant on this.
If it is too much trouble to educate me, it is ok to say so.
January 20, 2014 at 9:02 pm
Carolyn S. White (1/20/2014)
I literally copied and pasted the code verbatim from your post as a new query on the database I wanted to get the results on.Does it work on an entire database? or is it for a single table?
I'm literally ignorant on this.
If it is too much trouble to educate me, it is ok to say so.
Actually I'm interested in knowing why it failed.
To answer your question - Yes, it works on entire database
And it works with SQL Server 2012.
You don't need to change anything to be able to run this script. It should work on its own.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply