Rebuild and Reorganize indexes on all tables in MS database

As part of database maintenance, indexes on databases have to be rebuilt or reorganised depending on how fragmented the indexes are. From the article Reorganize and Rebuild Indexes, the advice is to reorganise index if avg_fragmentation_in_percent value is between 5 and 30 and to rebuild index if it is more than 30%.

The script below queries all fragmented indexes more than 5 percent and using a cursor a loop is performed on the results to rebuild or reorganise  indexes depending on the percentage of fragmentation using dynamic SQL, i.e.

The script can be downloaded from technet gallery  , i.e. if avg_fragmentation_in_percent value is between 5 and 30 then reorganise else rebuild.


declare @tableName nvarchar(500)
declare @indexName nvarchar(500)
declare @indexType nvarchar(55)
declare @percentFragment decimal(11,2)


declare FragmentedTableList cursor for
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE
-- indexstats.avg_fragmentation_in_percent , e.g. >30, you can specify any number in percent
indexstats.avg_fragmentation_in_percent > 5
AND ind.Name is not null
ORDER BY indexstats.avg_fragmentation_in_percent DESC


OPEN FragmentedTableList
FETCH NEXT FROM FragmentedTableList
INTO @tableName, @indexName, @indexType, @percentFragment


WHILE @@FETCH_STATUS = 0
BEGIN
print 'Processing ' + @indexName + 'on table ' + @tableName + ' which is ' + cast(@percentFragment as nvarchar(50)) + ' fragmented'
if(@percentFragment<= 30)
BEGIN
EXEC( 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REBUILD; ')
print 'Finished reorganizing ' + @indexName + 'on table ' + @tableName
END
ELSE
BEGIN
EXEC( 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REORGANIZE;')
print 'Finished rebuilding ' + @indexName + 'on table ' + @tableName
END
FETCH NEXT FROM FragmentedTableList
INTO @tableName, @indexName, @indexType, @percentFragment
END
CLOSE FragmentedTableList
DEALLOCATE FragmentedTableList

SQL function to abbreviate a set of words

The SQL Function script below will abbreviate a set of words using space as delimiter between the words up to number of letters specified

CREATE FUNCTION [dbo].FN_UTILITY_GetAbbreviation
(
-- Add the parameters for the function here
@string NVARCHAR(max),
@delimiter NVARCHAR(2) = ' ',-- default delimiter to space
@intNum int = 5 --default max number characters to be returned to 5, can change it to number of words passed
)
RETURNS NVARCHAR(512)
AS
BEGIN
-- Declare the return variable here
DECLARE @output NVARCHAR(512)
DECLARE @abbr NVARCHAR(512)

— Add the T-SQL statements to compute the return value here
DECLARE @level INT
SET @level = 0

DECLARE @lastDotPosition INT
SET @lastDotPosition = 0

DECLARE @nextDotPosition INT

SET @output = @string
SET @abbr = ”
–depends on number of string let’s say up to 5
WHILE(@level 0 THEN CHARINDEX(@delimiter, @string, @lastDotPosition+1) ELSE LEN(@string)+1 END
SET @output = SUBSTRING(@string, @lastDotPosition, @nextDotPosition-@lastDotPosition) –substring(@string, @lastDotPosition, charindex(‘.’, @string))
SET @output = REPLACE(@output,@delimiter,”)
SET @abbr = @abbr + LEFT(@output,1);
SET @lastDotPosition = @nextDotPosition
SET @level = @level + 1
END

— Return the result of the function
RETURN @abbr

END

Example: select [dbo].CPS_FN_UTILITY_GetAbbreviation(‘United States’)
US
select [dbo].CPS_FN_UTILITY_GetAbbreviation(‘Software as a service’)
SAAS

EOMONTH Function in SQL

EOMONTH returns the last day of the month that is the indicated number of months before or after start_date.

Syntax

SELECT EOMONTH(start_date, months)

The EOMONTH function syntax has the following arguments:

1. Start_date: Required argument. A date that represents the starting date.

2. Months: Optional argument. The number of months before or after start_date. A positive value for months returns a future date; a negative value returns a past date.

Please note that if months is not an integer, it is truncated.

Examples
select EOMONTH(‘2015-10-27’)
– returns ‘2015-10-31’
select EOMONTH(‘2015-10-27’,1)
– returns ‘2015-11-30’
select EOMONTH(‘2015-10-27’,1.5)
– returns ‘2015-11-30’
select EOMONTH(‘2015-10-27’,10)
– returns ‘2016-08-31’
select EOMONTH(‘2015-10-27’,-10)
– returns ‘2014-12-31’