SQLTeam.com | Weblogs | Forums

How to find SQL Server object location?

sql2008r2

#1

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?


#2

google is your friend:

Files and filegroups

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


#3

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