T-SQL - Find which Object resides in which FileGroup | SansSQL

Friday, April 30, 2010

T-SQL - Find which Object resides in which FileGroup

To Find which object resides in which FileGroup in a database, run the below queries.

/**************** For SQL 2000 ****************/

Select Distinct OBJECT_NAME(SI.id) AS ObjectName
        ,SO.type AS ObjectType
        ,FG.GroupName AS FileGroupName
from sysindexes SI , sysfilegroups FG ,sysobjects SO
where SI.groupid = FG.groupid and
      SI.id = SO.id
Order by ObjectName

/**************** For SQL 2005 and SQL 2008 ****************/

Select Distinct OBJECT_NAME(SI.object_id) AS ObjectName
        ,OBJECTPROPERTYEX(SI.object_id,'BaseType') AS ObjectType
        ,FG.Name AS FileGroupName
from sys.indexes SI , sys.filegroups FG
where SI.data_space_id = FG.data_space_id
Order by ObjectName


Raghavendra said...

Hey, i'm not good in SQL, but wanted to know something here...

To retrieve data from more than 1 table we use joins.. you have used very simple where clause and joined the tables..

how difference it is with joins and is there any performance difference..

i know it may be very silly question.. but i'm a beginner..so pls don't mind..

Raghavendra said...

Hey one more thing,

IN 2008, you are using SI.object_id for both object_name & object type.. i believe u've forgotten sys.objects to include...

correct me if i'm wrong.

Sandesh Segu said...

Hi Raghavendra,

Regarding the first comment, i have used the where clause for joining tables. This is a way on how you can combine tables to get desired results.

Regarding SI.Object_ID, if i use sys.objects i have to join the table. When I am getting the results by using the in-built functions of SQL, I think there is no need to join the tables again.

Sandesh Segu

Raghavendra said...

hey thanks for the info.. :)

www.oracledba.in said...

i like this

Post a Comment