List Objects in a datafile

Posted on 2006-05-19
Medium Priority
Last Modified: 2008-02-26
I'm trying to generate a list of all of the items in each datafile I have in a database. I want to be able to review what indexes and tables are in each to make sure everything is laid out optimally. Is there a sp that will do this or some other way I can get this info?

Question by:i2mental
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16718340
in the INFORMATION_SCHEMA.TABLES view you can see which tables are in which filegroup.
the views, procedure etc are all in the PRIMARY filegroup.

you CANNOT have the information per data file (unless of course, each filegroup is exactly 1 data file each time)

Author Comment

ID: 16718546
That view only appears to give me table_catalog, schema, table_name, and type. Nothing about filegroups. There is only one datafile per filegroup, so seeing it by filegroup would work.
LVL 75

Accepted Solution

Aneesh Retnakaran earned 2000 total points
ID: 16718680
try this

select  so.[name] as TableName,
 si.[name] as IndexName,
 sfg.groupname as FileGroupName
from sysobjects so
inner join sysindexes si  on so.id = si.id
inner join sysfilegroups sfg  on si.groupid = sfg.groupid
where type='U'

Author Comment

ID: 16718705
Great. That looks like it will work. Thanks.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question