Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql query to find in which datafile a particular table exists Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2013 3:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 03, 2014 1:23 AM
Points: 54, Visits: 531
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.
Post #1409892
Posted Tuesday, January 22, 2013 4:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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?




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1409912
Posted Tuesday, January 22, 2013 8:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 03, 2014 1:23 AM
Points: 54, Visits: 531
Different file groups
Post #1410048
Posted Tuesday, January 22, 2013 8:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:47 AM
Points: 12,738, Visits: 31,041
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1410058
Posted Tuesday, January 22, 2013 8:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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]





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1410059
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse