Database Scripts

Beta
 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

Versions

2005,2008 and above

DOWNLOAD

select OBJECT_NAME(si.id)as name,si.name as indexname,mf.name as filename,mf.physical_name,ds.name,au.type_desc,si.rowcnt,au.total_pages*8*1024 as size from
sys.sysindexes si join sys.partitions p on si.id=p.object_id 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 si.id 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