How to identify - Table(s) belonging to which FileGroup ?

PandianS
Posted by PandianS under Sql Server category on | Views : 2764
Whenever we create a new table, The table will be located on Default filegroup.

How to identify the default filegroup of the current Database:
USE DotnetFunda
Go
SELECT [Name] FROM sys.filegroups WHERE is_default =1
Go
Creating a Stored procedure to Identify the particular/all table(s) FileGroup.
USE DotnetFunda
Go
IF OBJECT_ID('USP_FileGroup_Tables','P') IS NOT NULL
DROP PROC USP_FileGroup_Tables
GO
CREATE PROC USP_FileGroup_Tables
(
@TableName VARCHAR(100) = NULL
)AS
BEGIN
SET NOCOUNT ON

SELECT d.[Name] 'File Group',
OBJECT_NAME(i.[Object_ID]) 'Table Name'
FROM sys.data_spaces d Join sys.indexes i On(d.data_space_id = i.data_space_id)
WHERE i.index_id < 2 AND OBJECTPROPERTY(i.[Object_ID],'IsMSShipped') = 0
AND i.[object_id] = CASE WHEN @TableName IS NOT NULL
AND @TableName <> '' THEN OBJECT_ID(@TableName) ELSE i.[object_id] END
ORDER BY 2
END
GO
To identify a Particular table's FileGroup:
USE DotnetFunda
Go
EXEC USP_FileGroup_Tables 'TableName'
GO
To identify all tables' FileGroup:
USE DotnetFunda
Go
EXEC USP_FileGroup_Tables
GO
Note:
"DotnetFunda" is the Database Name.

Cheers

Comments or Responses

Login to post response