Post reply

sql query to find in which datafile a particular table exists

  • January 22, 2013 at 3:27 am

    #267530

    Hi Friends,

    Let us assume am having 2 data files one mdf and one ndf. I have stopped the autogrowth of mdf. Is there a way to find in which data file the particular table exists.

    Thanks in advance.

  • January 22, 2013 at 4:06 am

    #1579528

    A table doesnt exist on a data file, it exists on a file group.

    Are the MDF and NDF in different file groups or are they both in the primary filegroup?

  • January 22, 2013 at 8:01 am

    #1579648

    Different file groups

  • January 22, 2013 at 8:10 am

    #1579655

    i have this saved, whcih shows all tables/the filegroup they belong to:

    SELECT

    objz.[name],

    objz.[type],

    idxz.[name],

    idxz.[index_id],

    CASE idxz.[index_id]

    WHEN 0 THEN 'HEAP'

    WHEN 1 THEN 'CLUSTERED'

    ELSE 'NON-CLUSTERED'

    END AS index_type,

    filz.[name]

    FROM sys.indexes idxz

    INNER JOIN sys.filegroups filz

    ON idxz.data_space_id = filz.data_space_id

    INNER JOIN sys.all_objects objz

    ON idxz.[object_id] = objz.[object_id]

    WHERE idxz.data_space_id = filz.data_space_id

    AND objz.type_desc IN( 'USER_TABLE') -- User Created Tables

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 22, 2013 at 8:10 am

    #1579656

    SELECT

    o.[name] AS ObjectName,

    o.[type] AS ObjectType,

    s.[name] AS SchemaName,

    f.[name] AS [Filename],

    i.[name] AS PrimaryKey,

    i.[index_id] AS IndexID

    FROM

    sys.indexes i

    INNER JOIN

    sys.filegroups f

    ON

    i.data_space_id = f.data_space_id

    INNER JOIN

    sys.all_objects o

    ON

    i.[object_id] = o.[object_id]

    INNER JOIN

    sys.schemas s

    ON

    s.[schema_id] = o.[schema_id]

    order by

    s.[name],

    o.[name]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply