In order to determine the space used by a database in SQL Server we use the following command
The above command, on execution gives two tables as output. First table shows the information on
database_name Name of the current database.
database_size Size of the current database in megabytes. database_size includes both data and log files.
unallocated space Space in the database that has not been reserved for database objects.
The second table gives the information on
reserved Total amount of space allocated by objects in the database
data Total amount of space used by data
index_size Total amount of space used by indexes
unused Total amount of space reserved for objects in the database, but not yet used
In case if we want to assess the space for any table in the database, we pass the name of the table as parameter. For example:
exec sp_spaceused @objname='test_nodrop.dbo.employee'
@objname is a qualified or nonqualified name of the table, indexed view, or queue for which space usage information is requested.
Output of the above command will be:
Name Name of the object for which space usage information was requested.
rows Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue
reserved Total amount of reserved space for objname
data Total amount of space used by data in objname
index_size Total amount of space used by indexes in objname
unused Total amount of space reserved for objname but not yet used
Please note that finding the name of each table in the database would be a tiresome job. Instead, I use the following script, which will provide me the size used by all the tables in the database
declare @tabname varchar(400) ,@counts int ,@rowcounts int; declare @tab1 table( sno int identity(1,1) ,tablename varchar (200) ,schemaname varchar(200) ,twopartname varchar(400) ); declare @space_table table ( tablename varchar(200) , row_count int , reserved varchar(50) , data varchar(50) , index_size varchar(50) , unused varchar(50) ); insert into @tab1 (tablename, schemaname, twopartname) select st.name , ss.name , ss.name + '.' + st.name FROM sys.tables st INNER JOIN sys.schemas ss ON ( st.schema_id = ss.schema_id ); select @counts=count(sno) from @tab1 select @rowcounts=1 while (@rowcounts <= @counts ) begin select @tabname=twopartname from @tab1 where sno=@rowcounts select @tabname = replace(@tabname, '[',''); select @tabname = replace(@tabname, ']',''); insert into @space_table exec sp_spaceused @tabname, false ; select @rowcounts = @rowcounts + 1 end select t1.* , t2.schemaname from @space_table t1 inner join @tab1 t2 on (t1.tablename = t2.tablename ) order by schemaname,tablename;
I will keep posting all possible other details very soon