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
5 comments:
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..
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.
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.
Regards,
Sandesh Segu
hey thanks for the info.. :)
i like this
Post a Comment