File Groups - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

File Groups

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-29 : 08:00:57

Marilyn writes "Is there a way to query the system tables or information_schema to determine which tables and indexes are assigned to each filegroup? I have a database that has 800 tables across 11 filegroups and I'd like to know which tables and which indexes are in each filegroup.

I know how to look it up individually on each table, but I'd like to use tsql to create a list for me.
Thanks!"

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2003-04-29 : 11:00:44

This should get you started...


SELECT 	OBJECT_NAME(id) AS [Table Name], 
	[Name] AS [Index Name], 
	FILEGROUP_NAME(groupid) AS [Filegroup Name]
FROM 	SYSINDEXES 
WHERE 	OBJECTPROPERTY(id, 'IsUserTable') = 1 AND
	(INDEXPROPERTY(id, name, 'IsAutoStatistics') = 0 OR
	 INDEXPROPERTY(id, name, 'IsAutoStatistics') IS NULL)
ORDER	BY 3, 1, 2

Jeff Banschbach, MCDBA

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-29 : 11:11:34

Go here and download the diagram for the system tables

http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.asp

Then the sql should look like

/* xtype
S system table
U user table
PK primary key
F foreign key
P Procedure
D UDF
*/
SELECT C.groupname, D.[name] as [file], d.[filename] as filepath, A.xtype as objecttype, A.[name] as objectname
FROM sysobjects A
INNER JOIN sysindexes B ON A.id = B.id
INNER JOIN sysfilegroups C ON B.groupid = C.groupid
INNER JOIN sysfiles D ON C.groupid = D.groupid
WHERE A.xtype IN ('S','U','PK','F','P','D')
ORDER BY C.groupid, A.xtype, A.[name]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources