There are often times that I need to do checks on the space used by databases on the server, and in particular, the tables in those databases. This is a script that I quickly put together that will return the following information for each of the tables in the database on which the script is run. The following information is returned:
- Table Name
- Row Count
- Reserved Space (KB)
- Data space used (KB)
- Index size (KB)
- Unused space (KB)
The above information is gathered using sp_spaceused. Now I’m sure that there are a number of other ways to get this information, but this was quick and easy to produce and use. If you have anything that you’d like to contribute, I’d love to hear it.
1: /***
2: Script to identify the space used by each table in a database
3: Information returned includes:
4: - Table name
5: - Record Count
6: - Reserved Space (KB)
7: - Data Size (KB)
8: - Index Size (KB)
9: - Unused Space (KB)
10: ***/
11:
12: /* Create a table variable to hold the values returned by sp_spaceused */
13: DECLARE @Results TABLE
14: (
15: RowNum INT IDENTITY(1,1)
16: , TableName VARCHAR(200)
17: , Records INT
18: , ReservedSpace VARCHAR(100)
19: , DataSpace VARCHAR(100)
20: , IndexSizeSpace VARCHAR(100)
21: , UnUsedSpace VARCHAR(100)
22: , CheckDateTime DateTime DEFAULT GETDATE()
23: )
24:
25: /* Declare a variable to hold the Object Names */
26: DECLARE @ObjectName VARCHAR(200)
27:
28: /*
29: Declare a cursor to loop through each of the tables in the database
30: and return their space used information.
31: */
32: DECLARE TableCursor CURSOR
33: FOR
34: SELECT
35: ObjectName = TABLE_SCHEMA + '.' + TABLE_NAME
36: FROM
37: INFORMATION_SCHEMA.TABLES
38: WHERE
39: TABLE_TYPE = 'BASE TABLE'
40:
41: OPEN TableCursor
42:
43: FETCH NEXT FROM TableCursor
44: INTO @ObjectName
45:
46: WHILE @@FETCH_STATUS = 0
47: BEGIN
48:
49: /* Execute sp_Spaceused for each table and load the results into the table variable */
50: INSERT INTO @Results
51: (
52: TableName
53: , Records
54: , ReservedSpace
55: , DataSpace
56: , IndexSizeSpace
57: , UnUsedSpace
58: )
59:
60: /*
61: Uncomment the @updateusage option if you want to ensure your stats are up-to-date
62: However, this may take some time depending on the size of your database tables.
63: It is set to 'false' in sp_spacedused by default.
64: */
65: EXEC sp_spaceused @ObjectName
66: --, @updateusage = 'true'
67: ;
68:
69: FETCH NEXT FROM TableCursor
70: INTO @ObjectName
71: END
72:
73: CLOSE TableCursor
74: DEALLOCATE TableCursor
75:
76: /*
77: Get the list of tables and their associated space usage data.
78: Update to your preferred format
79: */
80: SELECT
81: TableName
82: , Records
83: , ReservedSpace = CAST(SUBSTRING(ReservedSpace, 1, LEN(ReservedSpace)-3) AS INT)
84: , DataSpace = CAST(SUBSTRING(DataSpace, 1, LEN(DataSpace)-3) AS INT)
85: , IndexSizeSpace = CAST(SUBSTRING(IndexSizeSpace, 1, LEN(IndexSizeSpace)-3) AS INT)
86: , UnUsedSpace = CAST(SUBSTRING(UnUsedSpace, 1, LEN(UnUsedSpace)-3) AS INT)
87: FROM @Results
88: ORDER BY
89: TableName
Copy. Paste. Enjoy.
**UPDATE – 2011/09/14**
My friend Jim McLeod (Blog | Twitter) has informed me that there is a better way to get the space used and row count details for objects in your database. His script utilises the sysindexes table, which means that this is compatible across a wider set of SQL Server versions including 2000/2005/2008. His original script appears in the comments below. I have reproduced it here with a little formatting, commenting and an extra table to restrict the results to just *user* tables:
1: /***
2: This script will generate a list of User tables
3: and their associated space used and row count details.
4: ***/
5:
6: SELECT
7: SchemaName = ss.name
8: , ObjectName = OBJECT_NAME(si.id)
9: , ReservedMB = reserved * 8/1024.0
10: , DataMB = dpages * 8/1024.0
11: , RecCount = rowcnt
12:
13: FROM
14: sysindexes si
15: INNER JOIN sys.objects so ON si.id = so.object_id
16: INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id
17: WHERE
18: /* Limit the resultset to bring back Heaps & Clustered indexes only
19: (Heap = 0, Clustered Index = 1)
20: */
21: indid < 2
22: /* We only want to see the 'user' tables at this stage.
23: You can include System tables by adding 'S' to the xtype clause
24: */
25: AND type = 'U'
26: ORDER BY
27: SchemaName,
28: ObjectName
I’ve left the first script in the post for comparison. Thanks Jim!
So I have recently been having a look into 
