List Objects in a datafile

Posted on 2006-05-19
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
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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)
    LVL 8

    Author Comment

    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

    try this

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

    Author Comment

    Great. That looks like it will work. Thanks.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now