Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My experiences and references in SQL server
Browse by Tags · View All
SQL Server 14
#SQLServer 14
SQL Scripts 13
#TSQL 6
TSQL 6
SQL Serevr - Issues and Resolutions 3
SQL Server - Best Practises 3
SQL server - Misconceptions 3
SQL server - Statistics 2
SQL Server - Wait stats and Queues 2

Archive · View All
October 2011 8
March 2011 7
April 2011 4
May 2011 3
November 2011 3
December 2010 3
December 2011 2
June 2008 2
February 2011 2
February 2012 1

SQLZealot's Blog

To find out the size occupied by indexes in a table

Oct 18 2011 1:40AM by Latheesh NK   

The below procedure will return the size occupied by the indexes in a table. This will be helpful to measure the size before and after the maintenance activities.

Create Proc SQLZealot_FindIndexSize( @OBJECT_NAME  VARCHAR(255) )
 As
 Begin
 DECLARE @temp         TABLE
   (
    indexID BIGINT,
    objectId BIGINT,
    index_name NVARCHAR(MAX),
    used_page_count BIGINT,
    pages BIGINT
   )
 --Insert into temp table
 INSERT INTO @temp
 SELECT P.index_id,
     P.OBJECT_ID,
     I.name,
     SUM(used_page_count),
     SUM(
      CASE 
     WHEN (p.index_id < 2) THEN (
        in_row_data_page_count + lob_used_page_count + 
        row_overflow_used_page_count
       )
     ELSE lob_used_page_count + row_overflow_used_page_count
      END
     )
 FROM   sys.dm_db_partition_stats P
     INNER JOIN sys.indexes I
    ON  I.index_id = P.index_id
    AND I.OBJECT_ID = P.OBJECT_ID
 WHERE  p.OBJECT_ID = OBJECT_ID(@OBJECT_NAME)
 GROUP BY
     P.index_id,
     I.Name,
     P.OBJECT_ID;
 SELECT index_name INDEX_NAME,
     LTRIM(
      STR(
       (
        CASE 
       WHEN used_page_count > pages THEN (used_page_count - pages)
       ELSE 0
        END
       ) * 8,
       15,
       0
      ) + ' KB'
     ) INDEX_SIZE
 FROM   @temp T
End

/*-----Usage:*/
exec SQLZealot_FindIndexSize 'mas_user'


Republished from SQL - My Best Friend [58 clicks].  Read the original version here [32134 clicks].

Latheesh NK
55 · 4% · 1125
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"To find out the size occupied by indexes in a table" rated 5 out of 5 by 3 readers
To find out the size occupied by indexes in a table , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]