create Drop and Recreate Script of all Indexes in a database

Select A.[object_id]
 , OBJECT_NAME(A.[object_id]) AS Table_Name
 , A.Index_ID
 , A.[Name] As Index_Name
 , CAST(
 Case When A.type = 1 AND is_unique = 1 Then 'Create Unique Clustered Index '
 When A.type = 1 AND is_unique = 0 Then 'Create Clustered Index '
 When A.type = 2 AND is_unique = 1 Then 'Create Unique NonClustered Index '
 When A.type = 2 AND is_unique = 0 Then 'Create NonClustered Index '
 End
 + quotename(A.[Name]) + ' On ' + quotename(S.name) + '.' + quotename(OBJECT_NAME(A.[object_id])) + ' ('
 + Stuff(
 (
 Select
 ',[' + COL_NAME(A.[object_id],C.column_id)
 + Case When C.is_descending_key = 1 Then '] Desc' Else '] Asc' End
 From sys.index_columns C WITH (NOLOCK)
 Where A.[Object_ID] = C.object_id
 And A.Index_ID = C.Index_ID
 And C.is_included_column = 0
 Order by C.key_Ordinal Asc
 For XML Path('')
 )
 ,1,1,'') + ') '

 + CASE WHEN A.type = 1 THEN ''
 ELSE Coalesce('Include ('
 + Stuff(
 (
 Select
 ',' + QuoteName(COL_NAME(A.[object_id],C.column_id))
 From sys.index_columns C WITH (NOLOCK)
 Where A.[Object_ID] = C.object_id
 And A.Index_ID = C.Index_ID
 And C.is_included_column = 1
 Order by C.index_column_id Asc
 For XML Path('')
 )
 ,1,1,'') + ') '
 ,'') End
 + Case When A.has_filter = 1 Then 'Where ' + A.filter_definition Else '' End
 + ' With (Drop_Existing = ON, SORT_IN_TEMPDB = ON'
 --when the same index exists you'd better to set the Drop_Existing = ON
 --SORT_IN_TEMPDB = ON is recommended but based on your own environment.
 + ', Fillfactor = ' + Cast(Case When fill_factor = 0 Then 100 Else fill_factor End As varchar(3))
 + Case When A.[is_padded] = 1 Then ', PAD_INDEX = ON' Else ', PAD_INDEX = OFF' END
 + Case When D.[no_recompute] = 1 Then ', STATISTICS_NORECOMPUTE = ON' Else ', STATISTICS_NORECOMPUTE = OFF' End
 + Case When A.[ignore_dup_key] = 1 Then ', IGNORE_DUP_KEY = ON' Else ', IGNORE_DUP_KEY = OFF' End
 + Case When A.[ALLOW_ROW_LOCKS] = 1 Then ', ALLOW_ROW_LOCKS = ON' Else ', ALLOW_ROW_LOCKS = OFF' END
 + Case When A.[ALLOW_PAGE_LOCKS] = 1 Then ', ALLOW_PAGE_LOCKS = ON' Else ', ALLOW_PAGE_LOCKS = OFF' End
 + Case When P.[data_compression] = 0 Then ', DATA_COMPRESSION = NONE'
 When P.[data_compression] = 1 Then ', DATA_COMPRESSION = ROW'
 Else ', DATA_COMPRESSION = PAGE' End
 + ') On '
 + Case when C.type = 'FG' THEN quotename(C.name)
 ELSE quotename(C.name) + '(' + F.Partition_Column + ')' END + ';' --if it uses partition scheme then need partition column
 As nvarchar(Max)) As Index_Create_Statement
 , C.name AS FileGroupName
 , 'DROP INDEX ' + quotename(A.[Name]) + ' On ' + quotename(S.name) + '.' + quotename(OBJECT_NAME(A.[object_id])) + ';' AS Index_Drop_Statement
From SYS.Indexes A WITH (NOLOCK)
 INNER JOIN
 sys.objects B WITH (NOLOCK)
 ON A.object_id = B.object_id
 INNER JOIN
 SYS.schemas S
 ON B.schema_id = S.schema_id
 INNER JOIN
 SYS.data_spaces C WITH (NOLOCK)
 ON A.data_space_id = C.data_space_id
 INNER JOIN
 SYS.stats D WITH (NOLOCK)
 ON A.object_id = D.object_id
 AND A.index_id = D.stats_id
 Inner Join
 --The below code is to find out what data compression type was used by the index. If an index is not partitioned, it is easy as only one data compression
 --type can be used. If the index is partitioned, then each partition can be configued to use the different data compression. This is hard to generalize,
 --for simplicity, I just use the data compression type used most for the index partitions for all partitions. You can later rebuild the index partition to
 --the appropriate data compression type you want to use
 (
 select object_id, index_id, Data_Compression, ROW_NUMBER() Over(Partition By object_id, index_id Order by COUNT(*) Desc) As Main_Compression
 From sys.partitions WITH (NOLOCK)
 Group BY object_id, index_id, Data_Compression
 ) P
 ON A.object_id = P.object_id
 AND A.index_id = P.index_id
 AND P.Main_Compression = 1
 Outer APPLY
 (
 SELECT COL_NAME(A.object_id, E.column_id) AS Partition_Column
 From sys.index_columns E WITH (NOLOCK)
 WHERE E.object_id = A.object_id
 AND E.index_id = A.index_id
 AND E.partition_ordinal = 1
 ) F
Where A.type IN (1,2) --clustered and nonclustered
 AND B.Type != 'S'
 AND OBJECT_NAME(A.[object_id]) not like 'queue_messages_%'
 AND OBJECT_NAME(A.[object_id]) not like 'filestream_tombstone_%'
 AND OBJECT_NAME(A.[object_id]) not like 'sys%' --if you have index start with sys then remove it

SQL Server Dynamic data masking (2016 feature)

SQL Server Dynamic data masking is available in four different formats based on the data types.

Default- Fully masked data as per the data types of the selected fields.

ALTER COLUMN City ADD MASKED WITH (FUNCTION = 'default()')

Email – Using this method we can apply masking on the email data of the users.

ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')

Custom String – As per the name, this method can be used to mask the starting characters and last characters as per the custom requirement.

ALTER COLUMN [Mobile Number] ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')

Random – This function is used to replace number columns with random values.

ALTER COLUMN [OrdrerNO] ADD MASKED WITH (FUNCTION = 'random(1, 5)')