sql query to find in which datafile a particular table exists

• rj529

  Ten Centuries

  Points: 1194

  More actions

  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.

• Ant-Green

  SSC Guru

  Points: 113543

  More actions

  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?

• rj529

  Ten Centuries

  Points: 1194

  More actions

  January 22, 2013 at 8:01 am

  #1579648

  Different file groups

• Lowell

  SSC Guru

  Points: 323522

  More actions

  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!

• Ant-Green

  SSC Guru

  Points: 113543

  More actions

  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]