I am new in SQL Server and want to know that what is SQL Server file-group and Is there a way in SQL Server to check where the SQL Server objects are stored and how we can see them?
google is your friend:
SQL Server objects (are stored in database files (mdf, ndf, ldf extensions) . The best way to see them is with an IDE such as SSMS
select
a.dbName,
a.fgName,
a.type_desc,
df.name,
df.physical_name,
a.TableName,
a.IndexName,
a.XmlIndexName,
a.SpacialIndexName
from (
select distinct
d.name dbName,
fg.data_space_id,
fg.name fgName,
fg.type_desc,
object_name(i.object_id) TableName,
case
when i.type = 2 then i.name
else N''
end IndexName,
case
when i.type = 3 then i.name
else N''
end XmlIndexName,
case
when i.type = 4 then i.name
else N''
end SpacialIndexName
from
sys.databases d
inner join
sys.master_files mf
on mf.database_id = d.database_id
inner join
sys.filegroups fg
on fg.data_space_id = mf.data_space_id
inner join
sys.indexes i
on i.data_space_id = mf.data_space_id
where
d.database_id = DB_ID()
) a
inner join
sys.database_files df
on a.data_space_id = df.data_space_id
order by
a.dbName,
a.fgName,
df.name,
a.TableNameHTH
1 Like