working on it ...

Filters

Explore Public Snippets

Sort by

Found 7 snippets matching: filegroup

    public by cghersi  2704  0  6  0

    How to Retrieve all the tables having indexes on a specific filegroup in SQL Server

    Here are listed all the tables that have one or more indexes stored in a filegroup called 'INDEXES'
    SELECT 
        distinct  TableName = t.name
    FROM 
         sys.indexes ind 
    INNER JOIN 
         sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
    INNER JOIN 
         sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
    INNER JOIN 
         sys.tables t ON ind.object_id = t.object_id 
    INNER JOIN sys.[filegroups] f
        ON f.[data_space_id] = ind.data_space_id
    WHERE f.name = 'INDEXES'
    

    external by Mohammed Nisar Ansari  175  0  2  0

    Add a FILESTREAM filegroup to the database

    Add a FILESTREAM filegroup to the database: Add a FILESTREAM filegroup to the database.sql
    -- Add a FILESTREAM filegroup to the database
    ALTER DATABASE PhotoLibrary
     ADD FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
    
    -- Add a container to the FILESTREAM filegroup
    ALTER DATABASE PhotoLibrary
     ADD FILE
      (NAME = PhotoLibrary_blobs, 
       FILENAME = 'C:\Demo\PhotoLibrary\Photos')
     TO FILEGROUP FileStreamGroup1
    
    

    external by zwtokio  4  0  1  0

    Un breve script que permite modificar los archivos de la base de datos y tanbien adjuntarlos a los filegroup

    Un breve script que permite modificar los archivos de la base de datos y tanbien adjuntarlos a los filegroup: MODIFICAR ARCHIVOS DE SQL SERVER
    USE MASTER
    GO
    
    
    IF DB_ID('BDARTESA') IS NOT NULL
      DROP DATABASE BDARTESA
    GO
    
    CREATE DATABASE BDARTESA
    GO
    
    USE BDARTESA
    GO
    
    SP_HELPDB BDARTESA
    GO
    
    
    ALTER DATABASE BDARTESA
    ADD FILE (
    NAME = 'ARTESA_DATAN1',
    FILENAME = 'C:\DATAARTESA\ARTESA_DATAN1.NDF',
    SIZE = 100MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 5MB
    ) GO
    
    
    ALTER DATABASE BDARTESA
    ADD LOG FILE (
    NAME = 'ARTESA_LOG2',
    FILENAME = 'C:\DATAARTESA\ARTESA_LOG2.NDF',
    SIZE = 200MB,
    MAXSIZE = 650MB,
    FILEGROWTH = 10%
    ) GO
    
     
    
    ALTER DATABASE BDARTESA
    MODIFY FILE (
    NAME = 'ARTESA_DATAN1',
    MAXSIZE = 700MB
    )
    
    
    ALTER DATABASE BDARTESA
    MODIFY FILE (
    NAME = 'ARTESA_LOG2',
    FILEGROWTH = 25%
    )
    GO
    
    ALTER DATABASE BDARTESA
    ADD FILEGROUP FGSALES
    GO
    
    ALTER DATABASE BDARTESA
    ADD FILE (
    NAME = 'ARTESA_DATAN2',
    FILENAME = 'C:\DATAARTESA\ARTESA_DATAN2.NDF',
    SIZE = 100MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 5MB
    ) TO FILEGROUP FGSALES
      GO
    
    
    
    
    
    

    external by ayauka  68  0  1  0

    Migrations Helper Filegroups MSSQL

    Migrations Helper Filegroups MSSQL: MigrationsHelper.cs
    public static class MigrationsHelper
        {
    
            public static string CreateFilegroup(string filegroupName, byte numberOfFilegroupFiles, string sizeOfEachFile, string sizeOfFileGrown) {
                var sqlFromFileGroup = "";
                var sqlFromFilesCreated = new StringBuilder(4000);
    
                for( byte filesCreated = 0; filesCreated < numberOfFilegroupFiles; filesCreated++ )
                {
                    sqlFromFilesCreated.Append($@"
                        ALTER DATABASE CURRENT
                            ADD FILE (
                                NAME = [' + DB_NAME() + '_{filegroupName}_{filesCreated}],
                                FILENAME = N''' + REPLACE(@DatabasePath, N'.mdf', N'_{filegroupName}_{filesCreated}.ndf') + ''',
                                SIZE = {sizeOfEachFile},
                                MAXSIZE = UNLIMITED,
                                FILEGROWTH = {sizeOfFileGrown}
                                )
                            TO FILEGROUP [{filegroupName}];
                    ");
                }
    
                sqlFromFileGroup = $@"
                    IF NOT EXISTS (SELECT * FROM sys.filegroups where name = '{filegroupName}') BEGIN
                        ALTER DATABASE CURRENT
                        ADD FILEGROUP [{filegroupName}]
                    END
    
                    IF EXISTS (SELECT * FROM sys.filegroups where name = '{filegroupName}') AND NOT EXISTS (SELECT * FROM sys.master_files where name like DB_NAME() + '_{filegroupName}%') BEGIN
                        DECLARE @DatabasePath nvarchar(max)
                        DECLARE @SQL nvarchar(max)
    
                        SELECT TOP 1 @DatabasePath = physical_name
                        FROM sys.master_files
                        WHERE database_id = DB_ID() AND file_id = 1 AND type_desc = N'ROWS'
    
                        SET @SQL = N'{sqlFromFilesCreated.ToString()}'
                        EXECUTE sp_executesql @SQL
                    END
                ";
    
                return sqlFromFileGroup;
            }
    
    
            public static string SetFilegroupToTable(string filegroupName, string tableName, string primaryKeyName, string keyColumns = "Id")
            {
                var sqlFromSettedFilegroup = "";
    
                sqlFromSettedFilegroup = $@"
                    IF EXISTS (SELECT * FROM sys.filegroups where name = '{filegroupName}') AND EXISTS (SELECT * FROM sys.master_files where name like DB_NAME() + '_{filegroupName}%') BEGIN
                        CREATE UNIQUE CLUSTERED INDEX [{primaryKeyName}]
                            ON [dbo].[{tableName}]({keyColumns})
                            WITH (DROP_EXISTING = ON) ON [{filegroupName}]
                    END
                ";
    
                return sqlFromSettedFilegroup;
            }
        }
    
    

    external by BobPusateri  207  0  3  0

    Displays info for all files and filegroups in the current Microsoft SQL Server database

    Displays info for all files and filegroups in the current Microsoft SQL Server database: All_File_Info.sql
    SELECT
       fg.data_space_id AS FilegroupID,
       fg.name AS FileGroupName,
       f.file_id AS FileID,
       f.name AS LogicalName,
       f.physical_name,
       fileproperty(f.name,'SpaceUsed')/128 AS Used_MB,
       (f.size-fileproperty(f.name,'SpaceUsed'))/128 As Free_MB
    FROM
       sys.database_files f
    LEFT JOIN sys.filegroups fg ON f.data_space_id = fg.data_space_id
    ORDER BY
       fg.name,
       f.name;
    
    

    external by Travis Smith  200  0  2  0

    PowerShell: Set AutoGrowth for SQL Server cycling through the individual filegroups as well as excluding system databases.

    PowerShell: Set AutoGrowth for SQL Server cycling through the individual filegroups as well as excluding system databases.: Set-SQLAutoGrouth.ps1
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
    $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "localhost"
    $databases = $server.Databases;
    foreach ($db in $databases ) {
        #Set Log File growth
    	if ($db.Status -eq 'Normal' -and -$db.IsSystemObject -eq $false) {
    		$l = $db.LogFiles[0]
    		$l.GrowthType = "KB"
    		$l.Growth = "51200"
    		$l.Alter();
    		
    		$df = $db.FileGroups
                    foreach($fg in $df){
                           foreach ($f in $fg.Files) {
    			    $f.GrowthType = "KB";
    			    $f.Growth = "102400"
    			    $f.Alter();
    		       }
                    }
    	}
    } 
    
    

    external by David Stevens  108  0  2  0

    A T-SQL stored procedure for moving an index from one file group to another. The original script was found at http://blogs.msdn.com/b/ramoji/archive/2008/09/26/how-to-move-existing-indexes-from-one-filegroup-to-another.aspx and updated according to re...

    A T-SQL stored procedure for moving an index from one file group to another. The original script was found at http://blogs.msdn.com/b/ramoji/archive/2008/09/26/how-to-move-existing-indexes-from-one-filegroup-to-another.aspx and updated according to responses found on the same page.: gistfile1.sql
    /* 
    -- See below for moving a non-clustered file index.  Moving a clustered index is the same
    -- as moving the data, as each row lives as a leaf somewhere in the index tree.
    
    -- Current syntax for moving a CLUSTERED file index:
    CREATE UNIQUE CLUSTERED INDEX 
    	Your_PK_Name
    ON 
        	YourTable(YourColumnList)
    WITH (
    	STATISTICS_NORECOMPUTE = OFF, 
    	SORT_IN_TEMPDB = ON, 
    	IGNORE_DUP_KEY = OFF, 
    	DROP_EXISTING = ON, 
    	ONLINE = ON, 
    	ALLOW_ROW_LOCKS = OFF, 
    	ALLOW_PAGE_LOCKS = OFF 
    )
    ON [SECONDARY];
    */
    
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MoveIndexToFileGroup]') AND type in (N'P', N'PC'))
    	BEGIN
    		DROP PROCEDURE [dbo].[MoveIndexToFileGroup]
    	END
    GO
    
    CREATE PROC [dbo].[MoveIndexToFileGroup] (        
    	@DBName sysname,   
    	@SchemaName sysname = 'dbo',       
    	@ObjectNameList Varchar(Max),        
    	@IndexName sysname = null,  
    	@FileGroupName varchar(100)  
    ) WITH RECOMPILE
    
    AS        
      
    BEGIN  
         
    	SET NOCOUNT ON;
      
    	DECLARE @IndexSQL NVarchar(Max)  
    	DECLARE @IndexKeySQL NVarchar(Max)  
    	DECLARE @IncludeColSQL NVarchar(Max)  
    	DECLARE @FinalSQL NVarchar(Max)  
      
    	DECLARE @CurLoopCount Int  
    	DECLARE @MaxLoopCount Int  
    	DECLARE @StartPos Int  
    	DECLARE @EndPos Int  
      
    	DECLARE @ObjectName sysname  
    	DECLARE @IndName sysname  
    	DECLARE @IsUnique Varchar(10)  
    	DECLARE @Type Varchar(25)  
    	DECLARE @IsPadded Varchar(5)  
    	DECLARE @IgnoreDupKey Varchar(5) 
    	DECLARE @AllowRowLocks Varchar(5)  
    	DECLARE @AllowPageLocks Varchar(5) 
    	DECLARE @FillFactor Int  
    	DECLARE @ExistingFGName Varchar(Max) 
    	DECLARE @FilterDef NVarchar(Max)
      
    	DECLARE @ErrorMessage NVARCHAR(4000)  
    	DECLARE @SQL nvarchar(4000)  
    	DECLARE @RetVal Bit  
      
    	DECLARE @ObjectList Table(Id Int Identity(1,1),ObjectName sysname)  
      
    	DECLARE @WholeIndexData TABLE (
    		ObjectName SYSNAME
    		,IndexName SYSNAME
    		,Is_Unique BIT
    		,Type_Desc VARCHAR(25)
    		,Is_Padded BIT
    		,[Ignore_Dup_Key] BIT
    		,[Allow_Row_Locks] BIT
    		,[Allow_Page_Locks] BIT
    		,Fill_Factor INT
    		,Is_Descending_Key BIT
    		,ColumnName SYSNAME
    		,Is_Included_Column BIT
    		,FileGroupName VARCHAR(MAX)
    		,Has_Filter BIT
    		,Filter_Definition NVARCHAR(MAX)
    		,key_ordinal TINYINT
    	)
    
    	DECLARE @DistinctIndexData TABLE (
    		Id INT IDENTITY(1, 1)
    		,ObjectName SYSNAME
    		,IndexName SYSNAME
    		,Is_Unique BIT
    		,Type_Desc VARCHAR(25)
    		,Is_Padded BIT
    		,[Ignore_Dup_Key] BIT
    		,[Allow_Row_Locks] BIT
    		,[Allow_Page_Locks] BIT
    		,Fill_Factor INT
    		,FileGroupName VARCHAR(Max)
    		,Has_Filter BIT
    		,Filter_Definition NVARCHAR(Max)
    	)
      
    -------------Validate arguments----------------------   
      
    	IF(@DBName IS NULL)  
    		BEGIN  
    			SELECT @ErrorMessage = 'Database Name must be supplied.'   
    			GOTO ABEND  
    		END  
      
    	IF(@ObjectNameList IS NULL)  
    		BEGIN  
    			SELECT @ErrorMessage = 'Table or View Name(s) must be supplied.'   
    			GOTO ABEND  
    		END  
      
    	IF(@FileGroupName IS NULL)  
    		BEGIN  
    			SELECT @ErrorMessage = 'FileGroup Name must be supplied.'   
    			GOTO ABEND  
    		END  
      
    	--Check for the existence of the Database  
    	IF NOT EXISTS(SELECT Name FROM sys.databases where Name = @DBName) 
    		BEGIN 
    			SET @ErrorMessage = 'The specified Database does not exist' 
    			GOTO ABEND
    		END
      
    	--Check for the existence of the Schema  
    	IF (upper(@SchemaName) <> 'DBO')
    		BEGIN
    			SET @SQL = 'SELECT @RetVal = COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.schemas WHERE name = ''' + @SchemaName + ''''
    
    			BEGIN TRY
    				EXEC sp_executesql @SQL, N'@RetVal Bit OUTPUT', @RetVal OUTPUT
    			END TRY
    			BEGIN CATCH
    				SELECT @ErrorMessage = ERROR_MESSAGE()
    				GOTO ABEND
    			END CATCH
    
    			IF (@RetVal = 0)
    				BEGIN
    					SELECT @ErrorMessage = 'No Schema with the name ' + @SchemaName + ' exists in the Database ' + @DBName
    					GOTO ABEND
    				END
    		END 
      
    	--CHECK FOR THE EXISTENCE OF THE FILEGROUP  
    	SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.filegroups WHERE name = ''' + @FileGroupName + ''''  
    	BEGIN TRY  
    		EXEC sp_executesql @SQL,N'@RetVal Bit OUTPUT',@RetVal OUTPUT  
    	END TRY  
    	BEGIN CATCH  
    		SELECT @ErrorMessage = ERROR_MESSAGE()   
    		GOTO ABEND  
    	END CATCH  
      
    	IF(@RetVal = 0)  
    		BEGIN  
    			SELECT @ErrorMessage = 'No FileGroup with the name ' + @FileGroupName + ' exists in the Database ' + @DBName   
    			GOTO ABEND  
    		END  
      
    ----------Get the objects from the concatenated list----------------------------------------------------  
      
    SET @StartPos = 0  
    SET @EndPos = 0  
      
    WHILE(@EndPos >= 0)  
    BEGIN  
      
     SELECT @EndPos = CHARINDEX(',',@ObjectNameList,@StartPos)  
     IF(@EndPos = 0) --Means, separator is not found  
     BEGIN  
      INSERT INTO @ObjectList  
      SELECT SUBSTRING(@ObjectNameList,@StartPos,(LEN(@ObjectNameList) - @StartPos)+1)   
         
      BREAK  
     END  
       
     INSERT INTO @ObjectList  
     SELECT SUBSTRING(@ObjectNameList,@StartPos,(@EndPos - @StartPos))  
        
     SET @StartPos = @EndPos + 1  
       
    END  
      
    -------------Check for the validity of all the Objects----------------------  
      
    SET @StartPos = 1  
    SELECT @EndPos = COUNT(*) FROM @ObjectList  
      
    WHILE(@StartPos <= @EndPos)  
    BEGIN  
      
     SELECT @ObjectName = ObjectName FROM @ObjectList WHERE Id = @StartPos  
      
     --CHECK FOR EXISTENCE OF THE OBJECT  
     SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.Objects WHERE type IN (''U'',''V'') AND name = ''' + @ObjectName + ''''  
     BEGIN TRY  
      EXEC sp_executesql @SQL,N'@RetVal Int OUTPUT',@RetVal OUTPUT  
     END TRY  
     BEGIN CATCH  
      SELECT @ErrorMessage = ERROR_MESSAGE()   
      GOTO ABEND  
     END CATCH  
      
     IF(@RetVal = 0)  
     BEGIN  
      SELECT @ErrorMessage = 'No Table or View with the name ' + @ObjectName + ' exists in the Database ' + @DBName   
      GOTO ABEND  
     END   
      
     --Check for existence of Index  
     IF(@IndexName IS NOT NULL)  
     BEGIN  
      SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.Indexes si INNER JOIN ' + QUOTENAME(@DBName) + '.sys.Objects so '  
      SET @SQL = @SQL + ' ON si.Object_Id = so.Object_Id WHERE so.Schema_id = ' + CAST(Schema_Id(@Schemaname) as varchar(25))   
      SET @SQL = @SQL + ' AND so.name = ''' + @ObjectName + ''' AND si.name = ''' + @IndexName + ''''   
      
      BEGIN TRY  
       EXEC sp_executesql @SQL,N'@RetVal Int OUTPUT',@RetVal OUTPUT  
      END TRY  
      BEGIN CATCH  
       SELECT @ErrorMessage = ERROR_MESSAGE()   
       GOTO ABEND  
      END CATCH  
      
      IF(@RetVal = 0)  
      BEGIN  
       SELECT @ErrorMessage = 'No Index with the name ' + @IndexName + ' exists on the Object ' + @ObjectName   
       GOTO ABEND  
      END  
     END  
       
     SET @StartPos = @StartPos + 1  
    END  
      
    -------------Loop till all the Objects are processed----------------------  
      
    SET @StartPos = 1  
    SELECT @EndPos = COUNT(*) FROM @ObjectList  
      
    WHILE(@StartPos <= @EndPos)  
    BEGIN  
      
     SELECT @ObjectName = ObjectName FROM @ObjectList WHERE Id = @StartPos  
     
     -------------Build the SQL to get the index data based on the inputs provided----------------------   
      
    
    
     SET @IndexSQL =   
     'SELECT so.Name as ObjectName, si.Name as IndexName,si.Is_Unique,si.Type_Desc'  
     + ',si.Is_Padded,si.Ignore_Dup_Key,si.Allow_Row_Locks,si.Allow_Page_Locks,si.Fill_Factor,sic.Is_Descending_Key'  
     + ',sc.Name as ColumnName,sic.Is_Included_Column,sf.Name as FileGroupName,'+ CASE WHEN @@VERSION LIKE '%Server 2005%' THEN '0 as Has_Filter, N'''' as Filter_Definition' ELSE 'si.Has_Filter,si.Filter_Definition' END +',sic.Key_Ordinal FROM '
     + QUOTENAME(@DBName) + '.sys.Objects so INNER JOIN ' + QUOTENAME(@DBName) + '.sys.Indexes si ON so.Object_Id = si.Object_id INNER JOIN '  
     + QUOTENAME(@DBName) + '.sys.FileGroups sf ON sf.Data_Space_Id = si.Data_Space_Id INNER JOIN '   
     + QUOTENAME(@DBName) + '.sys.Index_columns sic ON si.Object_Id = sic.Object_Id AND si.Index_id = sic.Index_id INNER JOIN '  
     + QUOTENAME(@DBName) + '.sys.Columns sc ON sic.Column_Id = sc.Column_Id and sc.Object_Id = sic.Object_Id '  
     + ' WHERE so.Name = ''' + @ObjectName  + ''''  
     + ' AND so.Schema_id = ' + CAST(Schema_Id(@Schemaname) as varchar(25)) + ' AND si.Type_Desc = ''NONCLUSTERED'' '  
      
     IF(@IndexName IS NOT NULL)  
     BEGIN  
      SET @IndexSQL = @IndexSQL + ' AND si.Name = ''' + @IndexName + ''''  
     END  
      
     SET @IndexSQL = @IndexSQL + ' ORDER BY ObjectName, IndexName, sic.Key_Ordinal'  
      
     --PRINT @IndexSQL  
      
     -------------INSERT THE INDEX DATA INTO A VARIABLE----------------------   
      
    	BEGIN TRY  
    		INSERT INTO @WholeIndexData
    		EXEC sp_executesql @IndexSQL
    	END TRY  
    	BEGIN CATCH  
    		SELECT @ErrorMessage = ERROR_MESSAGE()   
    		GOTO ABEND  
    	END CATCH  
      
     --Check if any indexes are there on the object. Otherwise exit  
     IF (SELECT COUNT(*) FROM @WholeIndexData) = 0  
     BEGIN  
      SELECT 'Object does not have any nonclustered indexes to move'   
      GOTO FINAL   
     END  
        
     -------------Get the distinct index rows in to a variable----------------------   
      
    INSERT INTO @DistinctIndexData
    SELECT DISTINCT  
    	 ObjectName
    	,IndexName
    	,Is_Unique
    	,Type_Desc
    	,Is_Padded
    	,[Ignore_Dup_Key]
    	,[Allow_Row_Locks]
    	,[Allow_Page_Locks]
    	,Fill_Factor
    	,FileGroupName
    	,Has_Filter
    	,Filter_Definition
    FROM @WholeIndexData
    WHERE ObjectName = @ObjectName;
      
     SELECT @CurLoopCount = Min(Id), @MaxLoopCount = Max(Id) FROM @DistinctIndexData WHERE ObjectName = @ObjectName
      
     --SELECT @CurLoopCount, @MaxLoopCount  
      
     -------------Loop till all the indexes are processed----------------------   
      
     WHILE(@CurLoopCount <= @MaxLoopCount)  
     BEGIN  
      
      SET @IndexKeySQL = ''  
      SET @IncludeColSQL = ''  
      
      -------------Get the current index row to be processed----------------------  
      SELECT   
       @IndName   = IndexName  
       ,@Type   = Type_Desc
       ,@ExistingFGName = FileGroupName
       ,@IsUnique  = CASE WHEN Is_Unique = 1 THEN 'UNIQUE ' ELSE '' END  
       ,@IsPadded  = CASE WHEN Is_Padded = 0 THEN 'OFF,' ELSE 'ON,'  END  
       ,@IgnoreDupKey = CASE WHEN Ignore_Dup_Key = 0 THEN 'OFF,' ELSE 'ON,' END  
       ,@AllowRowLocks = CASE WHEN Allow_Row_Locks = 0 THEN 'OFF,' ELSE 'ON,' END 
       ,@AllowPageLocks = CASE WHEN Allow_Page_Locks = 0 THEN 'OFF,' ELSE 'ON,' END  
       ,@FillFactor  = CASE WHEN Fill_Factor = 0 THEN 100 ELSE Fill_Factor END  
       ,@FilterDef  = CASE WHEN Has_Filter = 1 THEN (' WHERE ' + Filter_Definition) ELSE '' END  
      FROM @DistinctIndexData   
      WHERE Id = @CurLoopCount  
        
      -------------Check if the index is already not part of that FileGroup----------------------  
      
      IF(@ExistingFGName = @FileGroupName)  
      BEGIN  
       PRINT 'Index ' +  @IndName + ' is NOT moved as it is already part of the FileGroup ' + @FileGroupName + '.'  
       SET @CurLoopCount = @CurLoopCount + 1  
       CONTINUE  
      END  
      
      ------- Construct the Index key string along with the direction--------------------  
    	SELECT @IndexKeySQL = CASE 
    			WHEN @IndexKeySQL = ''
    				THEN (
    						@IndexKeySQL + QUOTENAME(ColumnName) + CASE 
    							WHEN Is_Descending_Key = 0
    								THEN ' ASC'
    							ELSE ' DESC'
    							END
    						)
    			ELSE (
    					@IndexKeySQL + ',' + QUOTENAME(ColumnName) + CASE 
    						WHEN Is_Descending_Key = 0
    							THEN ' ASC'
    						ELSE ' DESC'
    						END
    					)
    			END
    	FROM @WholeIndexData
    	WHERE ObjectName = @ObjectName
    		AND IndexName = @IndName
    		AND Is_Included_Column = 0
    	ORDER BY key_ordinal ASC
    
        
      --PRINT @IndexKeySQL   
        
      ------ Construct the Included Column string --------------------------------------  
      SELECT   
       @IncludeColSQL =   
       CASE  
       WHEN @IncludeColSQL = '' THEN (@IncludeColSQL + QUOTENAME(ColumnName))   
       ELSE (@IncludeColSQL + ',' + QUOTENAME(ColumnName))   
       END   
      FROM @WholeIndexData  
      WHERE ObjectName = @ObjectName   
      AND IndexName = @IndName   
      AND Is_Included_Column = 1
      ORDER BY key_ordinal ASC  
        
      --PRINT @IncludeColSQL  
      
      -------------Construct the final Create Index statement----------------------  
      SELECT 
      @FinalSQL = 'CREATE ' + @IsUnique + @Type + ' INDEX ' + QUOTENAME(@IndName) 
      + ' ON ' + QUOTENAME(@DBName) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)  
      + '(' + @IndexKeySQL + ') '   
      + CASE WHEN LEN(@IncludeColSQL) <> 0 THEN  'INCLUDE(' + @IncludeColSQL + ') ' ELSE '' END
      + @FilterDef  
      + ' WITH ('   
      + 'PAD_INDEX = ' + @IsPadded   
      + 'IGNORE_DUP_KEY = ' + @IgnoreDupKey  
      + 'ALLOW_ROW_LOCKS  = ' + @AllowRowLocks   
      + 'ALLOW_PAGE_LOCKS  = ' + @AllowPageLocks   
      + 'SORT_IN_TEMPDB = OFF,'   
      + 'DROP_EXISTING = ON,'   
      + 'ONLINE = OFF,'  
      + 'FILLFACTOR = ' + CAST(@FillFactor AS Varchar(3))  
      + ') ON ' + QUOTENAME(@FileGroupName)  
      
      --PRINT @FinalSQL  
      
      -------------Execute the Create Index statement to move to the specified filegroup----------------------  
      BEGIN TRY  
       EXEC sp_executesql @FinalSQL  
      END TRY  
      BEGIN CATCH  
       SELECT @ErrorMessage = ERROR_MESSAGE()   
       GOTO ABEND  
      END CATCH   
      PRINT 'Index ' +  @IndName + ' on Object ' + @ObjectName + ' is moved successfully.'   
        
      SET @CurLoopCount = @CurLoopCount + 1  
      
     END  
       
     SET @StartPos = @StartPos + 1  
    END  
     SELECT 'The procedure completed successfully.'  
     RETURN  
      
    ABEND:  
     RAISERROR (@ErrorMessage, 16, 1);
      
    FINAL:  
     RETURN    
    END 
    
    GO
    
    

    List Complete ( 7 snippets total )

    • Public Snippets
    • Channels Snippets