SQL Server table and schema space usage-http://www.51myit.com/thread-24669-1-1.html

The Administrator of the SQL Server database often has a headache and the hard disk space is too small. He often needs to write a lot of queries to find out which tables and modes occupy a large amount of hard disk space, this allows regular cleaning of these tables and modes. This article describes how to query the system directory to determine the disk space usage. It helps the SQL Server database administrator identify the tables and modes that occupy the maximum space, so as to archive old data, clear unnecessary data. The scripts listed in this article apply to SQL Server 2005 and SQL Server 2008 ctp5.

Step 1: log on to the SQL server instance.

Step 2: Determine the database for which you want to obtain the space usage information and navigate to the database, as shown in the following figure (see figure 2)

Use adventureworks

Go

Step 3.1: Add the following code to the query window and execute it (see figure 3 ).
Begin try
Select
(Row_number () over (order by a3.name, a2.name) % 2 as L1,
A3.name as [schemaname],
A2.name as [tablename],
A1.rows as row_count,
(A1.reserved + isnull (a4.reserved, 0) * 8 as reserved,
A1.data * 8 as data,
(Case when (a1.used + isnull (a4.used, 0)> a1.data then
(A1.used + isnull (a4.used, 0)-a1.data else 0 end) * 8 as index_size,
(Case when (a1.reserved + isnull (a4.reserved, 0)> a1.used then
(A1.reserved + isnull (a4.reserved, 0)-a1.used else 0 end) * 8 as unused
From
(Select
PS. object_id,
Sum (
Case
When (PS. index_id <2) then row_count
Else 0
End
) As [rows],
Sum (PS. reserved_page_count) as reserved,
Sum (
Case
When (PS. index_id <2) then
(PS. in_row_data_page_count + PS. lob_used_page_count + PS. row_overflow_used_page_count)
Else (PS. lob_used_page_count + PS. row_overflow_used_page_count)
End
) As data,
Sum (PS. used_page_count) as used
From SYS. dm_db_partition_stats PS
Group by PS. object_id) as A1
Left Outer Join
(Select
It. parent_id,
Sum (PS. reserved_page_count) as reserved,
Sum (PS. used_page_count) as used
From SYS. dm_db_partition_stats PS
Inner join SYS. internal_tables it on (it. object_id = ps. object_id)
Where it. internal_type in (202,204)
Group by it. parent_id) as A4 on (a4.parent _ id = a1.object _ id)
Inner join SYS. all_objects A2 on (a1.object _ id = a2.object _ id)
Inner join SYS. schemas A3 on (a2.schema _ id = a3.schema _ id)
Where a2.type <>'s 'and a2.type <> 'it'
Order by a3.name, a2.name
End try
Begin catch
Select
-100 as L1
, 1 as schemaname
, Error_number () as tablename
, Error_severity () as row_count
, Error_state () as reserved
, Error_message () as data
, 1 as index_size
, 1 as unused
End catch

When executing the above code, we can see the table space usage report (see figure 4 ).

Step 3.2: make some modifications to the above script. Use the sum and count functions and group them by clauses to display the usage of the mode space. Add the following code to the query window and run it (see figure 5 ).
Begin try
Select
(Row_number () over (order by a3.name, a2.name) % 2 as L1,
A3.name as [schemaname],
Count (a2.name) as numberoftables,
Sum (a1.rows) as row_count,
Sum (a1.reserved + isnull (a4.reserved, 0) * 8) as reserved,
Sum (a1.data * 8) as data,
Sum (case when (a1.used + isnull (a4.used, 0)> a1.data then
(A1.used + isnull (a4.used, 0)-a1.data else 0 end) * 8) as index_size,
Sum (case when (a1.reserved + isnull (a4.reserved, 0)> a1.used then
(A1.reserved + isnull (a4.reserved, 0)-a1.used else 0 end) * 8) as unused
From
(Select
PS. object_id,
Sum (
Case
When (PS. index_id <2) then row_count
Else 0
End
) As [rows],
Sum (PS. reserved_page_count) as reserved,
Sum (
Case
When (PS. index_id <2) then
(PS. in_row_data_page_count + PS. lob_used_page_count + PS. row_overflow_used_page_count)
Else (PS. lob_used_page_count + PS. row_overflow_used_page_count)
End
) As data,
Sum (PS. used_page_count) as used
From SYS. dm_db_partition_stats PS
Group by PS. object_id) as A1
Left Outer Join
(Select
It. parent_id,
Sum (PS. reserved_page_count) as reserved,
Sum (PS. used_page_count) as used
From SYS. dm_db_partition_stats PS
Inner join SYS. internal_tables it on (it. object_id = ps. object_id)
Where it. internal_type in (202,204)
Group by it. parent_id) as A4 on (a4.parent _ id = a1.object _ id)
Inner join SYS. all_objects A2 on (a1.object _ id = a2.object _ id)
Inner join SYS. schemas A3 on (a2.schema _ id = a3.schema _ id)
Where a2.type <>'s 'and a2.type <> 'it'
Group by a3.name
Order by a3.name
End try
Begin catch
Select
-100 as L1
, 1 as schemaname
, Error_number () as tablename
, Error_severity () as row_count
, Error_state () as reserved
, Error_message () as data
, 1 as index_size
, 1 as unused
End catch