Verifying the FileGroup storage (Production Mapping)

Run the following SQL queries to verify that the product library workspace was created under the correct FileGroups.

NoteNote:

Copying and pasting the examples may cause syntax errors.

--use prodlibdb database
USE [prodlibdb]
GO

List FileGroups and data files:

EXEC sp_helpdb prodlibdb
GO

List FileGroup data files:

--use prodlibdb database
USE [prodlibdb]
GO
EXEC sp_helpfilegroup 'PRIMARY'
GO

List tables by FileGroup:

--use prodlibdb database
USE [prodlibdb]
GO
SELECT USER_NAME(o.uid) [Owner],
OBJECT_NAME(i.id) [Table Name],
FILEGROUP_NAME(groupid) AS [Filegroup Name]
FROM sysindexes i inner join sysobjects o
ON i.id = o.id
WHERE i.indid IN (0, 1) AND OBJECTPROPERTY(i.id, 'IsMSShipped') = 0 AND
USER_NAME(o.uid) = 'prodlib'
ORDER BY 1,3,2
GO

List indexes by table and FileGroup:

--use prodlibdb database
USE [prodlibdb]
GO
select 'owner'=user_name(o.uid) 
,'table_name'=object_name(i.id),i.indid
,'index_name'=i.name ,i.groupid
,'filegroup'=f.name ,'file_name'=d.physical_name
,'dataspace'=s.name from sys.sysindexes i
,sys.sysobjects o,sys.filegroups f 
,sys.database_files d, sys.data_spaces s
where objectproperty(i.id,'IsUserTable') = 1
and i.id = o.id
and f.data_space_id = i.groupid
and f.data_space_id = d.data_space_id
and f.data_space_id = s.data_space_id
and user_name(o.uid) = 'prodlib'
order by object_name(i.id),i.name,f.name
GO

If any tables or indexes are stored in the wrong FileGroup, ALTER TABLE and ALTER INDEX can be used to change the FileGroup (see SQL Server Books Online at http://msdn.microsoft.com/en-us/library/ms130214.aspx).

In SQL Server Management Studio, you can also re-create the DDL script of tables and indexes; then, within create script, you can modify the FileGroup parameter and re-create the tables and indexes in the correct FileGroups. This is particularly useful when tables are empty and you are allowed to re-create database objects.

9/26/2014