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

Naren gravatar image

Naren
27 17 18 19

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

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(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

Amardeep gravatar image

Amardeep
1.3k 86 88 89

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 Amardeep
(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

deepkalmeida gravatar image

deepkalmeida
26 1 1 1

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1945
x279
x83
x83

asked: Dec 29, 2011 at 09:16 AM

Seen: 1203 times

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