Hold the Lock on Table and Execute Query First

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRAN T1

 DELETE FROM Temp WITH(HOLDLOCK, ROWLOCK);

COMMIT TRAN T1

Modifying SQL Server Synonyms (Can't Alter Synonyms)

DECLARE
    @DropSynonym NVARCHAR(4000),
    @CreateSynonym NVARCHAR(4000),
    @Permissions NVARCHAR(4000)

SELECT
    @DropSynonym = '',
    @CreateSynonym = '',
    @Permissions = ''

SELECT
    @DropSynonym = @DropSynonym + 'DROP SYNONYM '
    + QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) + '.' + QUOTENAME(NAME) + ';
',
    @CreateSynonym = @CreateSynonym + 'CREATE SYNONYM '
    + QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) + '.' + QUOTENAME(NAME) + ' FOR '
    + REPLACE(base_object_name, '[OldDB]', '[NewDB]') + ';
'
FROM
    sys.synonyms ;
WITH    PermQuery
          AS (SELECT
                CASE WHEN perm.state <> 'W' THEN perm.state_desc
                     ELSE 'GRANT'
                END COLLATE database_default AS PermissionState,
                perm.permission_name COLLATE database_default AS Permission,
                SCHEMA_NAME(obj.schema_id) AS SchemaName,
                obj.name AS ObjectName,
                CASE WHEN cl.column_id IS NULL THEN SPACE(0)
                     ELSE '(' + QUOTENAME(cl.name) + ')'
                END AS ColumnName,
                CASE WHEN perm.state <> 'W' THEN 'N'
                     ELSE 'Y'
                END AS WithOption,
                usr.name AS UserName
              FROM
                sys.synonyms AS s
                INNER JOIN sys.all_objects AS obj
                    ON s.object_id = obj.object_id
                INNER JOIN sys.database_permissions AS perm
                    ON perm.major_id = obj.[object_id]
                INNER JOIN sys.database_principals AS usr
                    ON perm.grantee_principal_id = usr.principal_id
                LEFT JOIN sys.columns AS cl
                    ON cl.column_id = perm.minor_id
                       AND cl.[object_id] = perm.major_id)
    SELECT
        @Permissions = @Permissions + PermissionState + ' ' + Permission
        + ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' '
        + ColumnName + ' TO ' + UserName
        + CASE WithOption
            WHEN 'Y' THEN ' WITH GRANT OPTION'
            ELSE ''
          END + ';
'
    FROM
        PermQuery

PRINT @DropSynonym
EXEC(@DropSynonym)

PRINT @CreateSynonym
EXEC(@CreateSynonym)

PRINT @Permissions
EXEC (@Permissions)  

Auto Generate SQL Server All Databases Attach and Detach Scripts

-- Detach All Databases

-- Build the sp_detach_db command (ONLINE, non-system databases only):
SELECT DISTINCT'exec sp_detach_db ''' + DB_NAME(dbid) + ''';'
FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('C','D')
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')

-----------------------------------------------------------------------------

-- Attach All Databases
-- Build the sp_attach_db:
-- (I preach everyone against using cursor... so I don't)
SET NOCOUNT ON
DECLARE     @cmd        VARCHAR(MAX),
            @dbname     VARCHAR(200),
            @prevdbname VARCHAR(200)

SELECT @cmd = '', @dbname = ';', @prevdbname = ''

CREATE TABLE #Attach
    (Seq        INT IDENTITY(1,1) PRIMARY KEY,
     dbname     SYSNAME NULL,
     fileid     INT NULL,
     filename   VARCHAR(1000) NULL,
     TxtAttach  VARCHAR(MAX) NULL
)

INSERT INTO #Attach
SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid, filename, CONVERT(VARCHAR(MAX),'') AS TxtAttach
FROM master.dbo.sysaltfiles
WHERE dbid IN (SELECT dbid FROM master.dbo.sysaltfiles
            WHERE SUBSTRING(filename,1,1) IN ('C','D'))
            AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
            AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')
ORDER BY dbname, fileid, filename

UPDATE #Attach
SET @cmd = TxtAttach = 
            CASE WHEN dbname <> @prevdbname
            THEN CONVERT(VARCHAR(200),'exec sp_attach_db @dbname = N''' + dbname + '''')
            ELSE @cmd
            END +',@filename' + CONVERT(VARCHAR(10),fileid) + '=N''' + filename +'''',
    @prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END,
    @dbname = dbname
FROM #Attach  WITH (INDEX(0),TABLOCKX)
 OPTION (MAXDOP 1)

SELECT TxtAttach
FROM
(SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach
 GROUP BY dbname) AS x

DROP TABLE #Attach

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

SQL SERVER – Find the Size of Database File – Find the Size of Log File

Here is the script, if you remove the WHERE condition you will find the result for all the databases.

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'

Scripting SQL Server Logins

You ever find yourself with the need to copy SQL Server logins from one server to another? Maybe you are setting up a failover site, building a replacement server, setting up a reporting instance, or maybe you just want to backup the logins just in case. If you are using Windows Logins, this is a simple matter of scripting the login and applying it to the other server. Copying SQL Server Logins from one box to another is a bit trickier because SQL Server stores and manages the password. So just how do you copy the login and preserve the password? I am glad you asked.

Understanding Login Components
To successfully copy a login from one server to another, you will need to ensure that the copy has the same SID and password. The link between database users and logins is done with the logins SID, if this is different on the new server than any databases you copy over will contain orphaned users. To ensure that both the SID and the password are the same, Microsoft has written a stored procedure to aid in our transfer.

SP_HELP_REVLOGIN
SP_HELP_REVLOGIN is a stored procedure that will return a complete list of the logins that exists on you SQL Server in a script that can be run to recreate them. This script does not exist on your SQL Server by default, you must create with the code provided by Microsoft in KB article 918992 here http://support.microsoft.com/kb/918992/. Once you have create the procedures you can easily generate the create statements that allow you to copy your logins.
As an example, I created a new login on my local instance of SQL Server called SQLScript with a password of scriptme. Now I can run SP_HELP_REVLOGIN as follows:
sp_help_revlogin 'SQLScript'
RESULTS:
/* sp_help_revlogin script
** Generated Jan 22 2013 9:23AM on MyLaptop*/
-- Login: SQLLoginScript
CREATE LOGIN [SQLLoginScript] WITH PASSWORD = 0x0100B642C5A8BC6778ECE4710ED3DC8D70E0EA31B6DF6B122756 HASHED, SID = 0x80525EB475F8414FB32D627BB876F213, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
As you can see, I now have the syntax I need to recreate the login on another box. The SID will be forced to the same value and the passwords will match by virtue of this statement providing the hashed version of the password. If you need to copy all the logins, SQL Server and Windows Logins, you can run SP_HELP_REVLOGIN with no parameters.

Physical Architecture of SQL Server

Physical architecture of sql server from

SQL Server - Row Count for all Tables in a Database

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]