January 22, 2013 at 3:27 am
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
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
Different file groups
January 22, 2013 at 8:10 am
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
January 22, 2013 at 8:10 am
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy