Recently I was asked to write a query that would us us know the sizes of all user tables in a database. Thought of sharing the script; this might be useful to people who would like to know the amount of space being used by each of the tables in a particular database. Here goes the script:
OPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableName, @SchemaNameWHILE (@@Fetch_Status >= 0) BEGIN SET @TableFullName = @SchemaName + '.' + @TableName INSERT #TempTable EXEC sp_spaceused @TableFullName UPDATE #TempTable SET TableName = @TableFullName WHERE TableName = @TableName FETCH NEXT FROM tableCursor INTO @TableName, @SchemaName ENDCLOSE TableCursorDEALLOCATE TableCursor
Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.
That's a useful script, Suhas !!
Good to see you "Blogging"....will look forward to many useful post from you.
How about this?
DECLARE @TableVar TABLE
INSERT INTO @TableVar
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT * FROM @TableVar
But why write a script when we can use the built in SQL report - "Disk usage by table"?
Thanks for the comments. The script written by you is perfect for user databases; try running both scripts against the master or the msdb database, you will see the difference.
The reason why I had written this script is - my customer needed to have a SQL job, that would email the sizes of all user tables of a particular database to a particular operator. I guess thats where you would really need the script, right?
I want to create a new Database on my production.
I want to know how to determine the initial Database size.
Can you pls suggest?
There is no direct way to get an initial database size directly. You will have to do it on a per table basis, and then sum up to get the total database size.
For a table, since you know the table structure, you should be able to easily determine the number of bytes required to hold 1 row of data. Each data page in SQL Server can hold 8060 bytes of data, so divide 8060 by the size of each row, and you will have the number of rows per page. Take the integer value only.
Now, you will need to estimate the number of rows in the table. Divide the estimated number of rows by the number of rows per page, and you have the number of pages required to hold the data for that table. Multiply that by 8KB and you have the space required for storing data in that table in KB.
You will need to, similarly, estimate space requirement for the indexes on that table. Sum these up, and you have the total space requirement for the table. To be on the safe side, add a 20% buffer.
Follow the above steps for all the other tables, and finally sum the values up. This is the space required for the database.
Hope this helps.
Suhas De and Raj Kaimal, thank you for teh simple code :)