Database Scripts

 Log In    |   Sign Up

SQL Server : Tables and filegroups/filenames associated with them

Added on Sep-17-2013 by Naginder
For SQL Server

Tags : filegroupstables

Script will list all the tables and details of their indexes and on which filegroups/filenames they reside on. Script will be helpful in case if we need to identify space used within each database file and then decide on the archiving policies


2005,2008 and above


select OBJECT_NAME( name, as indexname, as filename,mf.physical_name,,au.type_desc,si.rowcnt,au.total_pages*8*1024 as size from
sys.sysindexes si join sys.partitions p on and si.indid=p.index_id join sys.allocation_units au on p.partition_id=au.container_id
join sys.data_spaces ds on au.data_space_id=ds.data_space_id join sys.master_files mf on ds.data_space_id=mf.data_space_id
where in (select object_id from sys.tables where is_ms_shipped=0)
and mf.database_id=DB_ID()
order by 1


Report Script

blog comments powered by Disqus