Tables and 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

SQL Server 2000 Forums

SQL Server Administration (2000)

Tables and File Groups

Author

Topic

acko
Yak Posting Veteran

52 Posts

Posted - 2004-03-12 : 07:12:45

Hi everybody
I need query that will determine which tables are created on primary file group or secondary file group...
I know that i can exectute sp_help 'tablename' to see on which file group the only one table is.
But i have 2000 tables and it is very slow to execute sp_help for every table.
alex

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-12 : 07:31:48

Look at sysindexes.groupid (indid = 0,1 for data)
and sysfiles.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-12 : 07:45:33

Would this help?


SELECT 
	sfilegrp.groupid AS GroupID
	, SUBSTRING(sfilegrp.groupname,1,30) AS FilegroupName
	, FILENAME
	, SUBSTRING(sobj.name,1,50) AS ObjectName
FROM 
	SYSOBJECTS sobj 
INNER JOIN 
	SYSINDEXES si 
ON 
	sobj.id = si.id
	INNER JOIN 
		SYSFILEGROUPS sfilegrp 
	ON 
		si.groupid = sfilegrp.groupid
		INNER JOIN 
			SYSFILES sfiles 
		ON 
			sfilegrp.groupid = sfiles.groupid
WHERE 
	si.indid = 0 
AND 
	sobj.xtype = 'U'

--Frank
http://www.insidesql.de

acko
Yak Posting Veteran

52 Posts

Posted - 2004-03-12 : 08:49:57

thanks
alex

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources