/*
MSP_UTIL_ADD_FILEGROUP_AND_DATAFILES
MSP_UTIL_CREATE_CLUSTERED_IDENTITY
MSP_UTIL_ADD_CLUSTERED_IDENTITY_TO_BLOBS
MSP_UTIL_DROP_TEMP_CLUSTERED_IDENTITY
MSP_UTIL_MOVE_CLUSTERED_INDEXES
MSP_UTIL_MOVE_NONCLUSTERED_INDEXES
MSP_UTIL_REBUILD_INDEX
MSP_UTIL_EXEC_MOVE_DATABASE_OBJECTS
MSP_UTIL_EXEC_MOVE_DATABASE_FILES
MSP_UTIL_EXEC_MOVE_TABLES_TO_FILEGROUP
MSP_UTIL_EXEC_MOVE_INDEXES_TO_FILEGROUP
*/

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



--**********************************************************************************************************************
-- MSP_UTIL_REBUILD_INDEX
--**********************************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[MSP_UTIL_REBUILD_INDEX]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[MSP_UTIL_REBUILD_INDEX]
GO

CREATE PROCEDURE [dbo].[MSP_UTIL_REBUILD_INDEX]
   @TableName		sysname		= NULL,
   @IndexName		sysname		= NULL,
   @FileGroupName	sysname		= NULL
		
AS

IF @TableName IS NOT NULL
  BEGIN
	IF OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0 
	  BEGIN
		  RAISERROR('Object: %s exists but is NOT a User-defined Table. This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
		  RETURN
	  END
  END
ELSE
  BEGIN
	  RAISERROR('Parameter %s  is required.', 16, 1, '@TableName')
	  RETURN
  END

IF @IndexName IS NOT NULL
  BEGIN
	IF INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered') IS NULL
	  BEGIN
		  SELECT @TableName, @IndexName, INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered')
		  RAISERROR('There is no index with name %s on this table. Please check the table name and index name as well as location (which database?). This procedure only accepts existing table names and their clustered indexes for rebuilds.', 16, 1, @IndexName)
		  RETURN
	  END
  END
ELSE
  BEGIN
	  RAISERROR('Parameter %s  is required.', 16, 1, '@IndexName')
	  RETURN
  END

DECLARE @ExecStr		nvarchar(4000)
		, @ColList		nvarchar(3000)
		, @Unique		nvarchar(7)	-- Will be either '' or 'Unique '
		, @IndexType	nvarchar(13)	-- Will be either 'CLUSTERED ' or 'NONCLUSTERED '
		, @FillFactor	nvarchar(100)

CREATE TABLE #IndexInfo
(
	IndexName	sysname,
	IndexDesc	varchar(210),
	IndexKeys	nvarchar(2126)
)

INSERT INTO #IndexInfo 
  EXEC sp_helpindex @TableName

-- Get index columns
SELECT @ColList = IndexKeys
FROM #IndexInfo
WHERE IndexName = @IndexName

-- Get index unique property
SELECT @Unique = CASE WHEN (INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsUnique') = 1) THEN 'Unique '  ELSE ''  END

-- Get index type (clustered, nonclustered)
SELECT @IndexType = CASE WHEN (INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered') = 1) THEN ''  ELSE 'NON'  END
SELECT @IndexType = @IndexType + 'CLUSTERED '

-- Get index fillfactor
SELECT @FillFactor = ', FILLFACTOR = ' + NULLIF(convert(nvarchar(3), 
						(SELECT OrigFillFactor 
							FROM sysindexes 
							WHERE id = object_id(@TableName) 
								AND Name = @IndexName)), 0)

IF NOT (@FileGroupName IS NULL OR RTRIM(@FileGroupName) = '')
BEGIN
	SELECT @FileGroupName = 'ON ' + @FileGroupName
END

SELECT @ExecStr = 'CREATE ' + @Unique + @IndexType + 'INDEX ' 
						+ @IndexName + ' ON ' 
						+ @TableName + '(' + @ColList 
						+ ') WITH DROP_EXISTING '
						+ ISNULL(@FillFactor, '')
						+ ISNULL(@FileGroupName, '')

-- Create the Index
EXEC (@ExecStr)

RETURN
-- End procedure MSP_UTIL_REBUILD_INDEX
GO



--**********************************************************************************************************************
-- MSP_UTIL_ADD_FILEGROUP_AND_DATAFILES
--**********************************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'dbo.MSP_UTIL_ADD_FILEGROUP_AND_DATAFILES') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE dbo.MSP_UTIL_ADD_FILEGROUP_AND_DATAFILES
GO

CREATE PROCEDURE dbo.MSP_UTIL_ADD_FILEGROUP_AND_DATAFILES
  @DatabaseName		varchar(100) = NULL,
  @FilegroupName	varchar(100) = NULL,
  @NewDataFile1		varchar(2000) = NULL,
  @NewDataFile2		varchar(2000) = NULL,
  @NewFileLabel1	varchar(100) = NULL,
  @NewFileLabel2	varchar(100) = NULL

AS

  DECLARE @QueryText 		varchar(4000)
  DECLARE @Result			int

  -- Validation
  IF @DatabaseName IS NULL OR RTRIM(@DatabaseName) = ''
    BEGIN
      PRINT 'You must supply value for @DatabaseName in order to add the database files.'
      RETURN
    END

   IF @FilegroupName IS NULL OR RTRIM(@FilegroupName) = ''
     BEGIN
       PRINT 'You must supply value for @FilegroupName in order to add the database files.'
       RETURN
     END

   IF @NewDataFile1 IS NULL  OR RTRIM(@NewDataFile1) = '' OR @NewDataFile2 IS NULL OR RTRIM(@NewDataFile2) = ''
     BEGIN
       PRINT 'You must supply value for each @NewDataFile parameter in order to add the database files.'
       RETURN
     END

   IF @NewFileLabel1 IS NULL OR RTRIM(@NewFileLabel1) = '' OR @NewFileLabel2 IS NULL OR RTRIM(@NewFileLabel2) = ''
     BEGIN
       PRINT 'You must supply value for each @NewFileLabel parameter in order to add the database files.'
       RETURN
     END

  -- Make sure that the file labels don't already exist in the database

  SELECT @QueryText =
  'IF (SELECT COUNT(*) FROM ' + @DatabaseName + '..sysfiles WHERE name = ''' + @NewFileLabel1 + ''' or name = ''' + @NewFileLabel2 + ''') > 0 
    BEGIN
      PRINT ''This file label already exists in the database ' + @DatabaseName + '. Please choose another file label.''
      RETURN
    END'
  EXEC (@QueryText)

  -- Make sure that the file names don't already exist in the database

  SELECT @QueryText =
  'IF (SELECT COUNT(*) FROM ' + @DatabaseName + '..sysfiles WHERE filename = ''' + @NewDataFile1 + ''' or filename = ''' + @NewDataFile2 + ''') > 0 
    BEGIN
      PRINT ''This file path already exists in the database ' + @DatabaseName + '. Please choose another file path.''
      RETURN
    END'
  EXEC (@QueryText)

  -- Create new filegroup if it does not exist

  PRINT 'Creating new filegroup "' + @FilegroupName + '", if needed'
  SELECT @QueryText =
  'IF (SELECT COUNT(*) FROM ' + @DatabaseName + '..sysfilegroups WHERE groupname = ''' + @FilegroupName + ''') < 1
    BEGIN
      ALTER DATABASE ' + @DatabaseName + '
      ADD FILEGROUP [' + @FilegroupName + ']
    END'
  EXEC (@QueryText)

  -- Now that we've verified no data duplication will occur, add new datafiles to filegroup

  PRINT 'Adding new datafiles to filegroup ' + @FilegroupName
  SELECT @QueryText =
  'ALTER DATABASE ' + @DatabaseName + ' 
  ADD FILE 
  ( NAME = ''' + @NewFileLabel1 + ''',
    FILENAME = ''' + @NewDataFile1 + ''',
    SIZE = 100MB,
    FILEGROWTH = 10%),
  ( NAME = ''' + @NewFileLabel2 + ''',
    FILENAME = ''' + @NewDataFile2 + ''',
    SIZE = 100MB,
    FILEGROWTH = 10%)
  TO FILEGROUP "' + @FilegroupName + '"'
  PRINT @QueryText
  EXEC (@QueryText)

RETURN
  -- End procedure MSP_UTIL_ADD_FILEGROUP_AND_DATAFILES
GO



--**********************************************************************************************************************
-- MSP_UTIL_CREATE_CLUSTERED_IDENTITY
--**********************************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[MSP_UTIL_CREATE_CLUSTERED_IDENTITY]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[MSP_UTIL_CREATE_CLUSTERED_IDENTITY]
GO

CREATE PROCEDURE [dbo].[MSP_UTIL_CREATE_CLUSTERED_IDENTITY]
  @TableName		varchar(100)

AS

DECLARE @ExecStr		nvarchar(4000)
DECLARE @IdentityName		sysname

-- Determine whether identity field exists. If not - create one
SELECT @IdentityName = name
FROM syscolumns
WHERE id = object_id(@TableName)
AND colstat = 1

IF @IdentityName IS NULL
  BEGIN
	SET @IdentityName = 'T1M2P3___ID'
	SET @ExecStr = 'ALTER TABLE ' + @TableName + ' ADD ' + @IdentityName + ' int IDENTITY(1,1)'
	EXEC (@ExecStr)
  END

-- Creating the index
SET @ExecStr = 'CREATE UNIQUE CLUSTERED INDEX I_T1M2P3_' + @IdentityName + ' ON ' + @TableName + ' (' + @IdentityName + ')'
EXEC (@ExecStr)

RETURN
-- End procedure MSP_UTIL_CREATE_CLUSTERED_IDENTITY
GO



--**********************************************************************************************************************
-- MSP_UTIL_ADD_CLUSTERED_IDENTITY_TO_BLOBS
--**********************************************************************************************************************
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[MSP_UTIL_ADD_CLUSTERED_IDENTITY_TO_BLOBS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[MSP_UTIL_ADD_CLUSTERED_IDENTITY_TO_BLOBS]
GO

CREATE PROCEDURE [dbo].[MSP_UTIL_ADD_CLUSTERED_IDENTITY_TO_BLOBS]
  @TableCategory	varchar(100)   -- WEB, CUBE, PROJ or VIEW

AS

DECLARE @QueryText		varchar(2000)
DECLARE @WhereClause		varchar(2000)
DECLARE @TableName 		varchar(100)
DECLARE @IndexName 		varchar(100)
DECLARE @ColumnName		varchar(100)
DECLARE @Result		int

SELECT @WhereClause = CASE
	WHEN @TableCategory = 'WEB' THEN ' AND t.name like ''MSP_WEB_%'''
	WHEN @TableCategory = 'CUBE' THEN ' AND t.name like ''MSP_CUBE_%'''
	WHEN @TableCategory = 'VIEW' THEN ' AND t.name like ''MSP_VIEW_%'''
	WHEN @TableCategory = 'PROJ' THEN ' AND t.name not like ''MSP_VIEW_%'' AND t.name not like ''MSP_CUBE_%'' AND t.name not like ''MSP_WEB_%'' '
	WHEN @TableCategory = '*' THEN ''
	ELSE ''
END

CREATE TABLE dbo.#tmpnonclusteredtables (TableName varchar(100))

SELECT @QueryText =
'INSERT INTO dbo.#tmpnonclusteredtables (TableName)
SELECT distinct t.name
FROM dbo.sysobjects t
WHERE 1 > ( SELECT count(*) FROM dbo.sysindexes i WHERE i.id = t.id AND INDEXPROPERTY(t.id, i.name, ''IsClustered'') = 1 ) 
	AND OBJECTPROPERTY(t.id, ''IsUserTable'') = 1
	AND t.name <> ''dtproperties'''
	+ ISNULL(@WhereClause, '')
EXEC (@QueryText)

DECLARE CursorTables CURSOR FAST_FORWARD FOR 
SELECT TableName
FROM #tmpnonclusteredtables

OPEN CursorTables

FETCH NEXT FROM CursorTables
	INTO @TableName

WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		-- Call MSP_UTIL_CREATE_CLUSTERED_IDENTITY to create the clustered index and identity field if necessary
		PRINT 'Adding temporary identity and clustered index to table: ' + @TableName
		EXEC MSP_UTIL_CREATE_CLUSTERED_IDENTITY @TableName
		FETCH NEXT FROM CursorTables INTO @TableName
	END
END
PRINT 'Finished adding temporary clustered index to table ' + @TableName

DEALLOCATE CursorTables

DROP TABLE dbo.#tmpnonclusteredtables

RETURN
-- End procedure MSP_UTIL_ADD_CLUSTERED_IDENTITY_TO_BLOBS
GO



--**********************************************************************************************************************
-- MSP_UTIL_DROP_TEMP_CLUSTERED_IDENTITY
--**********************************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[MSP_UTIL_DROP_TEMP_CLUSTERED_IDENTITY]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[MSP_UTIL_DROP_TEMP_CLUSTERED_IDENTITY]
GO

CREATE PROCEDURE [dbo].[MSP_UTIL_DROP_TEMP_CLUSTERED_IDENTITY]
AS

  DECLARE @TableName 		varchar(100)
  DECLARE @IndexName 		varchar(100)
  DECLARE @ColumnName		varchar(100)
  DECLARE @ExecStr		nvarchar(4000)
  
  DECLARE CursorTables CURSOR FAST_FORWARD FOR 
    SELECT t.name, i.name 
    FROM sysobjects t
		INNER JOIN sysindexes i ON i.id = t.id
	WHERE i.name like 'I_T1M2P3_%'

  OPEN CursorTables

  FETCH NEXT FROM CursorTables
	INTO @TableName, @IndexName

  WHILE (@@fetch_status <> -1)
  BEGIN
   IF (@@fetch_status <> -2)
     BEGIN
       -- Drop the temporary clustered index created
       PRINT 'Dropping temporary clustered index ' + @IndexName + ' from table ' + @TableName
       SET @ExecStr = 'DROP INDEX ' + @TableName + '.' + @IndexName
       EXEC (@ExecStr)	
       FETCH NEXT FROM CursorTables INTO @TableName, @IndexName
     END
  END

  DEALLOCATE CursorTables

  DECLARE CursorTables CURSOR FAST_FORWARD FOR 
    SELECT t.name, c.name 
    FROM sysobjects t
		INNER JOIN syscolumns c ON c.id = t.id
	  WHERE c.name = 'T1M2P3___ID'

  OPEN CursorTables

  FETCH NEXT FROM CursorTables
	INTO @TableName, @ColumnName

  WHILE (@@fetch_status <> -1)
  BEGIN
   IF (@@fetch_status <> -2)
     BEGIN
       -- Drop the identity column if it had to be created
       PRINT 'Dropping temporary identity column ' + @ColumnName + ' from table ' + @TableName
       SET @ExecStr = 'ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @ColumnName
       EXEC (@ExecStr)	
       FETCH NEXT FROM CursorTables INTO @TableName, @ColumnName
     END
  END

  DEALLOCATE CursorTables

RETURN
-- End procedure MSP_UTIL_DROP_TEMP_CLUSTERED_IDENTITY
GO



--**********************************************************************************************************************
-- MSP_UTIL_MOVE_CLUSTERED_INDEXES
--**********************************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[MSP_UTIL_MOVE_CLUSTERED_INDEXES]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[MSP_UTIL_MOVE_CLUSTERED_INDEXES]
GO

CREATE PROCEDURE [dbo].[MSP_UTIL_MOVE_CLUSTERED_INDEXES]
  @FilegroupName	varchar(100), 
  @TableCategory	varchar(100)  -- WEB, PROJ, CUBE, VIEW

AS

DECLARE @QueryText		varchar(2000)
DECLARE @WhereClause	varchar(100)
DECLARE @TableName		varchar(100)
DECLARE @IndexName		varchar(100)

-- Create temp table for managing index rebuilt
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[dbo].[#tmpindexes]'))
	DROP TABLE [dbo].[#tmpindexes]

CREATE TABLE dbo.#tmpindexes
(
	TableName		sysname,
	IndexName		sysname 
)

-- Store all clustered index names

SELECT @WhereClause = CASE
	WHEN @TableCategory = 'WEB' THEN ' AND t.name like ''MSP_WEB_%'''
	WHEN @TableCategory = 'CUBE' THEN ' AND t.name like ''MSP_CUBE_%'''
	WHEN @TableCategory = 'VIEW' THEN ' AND t.name like ''MSP_VIEW_%'''
	WHEN @TableCategory = 'PROJ' THEN ' AND t.name not like ''MSP_VIEW_%'' AND t.name not like ''MSP_CUBE_%'' AND t.name not like ''MSP_WEB_%'' '
	WHEN @TableCategory = '*' THEN ''
	ELSE ''
END

SELECT @QueryText = 
'insert #tmpindexes (TableName, IndexName)
select t.name, i.name
from dbo.sysindexes i
	inner join dbo.sysobjects t on t.id = i.id
where INDEXPROPERTY(t.id, i.name, ''IsClustered'') = 1
	and OBJECTPROPERTY(t.id, ''IsUserTable'') = 1
	and t.name <> ''dtproperties'''
	+ ISNULL(@WhereClause, '') +
'order by 1, 2'

EXEC (@QueryText)

DECLARE CursorIndexes CURSOR FAST_FORWARD FOR 
	SELECT TableName, IndexName 
	FROM #tmpindexes

OPEN CursorIndexes

FETCH NEXT FROM CursorIndexes 
	INTO @TableName, @IndexName

WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		PRINT 'Processing table: ' + @TableName
		EXEC MSP_UTIL_REBUILD_INDEX @TableName, @IndexName, @FilegroupName
		FETCH NEXT FROM CursorIndexes INTO @TableName, @IndexName
	END
END
PRINT 'Rebuilding tables complete'

DEALLOCATE CursorIndexes 

DROP TABLE #tmpindexes

RETURN
-- End procedure MSP_UTIL_MOVE_CLUSTERED_INDEXES
GO



--**********************************************************************************************************************
-- MSP_UTIL_MOVE_NONCLUSTERED_INDEXES
--**********************************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[MSP_UTIL_MOVE_NONCLUSTERED_INDEXES]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[MSP_UTIL_MOVE_NONCLUSTERED_INDEXES]
GO

CREATE PROCEDURE [dbo].[MSP_UTIL_MOVE_NONCLUSTERED_INDEXES]
  @FilegroupName	varchar(100),
  @TableCategory	varchar(100)

AS

DECLARE @QueryText	varchar(2000)
DECLARE @WhereClause	varchar(100)
DECLARE @TableName 	varchar(100)
DECLARE @IndexName 	varchar(100)

-- Create temp table for managing index rebuilt
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[dbo].[#tmpindexes]'))
	DROP TABLE [dbo].[#tmpindexes]

CREATE TABLE dbo.#tmpindexes (TableName sysname, IndexName sysname)

SELECT @WhereClause = CASE
	WHEN @TableCategory = 'WEB' THEN ' AND t.name like ''MSP_WEB_%'''
	WHEN @TableCategory = 'CUBE' THEN ' AND t.name like ''MSP_CUBE_%'''
	WHEN @TableCategory = 'VIEW' THEN ' AND t.name like ''MSP_VIEW_%'''
	WHEN @TableCategory = 'PROJ' THEN ' AND t.name not like ''MSP_VIEW_%'' AND t.name not like ''MSP_CUBE_%'' AND t.name not like ''MSP_WEB_%'' '
	WHEN @TableCategory = '*' THEN ''
	ELSE ''
END

SELECT @QueryText = 
'insert #tmpindexes (TableName, IndexName)
select t.name, i.name
from dbo.sysindexes i
	inner join dbo.sysobjects t on t.id = i.id
	inner join dbo.sysindexkeys xk on (t.id = xk.id and i.indid = xk.indid)
where INDEXPROPERTY(t.id, i.name, ''IsClustered'') = 0
	and OBJECTPROPERTY(t.id, ''IsUserTable'') = 1
	and t.name <> ''dtproperties'''
	+ ISNULL(@WhereClause, '') +
'order by 1, 2'

EXEC (@QueryText)

DECLARE CursorIndexes CURSOR FAST_FORWARD FOR 
	SELECT TableName, IndexName 
	FROM #tmpindexes

OPEN CursorIndexes

FETCH NEXT FROM CursorIndexes 
	INTO @TableName, @IndexName

WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		PRINT 'Processing table: ' + @TableName
		EXEC MSP_UTIL_REBUILD_INDEX @TableName, @IndexName, @FilegroupName
		FETCH NEXT FROM CursorIndexes INTO @TableName, @IndexName
	END
END
PRINT 'Rebuilding indexes complete'

DEALLOCATE CursorIndexes 

DROP TABLE #tmpindexes

RETURN
-- End procedure MSP_UTIL_MOVE_NONCLUSTERED_INDEXES
GO



--**********************************************************************************************************************
-- MSP_UTIL_EXEC_MOVE_DATABASE_OBJECTS
--**********************************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[MSP_UTIL_EXEC_MOVE_DATABASE_OBJECTS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[MSP_UTIL_EXEC_MOVE_DATABASE_OBJECTS]
GO

CREATE PROCEDURE [dbo].[MSP_UTIL_EXEC_MOVE_DATABASE_OBJECTS]
	@FilegroupName	varchar(100)	= NULL

AS

--**********************************************************************************************************************
--
-- Procedure Name: 	MSP_UTIL_EXEC_MOVE_DATABASE_OBJECTS
-- Created Date: 	11/18/2002
-- Description:		This procedure moves all user tables and indexes to the specified filegroup. For moving
--					specific tables / indexes use MSP_UTIL_EXEC_MOVE_TABLES_TO_FILEGROUP and/or MSP_UTIL_EXEC_MOVE_INDEXES_TO_FILEGROUP
-- 
-- Important Notes:
--				1 - Make a full backup of the database prior to beginning
--				2 - This procedure must be executed as a system administrator authorized login within the Project Server database context
--				3 - This procedure must be executed from within the database you intend to modify
--
--**********************************************************************************************************************

-- Validation
IF @FilegroupName IS NULL OR RTRIM(@FilegroupName) = ''
  BEGIN
	  PRINT 'You must supply value for @FilegroupName in order to add the database files.'
	  RETURN
  END

IF (SELECT COUNT(*) FROM sysfilegroups WHERE groupname = @FilegroupName) = 0
BEGIN
	PRINT 'Filegroup ' + @FilegroupName + ' does not exist.  Please create it first with the stored procedure MSP_UTIL_ADD_FILEGROUP'
	RETURN
END

-- Creating temp clustered index on temp identity column
EXEC MSP_UTIL_ADD_CLUSTERED_IDENTITY_TO_BLOBS '*'

-- Moving tables
EXEC MSP_UTIL_MOVE_CLUSTERED_INDEXES @FilegroupName, '*'

-- Moving nonclustered indexes
EXEC MSP_UTIL_MOVE_NONCLUSTERED_INDEXES @FilegroupName, '*'

-- Dropping temp identity column and the temp clustered index
EXEC MSP_UTIL_DROP_TEMP_CLUSTERED_IDENTITY

RETURN
-- end MSP_UTIL_EXEC_MOVE_DATABASE_OBJECTS
GO



--**********************************************************************************************************************
-- MSP_UTIL_EXEC_MOVE_DATABASE_FILES
--**********************************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[MSP_UTIL_EXEC_MOVE_DATABASE_FILES]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[MSP_UTIL_EXEC_MOVE_DATABASE_FILES]
GO

CREATE PROCEDURE [dbo].[MSP_UTIL_EXEC_MOVE_DATABASE_FILES]
	@DatabaseName 		varchar(100)	= NULL,
	@NewDataFilePath	varchar(2000)	= NULL,
	@NewLogFilePath		varchar(2000)	= NULL

AS

--**********************************************************************************************************************
--
-- Procedure Name: 	MSP_UTIL_EXEC_MOVE_DATABASE_FILES
-- Created Date: 	11/18/2002
-- Description:		This procedure moves the primary datafile and transaction log. For moving more data files /
--			transaction logs you have to modify the code within the procedure
-- 
-- Important Notes:
--			1 - Please make a complete database backup prior to executing this procedure
--			2 - This procedure must be executed as a system administrator authorized login from a database different than the one being modified
--        	3 - NewDataFilePath and NewLogFilePath must include the new filenames (ie: d:\mydb.ldf)
--**********************************************************************************************************************

-- Declarations
DECLARE @QueryText 		varchar(2000)
DECLARE @ExecStr		  varchar(4000)
DECLARE @Result			  int


-- Validation
IF @DatabaseName IS NULL OR RTRIM(@DatabaseName) = ''
  BEGIN
	  PRINT 'You must supply value for @DatabaseName in order to move the database files.'
	  RETURN
  END
IF @NewDataFilePath IS NULL OR RTRIM(@NewDataFilePath) = ''
  BEGIN
	  PRINT 'You must supply value for @NewDataFilePath in order to move the database files.'
	  RETURN
  END
if @NewLogFilePath IS NULL OR RTRIM(@NewLogFilePath) = ''
  BEGIN
	  PRINT 'You must supply value for @NewLogFilePath in order to move the database files.'
	  RETURN
  END

-- Create temp table for managing file locations
IF EXISTS (select * from [tempdb].[dbo].[sysobjects] where id = object_id(N'[tempdb].[dbo].[#tmpsysfiles]'))
	DROP TABLE [dbo].[#tmpsysfiles]

CREATE TABLE dbo.#tmpsysfiles
( 
	FileType			varchar(10),
	OldFilePath			varchar(2000),
	NewFilePath			varchar(2000)
)

-- Get current and new file locations for datafile
SELECT @QueryText = 'INSERT INTO #tmpsysfiles (FileType, OldFilePath, NewFilePath)
								     SELECT ''data1'', filename, ''' + @NewDataFilePath + '''
		     						 FROM ' + @DatabaseName + '.dbo.sysfiles 
		     						 WHERE filename like ''%.MDF%'''
EXEC (@QueryText)

-- Get current file locations for logfile
SELECT @QueryText = 'INSERT INTO #tmpsysfiles (FileType, OldFilePath, NewFilePath)
								     SELECT ''log1'', filename, ''' + @NewLogFilePath + '''
                     FROM ' + @DatabaseName + '.dbo.sysfiles 
                     WHERE filename like ''%.LDF%'''
EXEC (@QueryText)

PRINT 'Detaching database ' + @DatabaseName
EXEC sp_detach_db @DatabaseName

PRINT 'Moving data file 1'
SELECT @ExecStr = 'move /Y "' + RTRIM(OldFilePath) + '" "' + RTRIM(NewFilePath) + '"' FROM #tmpsysfiles WHERE FileType = 'data1'
PRINT @ExecStr
EXEC @Result = master.dbo.xp_cmdshell @ExecStr
IF @Result <> 0
  BEGIN
	  PRINT 'Moving data file with xp_cmdshell failed with error code ' + CONVERT(varchar(5), @@error)
	  RETURN 1
  END

PRINT 'Moving transaction log 1'
SELECT @ExecStr = 'move /Y "' + RTRIM(OldFilePath) + '" "' + RTRIM(NewFilePath) + '"' FROM #tmpsysfiles WHERE FileType = 'log1'
PRINT @ExecStr
EXEC @Result = master.dbo.xp_cmdshell @ExecStr
IF @Result <> 0
  BEGIN
	  PRINT 'Moving transaction log with xp_cmdshell failed with error code ' + CONVERT(varchar(5), @@error)
	  RETURN 1
  END

PRINT 'Attaching database from new file locations'
EXEC sp_attach_db 
	@dbname = @DatabaseName,
	@filename1 = @NewDataFilePath,
	@filename2 = @NewLogFilePath

-- Drop the temp table
DROP TABLE dbo.#tmpsysfiles

EXEC ('SELECT * FROM ' + @DatabaseName + '..sysfiles')

PRINT 'Moving database files completed successfully.'

RETURN
-- end MSP_UTIL_EXEC_MOVE_DATABASE_FILES
GO



--**********************************************************************************************************************
-- MSP_UTIL_EXEC_MOVE_TABLES_TO_FILEGROUP
--**********************************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[MSP_UTIL_EXEC_MOVE_TABLES_TO_FILEGROUP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[MSP_UTIL_EXEC_MOVE_TABLES_TO_FILEGROUP]
GO

CREATE PROCEDURE [dbo].[MSP_UTIL_EXEC_MOVE_TABLES_TO_FILEGROUP]
  @TableCategory	varchar(10),
  @TableFileGroup	varchar(100)

AS

--**********************************************************************************************************************
--
-- Procedure Name: 	MSP_UTIL_EXEC_MOVE_TABLES_TO_FILEGROUP
-- Created Date: 	01/31/2003
-- Description:		This procedure calls the procedures necessary to move given group of tables to another filegroup
-- 
-- Important Notes:
--			1 - Please make a complete database backup prior to beginning
--      2 - This procedure must be executed as a system administrator authorized from within the Project Server database context
--			3 - @TableFileGroup is the name of the SQL Server filegroup to which you want to move the given tables
--			4 - @TableCategory can be one of the following: WEB, PROJ, CUBE, VIEW or *
--
--**********************************************************************************************************************

  -- Validation

IF @TableCategory IS NULL OR RTRIM(@TableCategory) = ''
  BEGIN
	PRINT 'You must supply a value for @TableCategory ("WEB", "PROJ", "CUBE", "VIEW" or "*". ie. Which type of Project Server table to you wish to move?)'
	RETURN
  END

IF @TableFileGroup IS NULL OR RTRIM(@TableFileGroup) = ''
  BEGIN
	PRINT 'You must supply a value for @TableFileGroup (this is the name of the filegroup to which you want to move the tables).'
	RETURN
  END

  -- Check to make sure the filegroup exists - if not notify to run MSP_UTIL_ADD_FILEGROUP

  IF (SELECT COUNT(*) FROM sysfilegroups WHERE groupname = @TableFileGroup) = 0
  BEGIN
    PRINT 'The filegroup ' + @TableFileGroup + ' does not exist.  Please create it first with the stored procedure MSP_UTIL_ADD_FILEGROUP'
    RETURN
  END

  -- Call the procedure(s) to move the indexes

  EXEC MSP_UTIL_ADD_CLUSTERED_IDENTITY_TO_BLOBS @TableCategory
  EXEC MSP_UTIL_MOVE_CLUSTERED_INDEXES @TableFileGroup, @TableCategory
  EXEC MSP_UTIL_DROP_TEMP_CLUSTERED_IDENTITY

RETURN
-- End procedure MSP_UTIL_EXEC_MOVE_TABLES_TO_FILEGROUP
GO



--**********************************************************************************************************************
-- MSP_UTIL_EXEC_MOVE_INDEXES_TO_FILEGROUP
--**********************************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[MSP_UTIL_EXEC_MOVE_INDEXES_TO_FILEGROUP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[MSP_UTIL_EXEC_MOVE_INDEXES_TO_FILEGROUP]
GO

CREATE PROCEDURE [dbo].[MSP_UTIL_EXEC_MOVE_INDEXES_TO_FILEGROUP]
  @IndexCategory	varchar(10),
  @IndexFileGroup	varchar(100)

AS

--**********************************************************************************************************************
--
-- Procedure Name: 	MSP_UTIL_EXEC_MOVE_INDEXES_TO_FILEGROUP
-- Created Date: 	01/31/2003
-- Description:		This procedure calls the procedures necessary to move given group of indexes to another filegroup
-- 
-- Important Notes:
--			1 - Please make a complete database backup prior to beginning
--			2 - This stored procedure must be executed as a system administrator authorized login within the Project Server database context
--			3 - @IndexFileGroup is the name of the SQL Server filegroup to which you want to move the given indexes
--			4 - @IndexCategory can be one of the following: WEB, PROJ, CUBE, VIEW or *
--
--**********************************************************************************************************************

  -- Validation

IF @IndexCategory IS NULL OR RTRIM(@IndexCategory) = ''
  BEGIN
	PRINT 'You must supply a value for @IndexCategory ("WEB", "PROJ", "CUBE", "VIEW" or "*". ie. Which type of Project Server indexes to you wish to move?)'
	RETURN
  END
  
IF @IndexFileGroup IS NULL OR RTRIM(@IndexFileGroup) = ''
  BEGIN
	PRINT 'You must supply a value for @IndexFileGroup (this is the name of the filegroup to which you want to move the tables).'
	RETURN
  END

  -- Check to make sure the filegroup exists - if not notify to run MSP_UTIL_ADD_FILEGROUP

  IF (SELECT COUNT(*) FROM sysfilegroups WHERE groupname = @IndexFileGroup) = 0
  BEGIN
    PRINT 'The filegroup ' + @IndexFileGroup + ' does not exist.  Please create it first with the stored procedure MSP_UTIL_ADD_FILEGROUP'
    RETURN
  END

  -- Call the procedure(s) to move the indexes

  EXEC MSP_UTIL_MOVE_NONCLUSTERED_INDEXES @IndexFileGroup, @IndexCategory

RETURN
-- End procedure MSP_UTIL_EXEC_MOVE_INDEXES_TO_FILEGROUP
GO



SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
