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


Unknown 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..

Unknown 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

Unknown said...

hey thanks for the info.. :)

Huongkv said...

Aivivu chuyên vé máy bay, tham khảo
gia ve may bay ve vn
chuyến bay từ đức về hà nội hôm nay
Lịch bay từ Seoul đến Hà Nội
chuyen bay chuyen gia ve viet nam

Post a Comment