How to identify all tables in certain filegroup  
Author Message
drjoeross





PostPosted: Mon Dec 20 10:41:10 CST 2004 Top

SQL Server Developer >> How to identify all tables in certain filegroup

All tables belong to Primary filegroup by default.
Are there any SQL commends to list tables in a certain filegroup?

Thanks in advance,

SQL Server210  
 
 
scuba79





PostPosted: Mon Dec 20 10:41:10 CST 2004 Top

SQL Server Developer >> How to identify all tables in certain filegroup Search for "sp_tables" in the BOL and see if that will help



> All tables belong to Primary filegroup by default.
> Are there any SQL commends to list tables in a certain filegroup?
>
> Thanks in advance,
>
>
>
>
 
 
Jacco





PostPosted: Mon Dec 20 10:53:24 CST 2004 Top

SQL Server Developer >> How to identify all tables in certain filegroup SELECT OBJECT_NAME(i.ID), fg.groupname
FROM sysindexes i
INNER JOIN sysfilegroups fg
ON i.groupid = fg.groupid
WHERE i.indid IN (0,1)


--
Jacco Schalkwijk
SQL Server MVP




> All tables belong to Primary filegroup by default.
> Are there any SQL commends to list tables in a certain filegroup?
>
> Thanks in advance,
>
>
>
>


 
 
AlejandroMesa





PostPosted: Mon Dec 20 10:57:05 CST 2004 Top

SQL Server Developer >> How to identify all tables in certain filegroup Try using the "groupid" column from table "sysindexes ".

Example:

use northwind
go

select
object_name([id]),
filegroup_name(groupid)
from
sysindexes
where
(indid = 0 or indid = 1)
and objectproperty([id], 'IsUserTable') = 1
and objectproperty([id], 'IsMSSHipped') = 0



AMB



> All tables belong to Primary filegroup by default.
> Are there any SQL commends to list tables in a certain filegroup?
>
> Thanks in advance,
>
>
>
>
 
 
MikeTorry





PostPosted: Mon Dec 20 11:53:05 CST 2004 Top

SQL Server Developer >> How to identify all tables in certain filegroup Thanks to all of your kind help. The ideas work well.




> All tables belong to Primary filegroup by default.
> Are there any SQL commends to list tables in a certain filegroup?
>
> Thanks in advance,
>
>
>
>