question

Naren avatar image
Naren asked

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...
sql-server-2005tsqldbaindexes
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hi everyone anyboday have an idea how to get the script for above requirement...
0 Likes 0 ·
Sharma avatar image
Sharma answered
Please get the required query from given below link- http://sqlshares.blogspot.com/search/label/SQL%20Queries
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for giving its working fine i want to add table size also to that output how I can?
0 Likes 0 ·
SP_SPACEUSED 'TABLE_NAME'
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

deepkalmeida avatar image
deepkalmeida answered
The best way is right click on the database go to reports and select Disk Usage by Table.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.