Please get the required query from given below link-
http://sqlshares.blogspot.com/search/label/SQL%20Queries
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
The best way is right click on the database go to reports and select Disk Usage by Table.
