In order to determine the space used by a database in SQL Server we use the following command

exec sp_spaceused

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

 
Set your Twitter account name in your settings to use the TwitterBar Section.
%d bloggers like this:
Bitnami