Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

Check the size of each table in your Database

Author: DevASP
Download Source Code : 483_CheckSpace.zip

This article is about how you can check the size of each table in your database.

Steps you will do. Open the Enterprise Manager and in your database create a new store procedure with any name that you wish. Copy and paste the following code in the body of store procedure save the store procedure and open the query analyzer. Call the store procedure and see the results. You will see the name of each table in your data base, number of rows in the table and size of the table in the data base, etc. The store procedure is as below:

 

CREATE PROCEDURE CheckSpace

 

 

AS

 

 

            BEGIN

           

            DECLARE @tablename sysname

            DECLARE tables_cursor CURSOR

            FOR

            SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name

            OPEN tables_cursor

            FETCH NEXT FROM tables_cursor INTO @tablename

            WHILE (@@FETCH_STATUS <> -1)

           

           

                BEGIN

                        EXEC ('sp_spaceused ' + @tablename)

            FETCH NEXT FROM tables_cursor INTO @tablename

            END

            CLOSE tables_cursor

            DEALLOCATE tables_cursor

 

           

            END

GO

 

 

 

The script of the store procedure is also attached with this article for help.

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to decrypt value in SQL Server.

Disclaimer - Privacy
© 2002-2017 DevASP.net