Sunday, November 2, 2014

Creating a Stored procedure to get sizes of all the tables in a database using sp_spaceused

CREATE PROCEDURE pGetAllTableSizes
AS
BEGIN

Declare @tableName varchar(100)

Declare tblCursor Cursor
For
select [name] from dbo.sysobjects
where ObjectProperty(id,N'IsUserTable')=1
For read only

Create table #tmpTable
(
tableName varchar(100),
numberofrows varchar(100),
reservedsize varchar(100),
datasize varchar(100),
indexsize varchar(100),
unusedsize varchar(100)
)

open tblCursor

Fetch Next from tblCursor into @tableName
While (@@FETCH_STATUS>=0)
Begin
Insert #tmpTable
exec sp_spaceused @tableName

Fetch Next from tblCursor into @tableName
End
close tblCursor 
deallocate tblCursor 

select tableName,numberofrows, reservedsize , datasize, indexsize, unusedsize from #tmpTable
order by reservedsize desc

drop table #tmpTable

END
GO



Understanding SQL Server sp_spaceused for database size

sp_spaceused:

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

Syntax:
sp_spaceused [[ @objname = ] 'objname' ] [,[ @updateusage = ] 'updateusage' ]

Arguments:

@objname=

'objname'Is the name of the table, indexed view, or queue for
which space usage information is requested.

Permission to execute sp_spaceused is granted to the public role.

@updateusage=

'updateusage'Indicates DBCC UPDATEUSAGE should be run to update
space usage information.

Only members of the db_owner fixed database role can specify 
the @updateusage parameter.

Result Sets:

1) database_name
   Name of the current database

2) database_size
  i) Size of the current database in megabytes. 
 ii) database_size includes both data and log files.

3) unallocated space
  i) Space in the database that has not been reserved for database
    objects.

4) reserved
 Total amount of space allocated by objects in the database.
 Size in Kilobytes

5) data

   Total amount of space used by data.  Size in Kilobytes

6) index_size

   Total amount of space used by indexes. Size in Kilobytes

7) Unused
 Total amount of space reserved for objects in the database,but 
 not yet used. Size in Kilobytes


Saturday, May 3, 2014

Windows 8 OS Tips and Tricks

This video tutorial is to guide with some of the options available in Windows 8 Operating System to easily work with.

Its really easy to work in Windows 8, if you are aware of these features.




Hope this helps.

Thanks for watching...