Filegroups and Files

Objective:
To add, remove filegroups and files - To find objects in filegroup
Solution:
//**Listing 1: T-SQL to Display objects and filegroups not on Primary OR Any other Filegroup**//

select TableNAME = o.name, ObjectName = i.name, i.indid, s.groupname
from sysfilegroups s, sysindexes i, sysobjects o
where i.id = o.id
and o.type in ('S ','U ') --system or user table
and i.groupid = s.groupid
AND s.groupname <> 'PRIMARY'
go

select TableNAME = o.name, ObjectName = i.name, i.indid, s.groupname
from sysfilegroups s, sysindexes i, sysobjects o
where i.id = o.id
and o.type in ('S ','U ') --system or user table
and i.groupid = s.groupid
AND s.groupname = 'PRIMARY'
GO
select TableNAME = o.name, ObjectName = i.name, i.indid, s.groupname
from sysfilegroups s, sysindexes i, sysobjects o
where i.id = o.id
--and o.type in ('S ','U ') --system or user table
--and i.groupid = s.groupid
AND s.groupname = 'PRIMARY'
GO
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
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]
WHERE i.data_space_id = 2 --* New FileGroup*
GO
CREATE UNIQUE CLUSTERED
INDEX PK_TblWithClustIDXOnly ON dbo.TblWithClustIDXOnly (NonClustCol)
WITH
DROP_EXISTING
ON PRIMARY
GO
USE DBMONITOR
GO
EXEC sp_helpfilegroup
GO
EXEC sp_helpfilegroup @filegroupname= 'PRIMARY'
GO
USE DBMONITOR
GO
EXEC sp_helpfile
GO
EXEC sp_helpfile @filename= 'dbmonitor'
GO
//**ADDING A FILE **//
ALTER DATABASE Test1
ADD FILE
(
NAME = Test1dat2,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
) on filegroupname
GO

//***Remove a file from a database
This example removes one of the files added to the Test1 database.
***//
USE master
GO
ALTER DATABASE Test1 REMOVE FILE test1dat4
GO
//***Modify a file
This example increases the size of one of the files added to the Test1 database.
**//
USE master
GO
ALTER DATABASE Test1 MODIFY FILE (NAME = TEST1DATA3,SIZE = 20MB)
GO
ALTER DATABASE MSDB MODIFY FILE ( NAME = N'MSDBData', FILEGROWTH = 5%)
go
ALTER DATABASE [Logging] MODIFY FILE ( NAME = N'Logging', FILEGROWTH = 9%)

REMOVE FILEGROUPS

ALTER DATABASE MyDBName
REMOVE FILEGROUP MyFileGroupName


Actually sp_help calls for this resultset a nondocumented stored procedure:

sp_objectfilegroup OBJECT_ID('')

In order to display the table names and the filegroups in which they are allocated, you can execute:

select o.name, s.groupname
from sysobjects o
join sysindexes i on o.id = i.id
join sysfilegroups s on i.groupid = s.groupid
where o.type in ('U', 'S')
and i.indid < 2

Refer: http://www.mssqltips.com/tip.asp?tip=1112