Script to create SQL Server logins

-- ***********************************************************************************/
-- Description  :    Script to create SQL Server logins 
--                        This Script will create SQL syntax for re-creating existing SQL Server Logins. The OUTPUT of this script will be a TSQL statement, which can used to recreate the logins.
--                            We can also use use this script to transfer logins from one SQL instance to another instance
-- Compatibility  :     2005+
-- ***********************************************************************************/

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
 
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  sysname
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (256)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR
            SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l
            ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR
            SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l
            ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group
      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
                        SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
 
                        SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string
 
        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login has exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin
  END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
exec sp_help_revlogin

Find Last Index Rebuild

SELECT object_name(si.[object_id]) AS [TableName],
CASE
WHEN si.[index_id] = 0 then 'Heap'
WHEN si.[index_id] = 1 then 'CL'
WHEN si.[index_id] BETWEEN 2 AND 250 THEN 'NC ' + RIGHT('00' + convert(varchar, si.[index_id]), 3)
ELSE '' END AS [IndexType],
si.[name] AS [IndexName],
si.[index_id] AS [IndexID],
CASE
WHEN si.[index_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[index_id]) < DATEADD(m, -1, getdate())
THEN '!! More than a month OLD !!'
WHEN si.[index_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[index_id]) < DATEADD(wk, -1, getdate())
THEN '! Within the past month !'
WHEN si.[index_id] BETWEEN 1 AND 250 THEN 'Stats recent'
ELSE ''
END AS [Warning],
STATS_DATE (si.[object_id], si.[index_id]) AS [Last Stats Update]
FROM sys.indexes AS si
WHERE OBJECTPROPERTY(si.[object_id], 'IsUserTable') = 1
ORDER BY [TableName], si.[index_id]

Get Missing Identity Value Information

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @Store_TABLE_Info TABLE  (    Ident int IDENTITY (1,1)
                                    , Table_Name varchar(256)
                                    , Column_Name varchar(130)
                                    , Seed_Value sql_variant
                                    , increment_value sql_variant
                                    , Last_value sql_variant
                                 )

DECLARE @Table_Name varchar(261)
        ,@Column_Name varchar(130)
        ,@Seed_value sql_variant
        ,@Increment_Value sql_variant
        ,@last_Value sql_variant
        ,@count int
        ,@Sqlcmd1 nvarchar(4000)
        ,@Sqlcmd2 nvarchar(4000)
        ,@Sqlcmd3 nvarchar(4000)
        ,@count_Records  varchar(50)

/*
 List out all table names thats has identity column.
    * Get Complete Table Name (Including Schema Name).
    * Get Column Name on which Identity property is enabled
    * Get Seed Value (Starting Value of Identity)
    * Get Increment Value (Incremental value for Identity Property)
    * Get Current Seed value/ Last Value in column. (Current Value)
*/

INSERT INTO        @Store_TABLE_Info
                SELECT    '['+SCHEMA_NAME([Schema_id])+'].'+ '['+OBJECT_NAME(ST.[object_id])+']'  [Table_Name]
                        ,'['+SIC.Name+']' [Column_Name]
                        ,Seed_Value
                        ,Increment_Value
                        ,ISNULL(last_Value,0)last_Value
                FROM    Sys.Tables ST
                        JOIN sys.Identity_columns SIC ON ST.[object_id] = SIC.[object_id]

CREATE TABLE    #Temp_Final_Mismatch    (    TABLE_Name varchar(261)
                                            , Column_Name varchar(130)
                                            , Missing_Identity_Values varchar (MAX)
                                        )

SET @count = 1
WHILE @count < =  ( SELECT COUNT(* ) FROM @Store_TABLE_Info)
    BEGIN
        SELECT    @TABLE_Name = TABLE_Name
                ,@Column_Name = Column_Name
                ,@Seed_value = Seed_value
                ,@Increment_Value = Increment_Value
                ,@last_Value = last_Value
        FROM    @Store_TABLE_Info
        WHERE    Ident = @Count

        /*
        Considering One table at a time (Making Use of While Loop):

        Using Above information:
        1. we First create a Temporary table with same column name, with the same Seed Value and Same Increment value.
        2. We have Seed Value, Increment Value and Current Value, This information is enough to have complete set of Identity values in our Temporary table.
            * This is done through another while loop.
        3. Now we will compare Temporary Table created in Step 1 with User Defined table
            * Trying to find all missing Identity values that are present in Temporary table but not present in user defined table. (One Table at a time)
        4. Result from above step, we store into another temporary table.
        */   

        --        Based on Original Seed Value, Increment Value and Current Value, We can calculate how many records suppose to be present in User Defined Table.       

        SET @count_Records = CONVERT(varchar ,((CONVERT(int,@last_Value) - (1* CONVERT(int,@Seed_value)) )/CONVERT(int,@Increment_Value)  )+    1)

        SET @Sqlcmd1 = '
            CREATE TABLE #Temp_'+CONVERT(varchar , @Count) +' ( '+@Column_Name+' int IDENTITY ('+CONVERT(varchar ,@Seed_value) +','+CONVERT( varchar,@Increment_Value)+'), Bit_Value bit default (1))
           
            '+/*           
                Inserting Estimated No of Records into Temporary table.
            */+'
            DECLARE @Count1 int
            SET @Count1 = 1
            While @Count1 < = '+@count_Records+' -- To Get Actual Number of records that should be present in a TABLE.
                BEGIN
                    INSERT INTO #Temp_'+CONVERT(varchar , @Count) +' VALUES (default)       
                    SET @Count1 = @Count1 + 1
                END

            '+/*           
                Comparing Temporary Table with User Defined Table, To get Missing Identity Values information.
            */+'
            DECLARE @temp_store_id TABLE ( Ident int IDENTITY (1,1), Missing_Ident varchar (50))

            INSERT INTO @temp_store_id (Missing_Ident)
                                        SELECT '+@Column_Name+'
                                        FROM
                                            (
                                                SELECT    '+@Column_Name+' 
                                                FROM    #Temp_'+CONVERT(varchar , @Count) +'
                                                    EXCEPT
                                                SELECT    '+@Column_Name +'
                                                FROM    '+@TABLE_Name+'
                                            ) X

            '+/*           
                Now that we have all Missing value into @temp_store_id Table. Lets make it a string, So that we can easily read it.
            */+'
            DECLARE @String varchar (max)
            SET @String = '' ''
            SELECT @String = @String + CASE WHEN @string = '' '' THEN '''' ELSE '','' END + CONVERT( varchar(50),Missing_Ident)
            FROM @temp_store_id

            '+/*           
                Store String Values in another temporary table, Which will be displayed at the end
            */+'
            If @String != '' ''
                INSERT INTO #Temp_Final_Mismatch VALUES ('''+@TABLE_Name+''', '''+@Column_Name +''' , @String )

            DROP TABLE #Temp_'+CONVERT(varchar , @Count)

            --print @Sqlcmd1
             EXEC Sp_EXECutesql @Sqlcmd1
        SET @count = @count + 1
END

-- Displaying Final Result.
SELECT    *
FROM    #Temp_Final_Mismatch

DROP TABLE #Temp_Final_Mismatch
SET NOCOUNT ON
SET ANSI_WARNINGS ON

Top 10 Unused Indexes

SELECT TOP 10
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID 
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius 
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.object_id = i.object_id  
INNER JOIN sys.objects o on dm_ius.object_id = o.object_id
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.object_id
                FROM sys.partitions p GROUP BY p.index_id, p.object_id) p
        ON p.index_id = dm_ius.index_id AND dm_ius.object_id = p.object_id
WHERE OBJECTPROPERTY(dm_ius.object_id,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()  
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

Top 10 Missing Indexes

SELECT TOP 10
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
object_name(dm_mid.object_id,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + object_name(dm_mid.object_id,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
    WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
    ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC

Find Duplicate Indexes

WITH MyDuplicate AS (SELECT
    Sch.[name] AS SchemaName,
    Obj.[name] AS TableName,
    Idx.[name] AS IndexName,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 1) AS Col1,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 2) AS Col2,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 3) AS Col3,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 4) AS Col4,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 5) AS Col5,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 6) AS Col6,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 7) AS Col7,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 8) AS Col8,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 9) AS Col9,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 10) AS Col10,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 11) AS Col11,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 12) AS Col12,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 13) AS Col13,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 14) AS Col14,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 15) AS Col15,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 16) AS Col16
FROM sys.indexes Idx
INNER JOIN sys.objects Obj ON Idx.[object_id] = Obj.[object_id]
INNER JOIN sys.schemas Sch ON Sch.[schema_id] = Obj.[schema_id]
WHERE index_id > 0)
SELECT    MD1.SchemaName, MD1.TableName, MD1.IndexName,
        MD2.IndexName AS OverLappingIndex,
        MD1.Col1, MD1.Col2, MD1.Col3, MD1.Col4,
        MD1.Col5, MD1.Col6, MD1.Col7, MD1.Col8,
        MD1.Col9, MD1.Col10, MD1.Col11, MD1.Col12,
        MD1.Col13, MD1.Col14, MD1.Col15, MD1.Col16
FROM MyDuplicate MD1
INNER JOIN MyDuplicate MD2 ON MD1.tablename = MD2.tablename
    AND MD1.indexname <> MD2.indexname
    AND MD1.Col1 = MD2.Col1
    AND (MD1.Col2 IS NULL OR MD2.Col2 IS NULL OR MD1.Col2 = MD2.Col2)
    AND (MD1.Col3 IS NULL OR MD2.Col3 IS NULL OR MD1.Col3 = MD2.Col3)
    AND (MD1.Col4 IS NULL OR MD2.Col4 IS NULL OR MD1.Col4 = MD2.Col4)
    AND (MD1.Col5 IS NULL OR MD2.Col5 IS NULL OR MD1.Col5 = MD2.Col5)
    AND (MD1.Col6 IS NULL OR MD2.Col6 IS NULL OR MD1.Col6 = MD2.Col6)
    AND (MD1.Col7 IS NULL OR MD2.Col7 IS NULL OR MD1.Col7 = MD2.Col7)
    AND (MD1.Col8 IS NULL OR MD2.Col8 IS NULL OR MD1.Col8 = MD2.Col8)
    AND (MD1.Col9 IS NULL OR MD2.Col9 IS NULL OR MD1.Col9 = MD2.Col9)
    AND (MD1.Col10 IS NULL OR MD2.Col10 IS NULL OR MD1.Col10 = MD2.Col10)
    AND (MD1.Col11 IS NULL OR MD2.Col11 IS NULL OR MD1.Col11 = MD2.Col11)
    AND (MD1.Col12 IS NULL OR MD2.Col12 IS NULL OR MD1.Col12 = MD2.Col12)
    AND (MD1.Col13 IS NULL OR MD2.Col13 IS NULL OR MD1.Col13 = MD2.Col13)
    AND (MD1.Col14 IS NULL OR MD2.Col14 IS NULL OR MD1.Col14 = MD2.Col14)
    AND (MD1.Col15 IS NULL OR MD2.Col15 IS NULL OR MD1.Col15 = MD2.Col15)
    AND (MD1.Col16 IS NULL OR MD2.Col16 IS NULL OR MD1.Col16 = MD2.Col16)
ORDER BY
    MD1.SchemaName,MD1.TableName,MD1.IndexName

Top 10 Expensive Queries

SELECT DISTINCT TOP 10
d.Name,t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
JOIN sys.databases d ON t.dbid = d.database_id
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC