Listing and Changing Filegroup assignments in SSMS

Using SQL 2005 Server Management Studio how can I:
1. List the Filegroup being used by each Table (or vice versa)?
2. Move a Table to a different Filegroup?
Note on #2: My understanding is that if I change the storage location of a
clustered index then the table should move with it. But if I try to change
the Filegroup from Index Properties -> Storage tab I get an Error 1779
("Recreate failed for index 'pk'. ... Table already has a primary key defined
on it. Could not create constraint.")Hi Dave
"Dave Booker" wrote:
> Using SQL 2005 Server Management Studio how can I:
> 1. List the Filegroup being used by each Table (or vice versa)?
sp_help 'table' will give the data file location or you can query sys.tables
and sys.filegroups (see below)
> 2. Move a Table to a different Filegroup?
> Note on #2: My understanding is that if I change the storage location of a
> clustered index then the table should move with it. But if I try to change
> the Filegroup from Index Properties -> Storage tab I get an Error 1779
> ("Recreate failed for index 'pk'. ... Table already has a primary key defined
> on it. Could not create constraint.")
You would need to drop the PK or Clustered index first. Moving the
PK/clustered index will not move where the text is e.g.
CREATE DATABASE MyDB
ON PRIMARY
( NAME = MyDb_dat,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_Data1.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15 ),
FILEGROUP Secondary
( NAME = MyDB_dat2,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_Data2.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15 )
LOG ON
( NAME = Mydb_log,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_log.log',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
USe MyDb
GO
CREATE TABLE MyTab ( id int not null constraint PK_MyTable PRIMARY KEY,
col2 varchar(300),
txt text )
ON [PRIMARY]
TEXTIMAGE_ON [SECONDARY]
GO
EXEC sp_help MyTab
CREATE TABLE MyTab2 ( id int not null ,
col2 varchar(300),
txt text )
ON [PRIMARY]
GO
EXEC sp_help MyTab2
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
ALTER TABLE MyTab DROP CONSTRAINT PK_MyTable
GO
EXEC sp_help MyTab
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
ALTER TABLE MyTab ADD CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (id)
ON [SECONDARY]
GO
EXEC sp_help MyTab
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
CREATE CLUSTERED INDEX Ind_MyTab2 ON MyTab2 (ID)
ON [SECONDARY]
GO
EXEC sp_help MyTab2
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
To move text columns you would need to create a new table and suck the data
out of the original table
If you have a FK referencing your PK or UNIQUE CLUSTERED index then the FK
would have to be dropped first and re-created after you PK/Index has been
re-created.
John