Home Forums SQL Server 2008 SQL Server 2008 - General sql query to find in which datafile a particular table exists RE: sql query to find in which datafile a particular table exists

  • 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!