How to determine Size & rows in database tables ?

Dear All,
Today I got task to determine the table size which table taking how much space on disk.
So I have write the following script to determine that I hope It will help you out somewhere.
Just checkout
BEGIN TRY
DECLARE @tblName AS VARCHAR(200)
DECLARE @tblTest TABLE ([Name] VARCHAR(100),
[rows ] INT,
reserved VARCHAR(100),
data VARCHAR(100),
index_Size VARCHAR(100),
Unused VARCHAR(100))
DECLARE cur_Test Cursor FOR SELECT [name] FROM sys.objects WHERE [type] = ‘U’
OPEN cur_Test
FETCH NEXT FROM cur_test INTO @tblName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @tblTest
EXECUTE sp_spaceUsed @tblName
FETCH NEXT FROM cur_test INTO @tblName
END
CLOSE cur_Test
DEALLOCATE cur_Test
SELECT * FROM @tblTest ORDER BY rows desc
END TRY
BEGIN CATCH
SELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_MESSAGE()
END CATCH

Enjoy Programming
Thanks
Rajat Jaiswal

Advertisements

2 thoughts on “How to determine Size & rows in database tables ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s