x

Regarding particular table and index sizes,rowcounts?

I need a script for particular table indexes, type of indexes in that table, rowcounts, sizes of indexes etc...

more ▼

asked Dec 29, 2011 at 09:16 AM in Default

avatar image

Naren
27 17 18 20

hi everyone anyboday have an idea how to get the script for above requirement...

Dec 29, 2011 at 07:41 PM Naren
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Below is the script which may help you. Please note that I have added filegroup name, filename as well, which was your requirement in another question. But please, quite a few people have asked you, to review the answers given to your questions. Otherwise, you may stop getting help from some of our community

 /* UNCOMMENT FOLLOWING TWO LINES IF YOU NEED TO KNOW ABOUT A PARTICULAR TABLE */
 --DECLARE @tablename NVARCHAR(200) --UNCOMMENT THIS IF YOU
 --SET @tablename = N'YOURTABLENAME'
 /*============================================================================*/
 
 SELECT  fg.name AS [FileGroup]
 ,        df.NAME AS [FileName]
 ,       sc.name + '.' + ta.name AS TableName
 ,       i.name AS IndexName
 ,       i.[type_desc] AS IndexTypeDesc
 ,       8 * SUM(a.used_pages) AS 'Indexsize(KB)'
 ,       SUM(ps.[row_count]) AS NumberOfRows
 FROM    sys.indexes AS i
         JOIN sys.partitions AS p
         ON p.OBJECT_ID = i.OBJECT_ID
            AND p.index_id = i.index_id
         JOIN sys.allocation_units AS a
         ON a.container_id = p.partition_id
         JOIN sys.tables ta
         ON [p].[object_id] = [ta].[object_id]
         JOIN sys.dm_db_partition_stats ps
         ON ta.object_id = ps.object_id
         JOIN sys.schemas sc
         ON ta.schema_id = sc.schema_id
         JOIN sys.filegroups AS fg
         ON fg.data_space_id = i.data_space_id
         JOIN sys.[database_files] AS DF ON [i].[data_space_id] = [DF].[data_space_id]
 
 --WHERE ta.[name] = @tablename -- UNCOMMENT THIS LINE IF YOU NEED TO KNOW ABOUT A PARTICULAR TABLE
 
 GROUP BY fg.name
 ,        df.NAME
 ,       sc.name
 ,       ta.name
 ,       i.index_id
 ,       i.name
 ,        i.[type_desc]
 ORDER BY sc.name
 ,       ta.name
 ,       i.index_id
more ▼

answered Dec 29, 2011 at 10:42 PM

avatar image

Usman Butt
13.9k 6 13 21

(comments are locked)
10|1200 characters needed characters left

Please get the required query from given below link-

http://sqlshares.blogspot.com/search/label/SQL%20Queries

more ▼

answered Dec 29, 2011 at 08:47 PM

avatar image

Sharma
1.3k 88 91 95

Thanks for giving its working fine i want to add table size also to that output how I can?

Dec 29, 2011 at 10:17 PM Naren

SP_SPACEUSED 'TABLE_NAME'

Jan 01, 2012 at 09:32 PM Sharma
(comments are locked)
10|1200 characters needed characters left

The best way is right click on the database go to reports and select Disk Usage by Table.

more ▼

answered Dec 30, 2011 at 01:51 AM

avatar image

deepkalmeida
26 1 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017
x393
x117
x94

asked: Dec 29, 2011 at 09:16 AM

Seen: 1512 times

Last Updated: Dec 29, 2011 at 10:47 PM

Copyright 2016 Redgate Software. Privacy Policy