SQL Server 2005/2008/2008R2: HTTP endpoints

You can use an HTTP endpoint to allow access to data over HTTP and Secure Sockets Layer (HTTPS) without needing to install Internet Information Services (IIS).

Important Note: HTTP Endpoints feature has been deprecate in SQL Server 2012 and later versions.  For more information, see here

Click here to read full article on SSWUG.org

Advertisements

Configuring SQL Server Database Mail Feature

You can send e-mail from within stored procedures, functions, and triggers by using SQL Server Database Mail. You can also configure SQL Server Agent Mail to use Database Mail. Some features of Database Mail include:

  • Uses Simple Mail Transport Protocol (SMTP)
  • Does not require Outlook or Extended Messaging Application Programming Interface (MAPI) support
  • Can use multiple SMTP servers
  • Can be used in a cluster server configuration
  • Supports asynchronous background delivery
  • Supports multiple profiles and accounts
  • Supports security features, included prohibited file extensions, a limit on attachment size, and auditing
  • Allows you to send messages formatted in HTML

Click here to read full article on SSWUG.org

The database principal owns a schema in the database, and cannot be dropped

Problem

You are trying to drop a database user, but are getting the following error message:

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

This error is self-explanatory as it tells you that the database user you are trying to drop is the database schema owner.

Resolution

To successfully drop the database user, you must find all the schemas that are owned by the database user, and then transfer their ownership to another user.

Here is the Transact-SQL script, which I wrote a while ago, to drop the database user. This script first transfer’s ownership of all database schemas associated with particular database user to the specified database user, and then drops that database user from the database.

To use this script, change the following two local variables of this script:

  • @SQLUser – Specify the name of the database user that you want to drop
  • @NewSchemaOwner – Specify the name of the database user that will be used as new schema owner for the schemas that is owned by the database user, which you are dropping

-- Ensure a USE database_name statement has been executed first.
SET NOCOUNT ON;

DECLARE @ID [int] ,
		@CurrentCommand [nvarchar](MAX) ,
		@ErrorMessage   [nvarchar](2000) ,
		@SQLUser        [sysname] , --Specify the name of the database user that you want to drop
		@NewSchemaOwner [sysname];  --Specify the name of the database user that will be used as new schema
								    --owner for the schemas that is owned by the database user you are dropping

SET @SQLUser = N'Specify_Database_User_You_Want_To_Drop'; --Example: testuser
SET @NewSchemaOwner = N'Specify_Database_User_Who_Will_User_As_New_Schema_Owner'; --Example: liveuser

DECLARE @Work_To_Do TABLE
    (
      [ID] [int] IDENTITY(1, 1)
                 PRIMARY KEY ,
      [TSQL_Text] [varchar](1024) ,
      [Completed] [bit]
    );

INSERT  INTO @Work_To_Do
        ( [TSQL_Text] ,
          [Completed]
        )
        SELECT  N'ALTER AUTHORIZATION ON SCHEMA::' + [name] + SPACE(1) + 'TO'
                + SPACE(1) + QUOTENAME(@NewSchemaOwner) ,
                0
        FROM    [sys].[schemas]
        WHERE   [principal_id] = USER_ID(@SQLUser);

INSERT  INTO @Work_To_Do
        ( [TSQL_Text] ,
          [Completed]
        )
        SELECT  N'DROP USER' + SPACE(1) + @SQLUser ,
                0

SELECT  @ID = MIN([ID])
FROM    @Work_To_Do
WHERE   [Completed] = 0;

WHILE @ID IS NOT NULL
    BEGIN
        SELECT  @CurrentCommand = [TSQL_Text]
        FROM    @Work_To_Do
        WHERE   [ID] = @ID;

        BEGIN TRY
            EXEC [sys].[sp_executesql] @CurrentCommand
            PRINT @CurrentCommand
        END TRY
        BEGIN CATCH

            SET @ErrorMessage = N'"Oops, an error occurred that could not be resolved. For more information, see below:'
                + CHAR(13) + ERROR_MESSAGE() 

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

            GOTO ChooseNextCommand
        END CATCH

        ChooseNextCommand:

        UPDATE  @Work_To_Do
        SET     [Completed] = 1
        WHERE   [ID] = @ID

        SELECT  @ID = MIN([ID])
        FROM    @Work_To_Do
        WHERE   [Completed] = 0
    END;

SET NOCOUNT OFF;

Verify a success of database backups

01One of the most important tasks on a DBA’s to-do list is backing up databases on a regular basis. This is because reliable backups are the most important tool in ensuring data recovery. Therefore, it is important for the DBA to check for database backups and validate that they have been successfully created and saved in a secure location.

SQL Server keeps information about each successful backup in the msdb database. Information about each successful backup operation is stored in the backupset table, and information about each backup physical file in the backupmediafamily table. I wrote the following query, which can be used to check all databases’ backup status for any given SQL Server instance:

USE [msdb]
GO

;WITH [MostRecentBackupStatus_CTE]
AS
(
	SELECT  bsfull.[server_name] ,
			bsfull.[database_name] ,
			bsfull.[backup_finish_date] AS [last_full_backup] ,
			bsdiff.[backup_finish_date] AS [last_diff_backup] ,
			bstlog.[backup_finish_date] AS [last_tran_backup] ,
			DATEDIFF(dd, bsfull.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_full_backup] ,
			DATEDIFF(dd, bsdiff.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_diff_backup] ,
			DATEDIFF(hh, bstlog.[backup_finish_date], CURRENT_TIMESTAMP) AS [hours_since_tranlog_backup] ,
			( SELECT    [physical_device_name]
			  FROM      [msdb]..[backupmediafamily] bmf
			  WHERE     bmf.[media_set_id] = bsfull.[media_set_id]
			) AS [full_backup_location] ,
			( SELECT    [physical_device_name]
			  FROM      [msdb]..[backupmediafamily] bmf
			  WHERE     bmf.[media_set_id] = bsdiff.[media_set_id]
			) AS [diff_backup_location] ,
			( SELECT    [physical_device_name]
			  FROM      [msdb]..[backupmediafamily] bmf
			  WHERE     bmf.[media_set_id] = bstlog.[media_set_id]
			) AS [tlog_backup_location]
	FROM    [msdb]..[backupset] AS bsfull
			LEFT JOIN [msdb]..[backupset] AS bstlog ON bstlog.[database_name] = bsfull.[database_name]
													   AND bstlog.[server_name] = bsfull.[server_name]
													   AND bstlog.[type] = 'L'
													   AND bstlog.[backup_finish_date] = ( (SELECT	MAX([backup_finish_date])
																							FROM	[msdb]..[backupset] b2
																							WHERE	b2.[database_name] = bsfull.[database_name]
																									AND b2.[server_name] = bsfull.[server_name]
																									AND b2.[type] = 'L') )
			LEFT JOIN [msdb]..[backupset] AS bsdiff ON bsdiff.[database_name] = bsfull.[database_name]
													   AND bsdiff.[server_name] = bsfull.[server_name]
													   AND bsdiff.[type] = 'I'
													   AND bsdiff.[backup_finish_date] = ( (SELECT	MAX([backup_finish_date])
																							FROM	[msdb]..[backupset] b2
																							WHERE	b2.[database_name] = bsfull.[database_name]
																									AND b2.[server_name] = bsfull.[server_name]
																									AND b2.[type] = N'I') )
	WHERE   bsfull.[type] = N'D'
			AND bsfull.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
												 FROM   [msdb]..[backupset] b2
												 WHERE  b2.[database_name] = bsfull.[database_name]
														AND b2.[server_name] = bsfull.[server_name]
														AND b2.[type] = N'D') )
			AND EXISTS ( SELECT [name]
						 FROM   [master].[sys].[databases]
						 WHERE  [name] = bsfull.[database_name] )
			AND bsfull.[database_name] <> N'tempdb'
)
SELECT  c.[server_name] ,
        c.[database_name] ,
        d.[recovery_model_desc] ,
        c.[last_full_backup] ,
        c.[last_diff_backup] ,
        c.[last_tran_backup] ,
        c.[days_since_full_backup] ,
        c.[days_since_diff_backup] ,
        c.[hours_since_tranlog_backup] ,
        c.[full_backup_location] ,
        c.[diff_backup_location] ,
        c.[tlog_backup_location]
FROM    [MostRecentBackupStatus_CTE] c
        INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];
GO

This query returns the following columns:

  • server_name – Specifies the name of the SQL Server
  • database_name – Specifies the name of the database.
  • recovery_model_desc – Specifies the recovery model of the database.
  • last_full_backup – Specifies the date time of the latest full backup.
  • last_diff_backup – Specifies the date time of the latest differential backup.
  • last_tran_backup – Specifies the date time of the latest transactional log backup.
  • days_since_full_backup – Number of days since the last successful full backup.
  • days_since_diff_backup – Number of days since the last successful differential backup.
  • hours_since_tranlog_backup – Number of days since the last successful log backup
  • full_backup_location – Specifies the physical name of the full backup file.
  • diff_backup_location – Specifies the physical name of the differential backup file.
  • tlog_backup_location – Specifies the physical name of the transactional backup file.

I have created a SSIS package that runs this script on a daily basis on each of my Production SQL Servers and then copy that information into our CMS database. This approach helps me to quickly identify the databases where backup is not successful.

Please share your approach, which you use to accomplish this task. It will be interesting to know that.

Different approaches of counting number of rows in a table

Today, I received an email from the developer asking if there is a better way instead of the COUNT (*) Transact-SQL statement, to count the number of records in a table. My reply to his question is yes, there are several methods to get this information from SQL Server. However, none of these approaches are perfect, and has its own disadvantages. In this blog post, I will show different methods of count number of rows in a table.

Before discussing the different approaches, I must emphasize that the COUNT (*) statement gives you the true count of the total number of rows in a table. The COUNT (*) statement performs the full table scan on heap table and cluster index scan on tables with clustered index, to get the exact count of the records in a table. Because of this, it can get slower as the table gets bigger, as effectively it is counting each row separately, which includes the rows that contains null values. For more information about COUNT (*), see MSDN resource here.

 Let’s take a look at different approaches of counting number of records in a table:

Approach 1: Counting rows using sys.partitions catalog view

As we know that we can use sys.partitions catalog view to check the structure of the table. This catalog returns one row for each partition of all tables and most types of indexes (except Full-Text, Spatial, and XML are not included in this view) in the database. We can join the sys.partitions catalog view with sys.tables catalog view to quickly get row count for all tables in the database. Here is the sample sys.partitions query, which I use to get the row count for all tables in the database:

SET NOCOUNT ON;
SET STATISTICS IO ON;

-- Ensure a USE [databasename] statement has been executed first.
SELECT SCHEMA_NAME(t.[schema_id]) AS [table_schema]
      ,OBJECT_NAME(p.[object_id]) AS [table_name]
      ,SUM(p.[rows]) AS [row_count]
FROM [sys].[partitions] p
INNER JOIN [sys].[tables] t ON p.[object_id] = t.[object_id]
WHERE p.[index_id] < 2
GROUP BY p.[object_id]
	,t.[schema_id]
ORDER BY 1, 2 ASC
OPTION (RECOMPILE);

Here is output when I run it against AdventureWork2012 system database:

01

Although it is one of the fastest ways to count the number of rows in a table, however, according to sys.partitions documentation, the count is not always accurate. I personally could not find anything on the MSDN that tells me the cases where the count will not be accurate. The only situation in which I found count is not accurate is when I run this query while DML operations are in progress against the table for which I am counting.

Approach 2: Counting table rows using sys.dm_db_partition_stats dynamic management view

Like sys.partitions, we can use sys.dm_db_partition_stats dynamic management view to count the number of rows in a table. This dynamic management view contains row-count information for every partition and displays the information about the space used to store and manage different data allocation unit types.  According to MSDN, the row_count column of sys.dm_db_partition_stats dynamic management view is approximate value, and Microsoft never reveals cases where counts are not accurate. However, similar to sys.partitions catalog view, the only situation in which I found count is not accurate is when I use it while DML operations are in progress against the table for which I am counting. Here is my query, which combines sys.dm_db_partition_stats dynamic management view and sys.tables catalog view, to get the row count for all tables in the database:

SET NOCOUNT ON;
SET STATISTICS IO ON;

-- Ensure a USE [databasename] statement has been executed first.
SELECT SCHEMA_NAME(t.[schema_id]) AS [table_schema]
      ,t.[name] AS [table_name]
      ,SUM(ps.[row_count]) AS [row_count]
FROM [sys].[tables] t
INNER JOIN [sys].[dm_db_partition_stats] ps
     ON ps.[object_id] = t.[object_id]
WHERE [index_id] < 2
GROUP BY t.[name]
	,t.[schema_id]
ORDER BY 1, 2 ASC
OPTION (RECOMPILE);

Here is output when I run it against AdventureWork2012 system database:

02

When I compared STATISTICS IO output of both queries, I noticed that the sys.dm_db_partition_stats dynamic management view query version is slightly faster than sys.partitions catalog view query version. Examine the STATISTICS IO output of both queries that shows that SQL Server is doing a less work to retrieve the row count information using sys.dm_db_partition_stats dynamic management view query version.

STATISTICS IO output for sys.partitions version of query:

Table 'syssingleobjrefs'. Scan count 75, logical reads 151, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 77, logical reads 175, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 0, logical reads 768, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysrowsets'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

STATISTICS IO output for sys.dm_db_partition_stats version of query:

Table 'syssingleobjrefs'. Scan count 75, logical reads 151, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 76, logical reads 164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 0, logical reads 384, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Approach 3: Counting table rows using sp_spaceused system stored procedure

We can use the sp_spaceused system-stored procedure to return space usage information about a database or a table within a database. The syntax for this procedure is:

sp_spaceused [@objectname=['objectname']]
[,[@updateusage=['TRUE|FASLE']]]

You use the objectname parameter to enter the name of a table, indexed view, or Service Broker queue to retrieve space usage information specific to that object. If you set the updateusage to TRUE, DBCC UPDATEUSAGE updates usage information. The default value for this parameter is FALSE. You can run the command without any parameters to retrieve information about the current database:

EXEC [sp_spaceused]

This returns the following information:

  • Current database name
  • Current database size
  • Unallocated space
  • Reserved space
  • Space used by data
  • Space used by indexes
  • Unused space

To view information for a table in the current database, you must include the table name. For example, to look at information for the Person.Address table in the AdventureWorks2012 database, you would run:

EXEC [sp_spaceused] 'Person.Address'

This would return:

  • Table name
  • Number of rows in the table
  • Reserved space
  • Space used by data
  • Space used by indexes
  • Unused space

Here is the sample output in SSMS:

03

It is not necessary to include the parameter names (@parametername =) when running this command.

Finally, here is my script that uses sp_spaceused system-stored procedure to return the table usage information for all database tables, which includes count of rows in the table:

SET NOCOUNT ON;

-- Ensure a USE [databasename] statement has been executed first.
DECLARE @Database		            [nvarchar] (256)
       ,@TSQLCommand01              [nvarchar] (MAX)

SET @Database = DB_NAME()

IF OBJECT_ID(N'TempDb.dbo.#Table_Size_Info') IS NOT NULL
	DROP TABLE #Table_Size_Info

CREATE TABLE #Table_Size_Info (
	 [ID] [int] IDENTITY(1, 1) PRIMARY KEY
	,[ObjectName] [sysname]
	,[NumRows] [bigint]
	,[Reserved] [varchar](30)
	,[Data] [varchar](30)
	,[IndexSize] [varchar](30)
	,[Unused] [varchar](30)
	,[ObjectType] [char](1)
	)

SET @TSQLCommand01 = N''
SET @TSQLCommand01 = N'USE' + SPACE(1) + QUOTENAME(@Database) + N';' + SPACE(1) + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'DECLARE @SQLStatementID02 [smallint] ,' + SPACE(1) + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'@CurrentObjectSchema         [sysname] ,' + SPACE(1) + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'@CurrentObjectName           [sysname] ,' + SPACE(1) + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'@CurrentObjectFullName [sysname] ,' + SPACE(1) + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'@CurrentObjectType           [char](1)' + SPACE(1) + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'DECLARE @AllObjects TABLE ( [ID] [int] IDENTITY(1, 1) PRIMARY KEY , [ObjectSchema] [sysname] , [ObjectName] [sysname] , [ObjectType] [char](1) , [Completed] [bit] );' + SPACE(1) + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'INSERT INTO @AllObjects ([ObjectSchema], [ObjectName], [ObjectType], [Completed])' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'SELECT  [TABLE_SCHEMA] , [TABLE_NAME] , N''T'' , 0' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'FROM    [INFORMATION_SCHEMA].[TABLES]' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'WHERE   [TABLE_TYPE] = N''BASE TABLE''' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'AND CHARINDEX(N'''''''' , [TABLE_NAME]) = 0' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'ORDER BY [TABLE_SCHEMA], [TABLE_NAME]' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'SELECT @SQLStatementID02 = MIN([ID]) FROM @AllObjects WHERE [Completed] = 0' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'WHILE @SQLStatementID02 IS NOT NULL' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'BEGIN' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + CHAR(9) + N'SELECT @CurrentObjectSchema = [ObjectSchema] , @CurrentObjectName = [ObjectName] , @CurrentObjectType = [ObjectType]' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + CHAR(9) + N'FROM @AllObjects WHERE [ID] = @SQLStatementID02' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + CHAR(9) + N'SET @CurrentObjectFullName = QUOTENAME(@CurrentObjectSchema) + ''.'' + QUOTENAME(@CurrentObjectName)' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + CHAR(9) + N'INSERT INTO #Table_Size_Info ([ObjectName] , [NumRows] , [Reserved] , [Data] , [IndexSize] , [Unused] )' + CHAR(13) + N'EXEC [sp_spaceused] @CurrentObjectFullName' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + CHAR(9) + N'UPDATE #Table_Size_Info SET [ObjectName] = @CurrentObjectFullName , [ObjectType] = @CurrentObjectType WHERE [ID] = SCOPE_IDENTITY();' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + CHAR(9) + N'UPDATE @AllObjects' + CHAR(13) + N'SET [Completed] = 1' + CHAR(13) + N'WHERE [ID] = @SQLStatementID02' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + CHAR(9) + N'SELECT @SQLStatementID02 = MIN([ID]) FROM @AllObjects WHERE [Completed] = 0' + CHAR(13)
SET @TSQLCommand01 = @TSQLCommand01 + N'END' + CHAR(13)

EXEC [sp_executesql] @TSQLCommand01

SELECT *
FROM #Table_Size_Info
GO

Here is the output when I executed against AdventureWorks2012 database:

04

I have also compared STATISTICS IO output of this query with above two queries and noticed that it is much slower than other two queries that I shared in this blog post, but still much faster than equivalent SELECT COUNT(*) version.

I hope you liked this post 🙂 . Please feel free to use comment section, If you have any question regarding this post. You can also drop me question via twitter (@BasitAali).

Rebuild all indexes on all tables in the SQL Server database

One of the key tasks of a DBA is to maintain the database indexes and make sure they are not fragmented. You can use a sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes based on fragmentation. However, sometimes we may need to rebuild all indexes on all tables in the database, especially if you have to change any index property such as fill factor, compression, etc.

For this task, I wrote a following script that accepts parameters to change the properties of the indexes, and dynamically generate and execute ALTER INDEX statements. This script is compatible with SQL Server 2005 and above versions.

Here is this script:

--/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--Arguments				Data Type				Description
--------------			------------			------------
--@FillFactor			[int]					Specifies a percentage that indicates how full the Database Engine should make the leaf level
--												of each index page during index creation or alteration. The valid inputs for this parameter
--												must be an integer value from 1 to 100 The default is 0.
--												For more information, see http://technet.microsoft.com/en-us/library/ms177459.aspx.

--@PadIndex				[varchar](3)			Specifies index padding. The PAD_INDEX option is useful only when FILLFACTOR is specified,
--												because PAD_INDEX uses the percentage specified by FILLFACTOR. If the percentage specified
--												for FILLFACTOR is not large enough to allow for one row, the Database Engine internally
--												overrides the percentage to allow for the minimum. The number of rows on an intermediate
--												index page is never less than two, regardless of how low the value of fillfactor. The valid
--												inputs for this parameter are ON or OFF. The default is OFF.
--												For more information, see http://technet.microsoft.com/en-us/library/ms188783.aspx.

--@SortInTempDB			[varchar](3)			Specifies whether to store temporary sort results in tempdb. The valid inputs for this
--												parameter are ON or OFF. The default is OFF.
--												For more information, see http://technet.microsoft.com/en-us/library/ms188281.aspx.

--@OnlineRebuild		[varchar](3)			Specifies whether underlying tables and associated indexes are available for queries and data
--												modification during the index operation. The valid inputs for this parameter are ON or OFF.
--												The default is OFF.
--												Note: Online index operations are only available in Enterprise edition of Microsoft
--														SQL Server 2005 and above.
--												For more information, see http://technet.microsoft.com/en-us/library/ms191261.aspx.

--@DataCompression		[varchar](4)			Specifies the data compression option for the specified index, partition number, or range of
--												partitions. The options  for this parameter are as follows:
--													> NONE - Index or specified partitions are not compressed.
--													> ROW  - Index or specified partitions are compressed by using row compression.
--													> PAGE - Index or specified partitions are compressed by using page compression.
--												The default is NONE.
--												Note: Data compression feature is only available in Enterprise edition of Microsoft
--														SQL Server 2005 and above.
--												For more information about compression, see http://technet.microsoft.com/en-us/library/cc280449.aspx.

--@MaxDOP				[int]					Overrides the max degree of parallelism configuration option for the duration of the index
--												operation. The valid input for this parameter can be between 0 and 64, but should not exceed
--												number of processors available to SQL Server.
--												For more information, see http://technet.microsoft.com/en-us/library/ms189094.aspx.
--- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*/

-- Ensure a USE <databasename> statement has been executed first.

SET NOCOUNT ON;

DECLARE  @Version							[numeric] (18, 10)
		,@SQLStatementID					[int]
		,@CurrentTSQLToExecute				[nvarchar](max)
		,@FillFactor						[int]		 = 100 -- Change if needed
		,@PadIndex							[varchar](3) = N'OFF' -- Change if needed
		,@SortInTempDB						[varchar](3) = N'OFF' -- Change if needed
		,@OnlineRebuild						[varchar](3) = N'OFF' -- Change if needed
		,@LOBCompaction						[varchar](3) = N'ON' -- Change if needed
		,@DataCompression					[varchar](4) = N'NONE' -- Change if needed
		,@MaxDOP							[int]		 = NULL -- Change if needed
		,@IncludeDataCompressionArgument	[char](1);

IF OBJECT_ID(N'TempDb.dbo.#Work_To_Do') IS NOT NULL
    DROP TABLE #Work_To_Do
CREATE TABLE #Work_To_Do
    (
      [sql_id] [int] IDENTITY(1, 1)
                     PRIMARY KEY ,
      [tsql_text] [varchar](1024) ,
      [completed] [bit]
    )

SET @Version = CAST(LEFT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)), CHARINDEX('.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - 1) + N'.' + REPLACE(RIGHT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)), LEN(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - CHARINDEX('.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)))), N'.', N'') AS [numeric](18, 10))

IF @DataCompression IN (N'PAGE', N'ROW', N'NONE')
	AND (
		@Version >= 10.0
		AND SERVERPROPERTY(N'EngineEdition') = 3
		)
BEGIN
	SET @IncludeDataCompressionArgument = N'Y'
END

IF @IncludeDataCompressionArgument IS NULL
BEGIN
	SET @IncludeDataCompressionArgument = N'N'
END

INSERT INTO #Work_To_Do ([tsql_text], [completed])
SELECT 'ALTER INDEX [' + i.[name] + '] ON' + SPACE(1) + QUOTENAME(t2.[TABLE_CATALOG]) + '.' + QUOTENAME(t2.[TABLE_SCHEMA]) + '.' + QUOTENAME(t2.[TABLE_NAME]) + SPACE(1) + 'REBUILD WITH (' + SPACE(1) + + CASE
		WHEN @PadIndex IS NULL
			THEN 'PAD_INDEX =' + SPACE(1) + CASE i.[is_padded]
					WHEN 1
						THEN 'ON'
					WHEN 0
						THEN 'OFF'
					END
		ELSE 'PAD_INDEX =' + SPACE(1) + @PadIndex
		END + CASE
		WHEN @FillFactor IS NULL
			THEN ', FILLFACTOR =' + SPACE(1) + CONVERT([varchar](3), REPLACE(i.[fill_factor], 0, 100))
		ELSE ', FILLFACTOR =' + SPACE(1) + CONVERT([varchar](3), @FillFactor)
		END + CASE
		WHEN @SortInTempDB IS NULL
			THEN ''
		ELSE ', SORT_IN_TEMPDB =' + SPACE(1) + @SortInTempDB
		END + CASE
		WHEN @OnlineRebuild IS NULL
			THEN ''
		ELSE ', ONLINE =' + SPACE(1) + @OnlineRebuild
		END + ', STATISTICS_NORECOMPUTE =' + SPACE(1) + CASE st.[no_recompute]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END + ', ALLOW_ROW_LOCKS =' + SPACE(1) + CASE i.[allow_row_locks]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END + ', ALLOW_PAGE_LOCKS =' + SPACE(1) + CASE i.[allow_page_locks]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END + CASE
		WHEN @IncludeDataCompressionArgument = N'Y'
			THEN CASE
					WHEN @DataCompression IS NULL
						THEN ''
					ELSE ', DATA_COMPRESSION =' + SPACE(1) + @DataCompression
					END
		ELSE ''
		END + CASE
		WHEN @MaxDop IS NULL
			THEN ''
		ELSE ', MAXDOP =' + SPACE(1) + CONVERT([varchar](2), @MaxDOP)
		END + SPACE(1) + ')'
	,0
FROM [sys].[tables] t1
INNER JOIN [sys].[indexes] i ON t1.[object_id] = i.[object_id]
	AND i.[index_id] > 0
	AND i.[type] IN (1, 2)
INNER JOIN [INFORMATION_SCHEMA].[TABLES] t2 ON t1.[name] = t2.[TABLE_NAME]
	AND t2.[TABLE_TYPE] = 'BASE TABLE'
INNER JOIN [sys].[stats] AS st WITH (NOLOCK) ON st.[object_id] = t1.[object_id]
	AND st.[name] = i.[name]

SELECT @SQLStatementID = MIN([sql_id])
FROM #Work_To_Do
WHERE [completed] = 0

WHILE @SQLStatementID IS NOT NULL
BEGIN
	SELECT @CurrentTSQLToExecute = [tsql_text]
	FROM #Work_To_Do
	WHERE [sql_id] = @SQLStatementID

	PRINT @CurrentTSQLToExecute

	EXEC [sys].[sp_executesql] @CurrentTSQLToExecute

	UPDATE #Work_To_Do
	SET [completed] = 1
	WHERE [sql_id] = @SQLStatementID

	SELECT @SQLStatementID = MIN([sql_id])
	FROM #Work_To_Do
	WHERE [completed] = 0
END