/* Find all FKs that are defined with some action for the DML event as opposed to NO_ACTION */

SELECT 

   o.name ,

   --o.object_id ,

   SCHEMA_NAME(o.schema_id) schemaname,

   OBJECT_NAME(o.parent_object_id) parent_object,

   OBJECT_NAME(referenced_object_id ) referenced_object,

   o.type_desc ,

   f.name ,

   --f.object_id ,

   f.principal_id ,

   f.schema_id ,

   --f.parent_object_id ,

   f.type_desc ,

   key_index_id ,

   is_disabled ,

   delete_referential_action_desc ,

   update_referential_action_desc 

FROM sys.objects o

--INNER JOIN sys.syscomments c ON o.object_id = c.id

INNER JOIN sys.foreign_keys f ON f.parent_object_id = o.object_id

WHERE 1=1

AND (delete_referential_action_desc NOT LIKE 'NO_ACTION' OR update_referential_action_desc NOT LIKE 'NO_ACTION')

--AND o.name in ('')

AND o.is_ms_shipped = 0

ORDER BY o.name;

USE --<---------- edit db name here

SET NOCOUNT ON;

IF OBJECT_id('tempdb.dbo.#t1') IS NOT NULL DROP TABLE #t1;

    CREATE TABLE #t1 (ReportServerTempDB sysname, opentran sysname );

DECLARE @spid sysname, @command varchar(1000), @dt datetime;

WHILE 1=1

BEGIN;

    INSERT INTO #t1 EXEC ('DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS');

        IF @@ROWCOUNT = 0 BEGIN; PRINT 'BREAK' BREAK; END;

    SELECT @spid = (SELECT TOP 1 opentran FROM #t1 WHERE ReportServerTempDB = 'OLDACT_SPID');

SELECT    @command = 'kill ' + @spid;

PRINT        @command;

--EXEC     (@command);

--SELECT * FROM #t1

/*

SELECT @dt = (SELECT TOP 1 opentran FROM #t1 WHERE ReportServerTempDB = 'OLDACT_STARTTIME');

SELECT 

     DATEDIFF(MINUTE, @dt, GETDATE()) AS diff_mm

    ,(SELECT TOP 1 opentran FROM #t1 WHERE ReportServerTempDB = 'OLDACT_STARTTIME') AS tran_time

    ,GETDATE() AS now

    ,@spid AS spid ;

*/

TRUNCATE TABLE #t1;

END;

/*

-- return file information at the database level

SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT   

     data_space_id AS [fg_id]

    ,FILEGROUP_NAME(data_space_id) [fg_name]

    ,[file_id]

    ,type_desc

    ,f.state_desc

    ,f.name AS logical_name

    ,physical_name

    ,size / 128 SizeMB

    ,FILEPROPERTY(f.name, 'SpaceUsed') / 128 SpaceUsedMB

    ,ROUND((CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS FLOAT) / size) * 100, 2) UsedPct

    ,growth / 128 [GrowthMB]

    ,max_size / 128 [MaxSizeMB]

    ,d.log_reuse_wait_desc

    ,d.recovery_model_desc

FROM     sys.database_files f

LEFT JOIN  sys.databases d on d.database_id = DB_ID() and f.type_desc = 'LOG'  

WHERE 1=1

--AND state_desc NOT LIKE 'ONLINE'

ORDER BY data_space_id;

*/

SELECT

     DATEADD(s,((-1)*([ms_ticks]/1000)), CURRENT_TIMESTAMP)    AS machine_start_time

    ,CAST([ms_ticks]/1000/60/60.0 AS DECIMAL(15,2))                    AS diff_hh

    ,CAST([ms_ticks]/1000/60/60/24.0 AS DECIMAL(15,2))            AS diff_dd

FROM sys.[dm_os_sys_info];

SELECT 

     sqlserver_start_time 

    ,DATEDIFF(hour, sqlserver_start_time , CURRENT_TIMESTAMP) AS diff_hh

    ,DATEDIFF(day, sqlserver_start_time , CURRENT_TIMESTAMP)  AS diff_dd

FROM sys.dm_os_sys_info;

SELECT 

     create_date AS ServiceStartTime

    ,DATEDIFF(hour, create_date, CURRENT_TIMESTAMP) AS diff_hh

    ,DATEDIFF(day, create_date, CURRENT_TIMESTAMP) AS diff_dd

FROM SYS.databases WHERE database_id = 2;

SELECT 

     login_time AS ServiceStartTime

    ,DATEDIFF(hour, login_time, CURRENT_TIMESTAMP) AS diff_hh

    ,DATEDIFF(day, login_time, CURRENT_TIMESTAMP) AS diff_dd

FROM sys.sysprocesses WHERE spid = 1;

SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @online BIT = 0;

USE --<-------------- edit database name here

SELECT TOP 50

   db_name(d.database_id)        AS [database]

    ,object_name(d.object_id) AS [object]

    ,d.equality_columns                     

    ,d.inequality_columns

    ,d.included_columns

    ,s.unique_compiles      

    ,s.user_seeks           

    ,s.user_scans           

    ,s.last_user_seek          

    ,s.last_user_scan          

    ,s.avg_total_user_cost    

    ,s.avg_user_impact    

    ,command = 'CREATE NONCLUSTERED INDEX IX_' + object_name(d.object_id) + '__' + REPLACE(REPLACE(REPLACE(ISNULL(d.equality_columns, ''), '[','') , ']',''), ', ','_') 

        + CASE WHEN d.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(d.inequality_columns,''), '[','') ,']','') ,', ','_')

        + ' ON [' + object_name(d.object_id) + '] ('

        + CASE WHEN d.equality_columns IS NOT NULL THEN d.equality_columns ELSE '' END 

        + CASE WHEN d.inequality_columns IS NOT NULL AND d.equality_columns IS NOT NULL THEN ', ' + d.inequality_columns ELSE ISNULL(d.inequality_columns, '') END + ')'

        + CASE WHEN d.included_columns IS NOT NULL THEN ' INCLUDE (' + d.included_columns + ')' ELSE '' END

        + ' WITH (ONLINE = ' + CASE WHEN @online = 1 THEN 'ON);' ELSE 'OFF);' END

FROM sys.dm_db_missing_index_details d

INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle

INNER JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle

WHERE 1=1

AND d.database_id = db_id() 

AND avg_user_impact > 50

--AND object_name(d.object_id) LIKE ''

ORDER BY s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans)DESC;

/*

    Run this script on a subscriber of transactional or p2p replication

    The script accesses the publisher via a linked server to pull metadata of row counts for tables participating in the replication 

    (belonging to all publications for the database being queried) and performs a comparison against the tables at the subscription database. 

    The rows with a diff are returned and in addition the script prints the INSERT commands required to close the diff/gap of those rows that 

    exist at the publisher and do not exist at the subscriber

*/

USE ; --<-------- Edit database name here

/*

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitMeToString]') AND type in (N'TF'))

    DROP FUNCTION [dbo].[SplitMeToString];

GO

CREATE FUNCTION [dbo].[SplitMeToString]

(

     @sep VARCHAR(32)

    ,@s VARCHAR(MAX)

)

RETURNS @t TABLE (val VARCHAR(MAX))

AS 

BEGIN;

   DECLARE @xml XML

   SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'

   INSERT   INTO @t(val)

            SELECT   RTRIM(LTRIM(r.value('.', 'VARCHAR(max)'))) AS Item

            FROM     @xml.nodes('//root/r') AS RECORDS (r);

   RETURN;

END;

*/

SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @LinkedServer sysname, @database sysname, @command varchar(max), @dubug BIT, @diff int; /* Eliminate a diff smaller than this value */

SELECT @dubug = 0, @diff = 0

, @LinkedServer = N''    --<-------- Edit linked server name here

, @database = N''    --<-------- Edit the published database name here

IF OBJECT_id('tempdb..#sysarticles ') IS NOT NULL DROP TABLE #sysarticles;

IF OBJECT_id('tempdb..#subscriber ')    IS NOT NULL DROP TABLE #subscriber;

IF OBJECT_id('tempdb..#publisher ')        IS NOT NULL DROP TABLE #publisher;

IF OBJECT_id('tempdb..#indexes')            IS NOT NULL DROP TABLE #indexes;

IF OBJECT_id('tempdb..#data')                    IS NOT NULL DROP TABLE #data;

/* Get the replication articles from the publisher */

CREATE TABLE dbo.#sysarticles (article sysname, publication sysname, ident bit);

SELECT @command = '

INSERT dbo.#sysarticles (article, publication, ident)

SELECT 

     a.name    AS article

    ,p.name    AS publication

    ,OBJECTPROPERTY(objid, ''TableHasIdentity'') AS ident 

FROM [' + @LinkedServer + '].[' + @database + '].dbo.sysarticles a

INNER JOIN [' + @LinkedServer + '].[' + @database + '].dbo.syspublications p ON p.pubid = a.pubid;';

IF @dubug = 1 PRINT @command;

EXEC (@command);

-- Index information

SELECT 

     SCHEMA_NAME(o.SCHEMA_ID) AS [Schema]

    ,o.name AS [Table]

    ,i.name AS [Index]

    ,LEFT(list, ISNULL(splitter-1,LEN(list))) AS [Columns]

    ,a.ident

INTO #indexes

FROM sys.indexes i

INNER JOIN sys.objects o ON i.[object_id] = o.[object_id] 

INNER JOIN dbo.#sysarticles a ON a.article = o.name /* replication tables only */

INNER JOIN  sys.stats s ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_id

CROSS APPLY (SELECT NULLIF(CHARINDEX('|',indexCols.list),0) splitter , list

                            FROM (SELECT CAST((SELECT CASE WHEN sc.is_included_column = 1 AND sc.ColPos = 1 THEN '|' ELSE '' END + CASE WHEN sc.ColPos  > 1 THEN ', ' ELSE '' END + name

                                                         FROM (SELECT 

                                                                             sc.is_included_column

                                                                            ,index_column_id

                                                                            ,name

                                                                            ,ROW_NUMBER() OVER (PARTITION BY sc.is_included_column ORDER BY sc.key_ordinal) ColPos

                                                                        FROM sys.index_columns  sc

                                                                        INNER JOIN sys.columns c ON sc.[object_id] = c.[object_id] AND sc.column_id = c.column_id

                                                                        WHERE sc.index_id = i.index_id AND sc.[object_id] = i.[object_id] ) sc

                                                    ORDER BY sc.is_included_column, ColPos

                                            FOR XML PATH (''), TYPE) AS VARCHAR(MAX)) list)indexCols ) indCol

WHERE i.is_primary_key = 1; /* PK only */

--AND o.name = '' 

--AND a.ident = 0

/* subscriber */

SELECT

    (row_number() OVER(ORDER BY t3.name, t2.name))%2 AS l1

    ,DB_NAME() AS [database]

    ,t3.name AS [schema]

    ,t2.name AS [table]

    ,t1.rows AS row_count

    ,((t1.reserved + ISNULL(a4.reserved,0))* 8) / 1024 AS reserved_MB 

    ,(t1.data * 8) / 1024 AS data_MB

    ,((CASE WHEN (t1.used + ISNULL(a4.used,0)) > t1.data THEN (t1.used + ISNULL(a4.used,0)) - t1.data ELSE 0 END) * 8) /1024 AS index_size_MB

    ,((CASE WHEN (t1.reserved + ISNULL(a4.reserved,0)) > t1.used THEN (t1.reserved + ISNULL(a4.reserved,0)) - t1.used ELSE 0 END) * 8)/1024 AS unused_MB

    ,i.[index]

    ,i.columns

    ,i.ident

INTO dbo.#subscriber

FROM

 (SELECT 

     ps.object_id

    ,SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows]

    ,SUM (ps.reserved_page_count) AS reserved

    ,SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data

    ,SUM (ps.used_page_count) AS used

  FROM sys.dm_db_partition_stats ps

  GROUP BY ps.object_id) AS t1

LEFT OUTER JOIN 

     (SELECT 

             it.parent_id

            ,SUM(ps.reserved_page_count) AS reserved

            ,SUM(ps.used_page_count) AS used

        FROM sys.dm_db_partition_stats ps

        INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204)

        GROUP BY it.parent_id) AS a4 ON (a4.parent_id = t1.object_id

        )

INNER JOIN sys.all_objects t2  ON ( t1.object_id = t2.object_id) 

INNER JOIN sys.schemas t3 ON (t2.schema_id = t3.schema_id)

INNER JOIN dbo.#sysarticles a ON a.article = t2.name

LEFT JOIN #indexes i ON i.[Schema] = SCHEMA_NAME(t2.schema_id) AND i.[Table] = t2.name

WHERE t2.type <> 'S' and t2.type <> 'IT';

/* publisher */

CREATE TABLE dbo.#publisher (l1 int, [database] sysname, [schema] sysname, [table] sysname, row_count int, reserved_MB float, data_MB float, index_size_MB float, unused_MB float);

SELECT @command = '

INSERT dbo.#publisher (l1, [database], [schema], [table], row_count, reserved_MB, data_MB, index_size_MB, unused_MB)

SELECT

    (row_number() over(order by t3.name, t2.name))%2 as l1

    ,DB_NAME() AS [database]

    ,t3.name AS [schema]

    ,t2.name AS [table]

    ,t1.rows AS row_count

    ,((t1.reserved + ISNULL(a4.reserved,0))* 8) / 1024 AS reserved_MB 

    ,(t1.data * 8) / 1024 AS data_MB

    ,((CASE WHEN (t1.used + ISNULL(a4.used,0)) > t1.data THEN (t1.used + ISNULL(a4.used,0)) - t1.data ELSE 0 END) * 8) /1024 AS index_size_MB

    ,((CASE WHEN (t1.reserved + ISNULL(a4.reserved,0)) > t1.used THEN (t1.reserved + ISNULL(a4.reserved,0)) - t1.used ELSE 0 END) * 8)/1024 AS unused_MB

FROM

 (SELECT 

     ps.object_id

    ,SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows]

    ,SUM (ps.reserved_page_count) AS reserved

    ,SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data

    ,SUM (ps.used_page_count) AS used

  FROM [' + @LinkedServer + '].[' + @database + '].sys.dm_db_partition_stats ps

  GROUP BY ps.object_id) AS t1

LEFT OUTER JOIN 

 (SELECT 

       it.parent_id

      ,SUM(ps.reserved_page_count) AS reserved

      ,SUM(ps.used_page_count) AS used

  FROM [' + @LinkedServer + '].[' + @database + '].sys.dm_db_partition_stats ps

  INNER JOIN [' + @LinkedServer + '].[' + @database + '].sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204)

  GROUP BY it.parent_id) AS a4 ON (a4.parent_id = t1.object_id)

INNER JOIN [' + @LinkedServer + '].[' + @database + '].sys.all_objects t2  ON ( t1.object_id = t2.object_id) 

INNER JOIN [' + @LinkedServer + '].[' + @database + '].sys.schemas t3 ON (t2.schema_id = t3.schema_id)

INNER JOIN [' + @LinkedServer + '].[' + @database + '].dbo.sysarticles a ON a.name = t2.name

WHERE t2.type <> ''S'' and t2.type <> ''IT'';';

IF @dubug = 1 PRINT @command;

EXEC (@command);

/* Prepare the final output */

SELECT 

     @@servername AS [server]

    ,p.[schema]

    ,p.[table]

    ,a.publication

    ,p.reserved_MB    

    ,p.row_count                                 AS publisher_row_count

    ,s.row_count                                 AS subscriber_row_count

    ,p.row_count - s.row_count    AS diff

    ,(SELECT SUM(p.row_count - s.row_count)    AS diff_total FROM #publisher p

                INNER JOIN #subscriber s ON s.[table] = p.[table] 

                INNER JOIN #sysarticles a ON a.article = s.[table]

                WHERE s.row_count <> p.row_count AND (p.row_count - s.row_count > @diff OR @diff IS NULL) ) AS diff_total

    ,s.[columns]

    ,s.[index]

    ,s.ident

INTO #data

FROM #publisher p

INNER JOIN #subscriber s ON s.[table] = p.[table] AND s.[schema] = p.[schema]

INNER JOIN #sysarticles a ON a.article = s.[table] 

WHERE s.row_count <> p.row_count

AND (p.row_count - s.row_count > @diff OR @diff IS NULL);

/* Return tables with diff */

SELECT  

         [server]

        ,[schema]

        ,[table]

        ,reserved_MB

        ,publisher_row_count

        ,subscriber_row_count

        ,diff

        ,diff_total 

FROM #data 

ORDER BY diff DESC;

/* Use a cursor over the table to construct the INSERT commands */

DECLARE cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR 

    SELECT [schema], [table], [index], [columns], [Ident] FROM #data ORDER BY [table];

OPEN cur;

DECLARE @schema sysname, @table sysname, @index sysname, @columns sysname, @cnt int, @where nvarchar(4000), @col_cnt int, @ident bit ;

DECLARE @colname nvarchar(4000);

DECLARE @cols table (name nvarchar(4000), col_order int);

DECLARE @min int, @max int, @columns_list varchar(max), @column sysname, @debug bit;

--SELECT @columns_list = '';

    FETCH NEXT FROM cur INTO @schema, @table, @index, @columns, @ident;

    WHILE (@@FETCH_STATUS <> -1)

    BEGIN;

    /* Handle cases where the primary keys is composit consisting of more than a single column */

    INSERT INTO @cols SELECT val, ROW_NUMBER() OVER(ORDER BY val) FROM [dbo].[SplitMeToString](',', @columns);

    SELECT @where = 'WHERE';

    SELECT @col_cnt = 0;

    WHILE (SELECT MAX(col_order) FROM @cols) > @col_cnt

    BEGIN;

        SELECT @col_cnt = @col_cnt + 1;

        SELECT @colname = name FROM @cols WHERE col_order = @col_cnt;

        SELECT @where = @where + ' s.' + QUOTENAME(@colname) + ' = p.' + QUOTENAME(@colname) + ' AND';

    END;

    /* remove the last AND of the WHERE string */

    SELECT @where = SUBSTRING(@where,0, LEN(@Where) - 3);

    /*

     Get the PK diff

    SELECT @command =     

    'SELECT @cnt = (SELECT COUNT(*)

     FROM [' + @LinkedServer + '].[' + @database + '].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' p

      WHERE NOT EXISTS (SELECT * FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) 

            + ' s ' + @where + '));'

    IF @debug = 1 PRINT @command;

    --Execute

  EXEC sp_executesql 

      @statement = @command, 

      @params         = N'@cnt int OUTPUT', 

      @cnt             = @cnt OUTPUT;

  */

    /* Tables with no identity column use a simple INSERT...SELECT command */

        IF (@ident = 0)

        BEGIN;

                SELECT @command =     

                'INSERT ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + CHAR(10) +

                'SELECT * FROM [' + @LinkedServer + '].[' + @database + '].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' p

                    WHERE NOT EXISTS (SELECT * FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) 

                        + ' s ' + @where + ');' + CHAR(10);

                PRINT @command;

        END

        /* Tables with identity column construct an explicit column list */

        ELSE IF (@ident = 1)

        BEGIN;

                SELECT @max = MAX(ORDINAL_POSITION), @min = MIN(ORDINAL_POSITION)     

                FROM    INFORMATION_SCHEMA.COLUMNS 

                WHERE TABLE_NAME = @table AND TABLE_SCHEMA = @schema;

                /* Loop through the columns to build the column list */

                SELECT @column = '', @columns_list = '';

                WHILE (@min <= @max )

                    BEGIN;

                        SELECT     

                             @column = QUOTENAME(COLUMN_NAME) + ','

                        FROM     INFORMATION_SCHEMA.COLUMNS

                        WHERE ORDINAL_POSITION = @min AND TABLE_NAME = @table AND TABLE_SCHEMA = @schema; 

                        SELECT @columns_list = @columns_list + @column, @min = @min + 1 ;

                    END;

                    /* Remove the last comma */

                    SELECT @columns_list = LEFT(@columns_list, LEN(@columns_list) - 1);

                    SELECT @command = 

                    'SET IDENTITY_INSERT ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' ON;

                    INSERT ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table)+ ' (' + @columns_list + ')'  + CHAR(10) +

                    'SELECT ' + @columns_list + CHAR(10) +

                    ' FROM [' + @LinkedServer + '].[' + @database + '].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' p

                        WHERE NOT EXISTS (SELECT * FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) 

                            + ' s ' + @where + ');

                    SET IDENTITY_INSERT ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' OFF;' + CHAR(10);

                    PRINT @command;

            END;

/*

    --Save the results to a table

    SELECT @command = 

    'INSERT dbo.#Data ([database], [schema], [table], [index], [columns], [cnt])

     SELECT '''

        + DB_NAME() + ''''

        + ',''' + @schema + ''''

        + ',''' + @table + ''''

        + ',''' + @index    + ''''

        + ',''' + @columns + ''''

        + ','        + CAST(@cnt AS sysname) + '';

    --PRINT @command;

    --EXEC (@command);

*/

    SELECT @command = '', @where = '';

    DELETE @cols;

    FETCH NEXT FROM cur INTO @schema, @table, @index, @columns, @ident;

    END;

CLOSE cur; DEALLOCATE cur;

-- Yaniv Etrogi 20100628

-- Get all the server configuration options that were modified from the default settings

USE master;

SET NOCOUNT ON;

IF OBJECT_ID('TEMPDB.dbo.#DefaultConfig') IS NOT NULL DROP TABLE #DefaultConfig;

CREATE TABLE #DefaultConfig ([name] sysname, minimum int, maximum int, config_value int, run_value int );

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('access check cache bucket count',0,16384,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('access check cache quota',0,2147483647,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Ad Hoc Distributed Queries',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('affinity I/O mask',-2147483648,2147483647,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('affinity mask',-2147483648,2147483647,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Agent XPs',0,1,1,1)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('allow updates',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('awe enabled',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('backup compression default',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('blocked process threshold (s)',0,86400,10,10)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('c2 audit mode',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('clr enabled',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('common criteria compliance enabled',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('cost threshold for parallelism',0,32767,5,5)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('cross db ownership chaining',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('cursor threshold',-1,2147483647,-1,-1)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Database Mail XPs',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('default full-text language',0,2147483647,1033,1033)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('default language',0,9999,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('default trace enabled',0,1,1,1)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('disallow results from triggers',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('EKM provider enabled',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('filestream access level',0,2,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('fill factor (%)',0,100,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('ft crawl bandwidth (max)',0,32767,100,100)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('ft crawl bandwidth (min)',0,32767,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('ft notify bandwidth (max)',0,32767,100,100)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('ft notify bandwidth (min)',0,32767,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('index create memory (KB)',704,2147483647,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('in-doubt xact resolution',0,2,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('lightweight pooling',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('locks',5000,2147483647,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max degree of parallelism',0,64,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max full-text crawl range',0,256,4,4)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max server memory (MB)',16,2147483647,2147483647,2147483647)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max text repl size (B)',-1,2147483647,65536,65536)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max worker threads',128,32767,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('media retention',0,365,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('min memory per query (KB)',512,2147483647,1024,1024)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('min server memory (MB)',0,2147483647,0,8)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('nested triggers',0,1,1,1)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('network packet size (B)',512,32767,4096,4096)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Ole Automation Procedures',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('open objects',0,2147483647,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('optimize for ad hoc workloads',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('PH timeout (s)',1,3600,60,60)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('precompute rank',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('priority boost',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('query governor cost limit',0,2147483647,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('query wait (s)',-1,2147483647,-1,-1)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('recovery interval (min)',0,32767,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote access',0,1,1,1)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote admin connections',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote login timeout (s)',0,2147483647,20,20)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote proc trans',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote query timeout (s)',0,2147483647,600,600)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Replication XPs',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('scan for startup procs',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('server trigger recursion',0,1,1,1)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('set working set size',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('show advanced options',0,1,1,1)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('SMO and DMO XPs',0,1,1,1)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('SQL Mail XPs',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('transform noise words',0,1,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('two digit year cutoff',1753,9999,2049,2049)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('user connections',0,32767,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('user options',0,32767,0,0)

INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('xp_cmdshell',0,1,0,0)

IF OBJECT_ID('TEMPDB.dbo.#CurrentConfig') IS NOT NULL DROP TABLE #CurrentConfig;

CREATE TABLE #CurrentConfig ([name] sysname, minimum int, maximum int, config_value int, run_value int );

INSERT #CurrentConfig ([name], minimum, maximum, config_value, run_value) EXEC sp_configure;

SELECT 

    c.name, c.minimum, c.maximum, d.config_value AS Default_config_value, d.run_value AS Default_run_value, c.config_value, c.run_value

FROM #CurrentConfig c

INNER JOIN #DefaultConfig d ON d.name = c.name

WHERE c.config_value <> d.config_value OR c.run_value <> d.run_value

ORDER BY c.name;

SELECT 'USE master; EXEC sp_configure ' + '''' + c.name + '''' + ', ' + CAST(c.run_value as sysname )

                + '; RECONFIGURE WITH OVERRIDE;'

    --, c.minimum, c.maximum, d.config_value AS Default_config_value, d.run_value AS Default_run_value

    --, c.config_value, c.run_value

FROM #CurrentConfig c

INNER JOIN #DefaultConfig d ON d.name = c.name

WHERE c.config_value <> d.config_value OR c.run_value <> d.run_value

ORDER BY c.name;

/*

    Run this script on a subscriber of transactional or p2p replication

    The script accesses the publisher via a linked server to pull metadata of row counts for tables participating in the replication 

    (belonging to all publications for the database being queried) and performs a comparison against the tables at the subscription database. 

    The rows with a diff are returned and in addition the script prints the INSERT commands required to close the diff/gap of those rows that 

    exist at the publisher and do not exist at the subscriber

*/

USE ; --<-------- Edit database name here

/*

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitMeToString]') AND type in (N'TF'))

    DROP FUNCTION [dbo].[SplitMeToString];

GO

CREATE FUNCTION [dbo].[SplitMeToString]

(

     @sep VARCHAR(32)

    ,@s VARCHAR(MAX)

)

RETURNS @t TABLE (val VARCHAR(MAX))

AS 

BEGIN;

   DECLARE @xml XML

   SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'

   INSERT   INTO @t(val)

            SELECT   RTRIM(LTRIM(r.value('.', 'VARCHAR(max)'))) AS Item

            FROM     @xml.nodes('//root/r') AS RECORDS (r);

   RETURN;

END;

*/

SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @LinkedServer sysname, @database sysname, @command varchar(max), @dubug BIT, @diff int; /* Eliminate a diff smaller than this value */

SELECT @dubug = 0, @diff = 0

, @LinkedServer = N''    --<-------- Edit linked server name here

, @database = N''    --<-------- Edit the published database name here

IF OBJECT_id('tempdb..#sysarticles ') IS NOT NULL DROP TABLE #sysarticles;

IF OBJECT_id('tempdb..#subscriber ')    IS NOT NULL DROP TABLE #subscriber;

IF OBJECT_id('tempdb..#publisher ')        IS NOT NULL DROP TABLE #publisher;

IF OBJECT_id('tempdb..#indexes')            IS NOT NULL DROP TABLE #indexes;

IF OBJECT_id('tempdb..#data')                    IS NOT NULL DROP TABLE #data;

/* Get the replication articles from the publisher */

CREATE TABLE dbo.#sysarticles (article sysname, publication sysname, ident bit);

SELECT @command = '

INSERT dbo.#sysarticles (article, publication, ident)

SELECT 

     a.name    AS article

    ,p.name    AS publication

    ,OBJECTPROPERTY(objid, ''TableHasIdentity'') AS ident 

FROM [' + @LinkedServer + '].[' + @database + '].dbo.sysarticles a

INNER JOIN [' + @LinkedServer + '].[' + @database + '].dbo.syspublications p ON p.pubid = a.pubid;';

IF @dubug = 1 PRINT @command;

EXEC (@command);

-- Index information

SELECT 

     SCHEMA_NAME(o.SCHEMA_ID) AS [Schema]

    ,o.name AS [Table]

    ,i.name AS [Index]

    ,LEFT(list, ISNULL(splitter-1,LEN(list))) AS [Columns]

    ,a.ident

INTO #indexes

FROM sys.indexes i

INNER JOIN sys.objects o ON i.[object_id] = o.[object_id] 

INNER JOIN dbo.#sysarticles a ON a.article = o.name /* replication tables only */

INNER JOIN  sys.stats s ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_id

CROSS APPLY (SELECT NULLIF(CHARINDEX('|',indexCols.list),0) splitter , list

                            FROM (SELECT CAST((SELECT CASE WHEN sc.is_included_column = 1 AND sc.ColPos = 1 THEN '|' ELSE '' END + CASE WHEN sc.ColPos  > 1 THEN ', ' ELSE '' END + name

                                                         FROM (SELECT 

                                                                             sc.is_included_column

                                                                            ,index_column_id

                                                                            ,name

                                                                            ,ROW_NUMBER() OVER (PARTITION BY sc.is_included_column ORDER BY sc.key_ordinal) ColPos

                                                                        FROM sys.index_columns  sc

                                                                        INNER JOIN sys.columns c ON sc.[object_id] = c.[object_id] AND sc.column_id = c.column_id

                                                                        WHERE sc.index_id = i.index_id AND sc.[object_id] = i.[object_id] ) sc

                                                    ORDER BY sc.is_included_column, ColPos

                                            FOR XML PATH (''), TYPE) AS VARCHAR(MAX)) list)indexCols ) indCol

WHERE i.is_primary_key = 1; /* PK only */

--AND o.name = '' 

--AND a.ident = 0

/* subscriber */

SELECT

    (row_number() OVER(ORDER BY t3.name, t2.name))%2 AS l1

    ,DB_NAME() AS [database]

    ,t3.name AS [schema]

    ,t2.name AS [table]

    ,t1.rows AS row_count

    ,((t1.reserved + ISNULL(a4.reserved,0))* 8) / 1024 AS reserved_MB 

    ,(t1.data * 8) / 1024 AS data_MB

    ,((CASE WHEN (t1.used + ISNULL(a4.used,0)) > t1.data THEN (t1.used + ISNULL(a4.used,0)) - t1.data ELSE 0 END) * 8) /1024 AS index_size_MB

    ,((CASE WHEN (t1.reserved + ISNULL(a4.reserved,0)) > t1.used THEN (t1.reserved + ISNULL(a4.reserved,0)) - t1.used ELSE 0 END) * 8)/1024 AS unused_MB

    ,i.[index]

    ,i.columns

    ,i.ident

INTO dbo.#subscriber

FROM

 (SELECT 

     ps.object_id

    ,SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows]

    ,SUM (ps.reserved_page_count) AS reserved

    ,SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data

    ,SUM (ps.used_page_count) AS used

  FROM sys.dm_db_partition_stats ps

  GROUP BY ps.object_id) AS t1

LEFT OUTER JOIN 

     (SELECT 

             it.parent_id

            ,SUM(ps.reserved_page_count) AS reserved

            ,SUM(ps.used_page_count) AS used

        FROM sys.dm_db_partition_stats ps

        INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204)

        GROUP BY it.parent_id) AS a4 ON (a4.parent_id = t1.object_id

        )

INNER JOIN sys.all_objects t2  ON ( t1.object_id = t2.object_id) 

INNER JOIN sys.schemas t3 ON (t2.schema_id = t3.schema_id)

INNER JOIN dbo.#sysarticles a ON a.article = t2.name

LEFT JOIN #indexes i ON i.[Schema] = SCHEMA_NAME(t2.schema_id) AND i.[Table] = t2.name

WHERE t2.type <> 'S' and t2.type <> 'IT';

/* publisher */

CREATE TABLE dbo.#publisher (l1 int, [database] sysname, [schema] sysname, [table] sysname, row_count int, reserved_MB float, data_MB float, index_size_MB float, unused_MB float);

SELECT @command = '

INSERT dbo.#publisher (l1, [database], [schema], [table], row_count, reserved_MB, data_MB, index_size_MB, unused_MB)

SELECT

    (row_number() over(order by t3.name, t2.name))%2 as l1

    ,DB_NAME() AS [database]

    ,t3.name AS [schema]

    ,t2.name AS [table]

    ,t1.rows AS row_count

    ,((t1.reserved + ISNULL(a4.reserved,0))* 8) / 1024 AS reserved_MB 

    ,(t1.data * 8) / 1024 AS data_MB

    ,((CASE WHEN (t1.used + ISNULL(a4.used,0)) > t1.data THEN (t1.used + ISNULL(a4.used,0)) - t1.data ELSE 0 END) * 8) /1024 AS index_size_MB

    ,((CASE WHEN (t1.reserved + ISNULL(a4.reserved,0)) > t1.used THEN (t1.reserved + ISNULL(a4.reserved,0)) - t1.used ELSE 0 END) * 8)/1024 AS unused_MB

FROM

 (SELECT 

     ps.object_id

    ,SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows]

    ,SUM (ps.reserved_page_count) AS reserved

    ,SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data

    ,SUM (ps.used_page_count) AS used

  FROM [' + @LinkedServer + '].[' + @database + '].sys.dm_db_partition_stats ps

  GROUP BY ps.object_id) AS t1

LEFT OUTER JOIN 

 (SELECT 

       it.parent_id

      ,SUM(ps.reserved_page_count) AS reserved

      ,SUM(ps.used_page_count) AS used

  FROM [' + @LinkedServer + '].[' + @database + '].sys.dm_db_partition_stats ps

  INNER JOIN [' + @LinkedServer + '].[' + @database + '].sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204)

  GROUP BY it.parent_id) AS a4 ON (a4.parent_id = t1.object_id)

INNER JOIN [' + @LinkedServer + '].[' + @database + '].sys.all_objects t2  ON ( t1.object_id = t2.object_id) 

INNER JOIN [' + @LinkedServer + '].[' + @database + '].sys.schemas t3 ON (t2.schema_id = t3.schema_id)

INNER JOIN [' + @LinkedServer + '].[' + @database + '].dbo.sysarticles a ON a.name = t2.name

WHERE t2.type <> ''S'' and t2.type <> ''IT'';';

IF @dubug = 1 PRINT @command;

EXEC (@command);

/* Prepare the final output */

SELECT 

     @@servername AS [server]

    ,p.[schema]

    ,p.[table]

    ,a.publication

    ,p.reserved_MB    

    ,p.row_count                                 AS publisher_row_count

    ,s.row_count                                 AS subscriber_row_count

    ,p.row_count - s.row_count    AS diff

    ,(SELECT SUM(p.row_count - s.row_count)    AS diff_total FROM #publisher p

                INNER JOIN #subscriber s ON s.[table] = p.[table] 

                INNER JOIN #sysarticles a ON a.article = s.[table]

                WHERE s.row_count <> p.row_count AND (p.row_count - s.row_count > @diff OR @diff IS NULL) ) AS diff_total

    ,s.[columns]

    ,s.[index]

    ,s.ident

INTO #data

FROM #publisher p

INNER JOIN #subscriber s ON s.[table] = p.[table] AND s.[schema] = p.[schema]

INNER JOIN #sysarticles a ON a.article = s.[table] 

WHERE s.row_count <> p.row_count

AND (p.row_count - s.row_count > @diff OR @diff IS NULL);

/* Return tables with diff */

SELECT  

         [server]

        ,[schema]

        ,[table]

        ,reserved_MB

        ,publisher_row_count

        ,subscriber_row_count

        ,diff

        ,diff_total 

FROM #data 

ORDER BY diff DESC;

/* Use a cursor over the table to construct the INSERT commands */

DECLARE cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR 

    SELECT [schema], [table], [index], [columns], [Ident] FROM #data ORDER BY [table];

OPEN cur;

DECLARE @schema sysname, @table sysname, @index sysname, @columns sysname, @cnt int, @where nvarchar(4000), @col_cnt int, @ident bit ;

DECLARE @colname nvarchar(4000);

DECLARE @cols table (name nvarchar(4000), col_order int);

DECLARE @min int, @max int, @columns_list varchar(max), @column sysname, @debug bit;

--SELECT @columns_list = '';

    FETCH NEXT FROM cur INTO @schema, @table, @index, @columns, @ident;

    WHILE (@@FETCH_STATUS <> -1)

    BEGIN;

    /* Handle cases where the primary keys is composit consisting of more than a single column */

    INSERT INTO @cols SELECT val, ROW_NUMBER() OVER(ORDER BY val) FROM [dbo].[SplitMeToString](',', @columns);

    SELECT @where = 'WHERE';

    SELECT @col_cnt = 0;

    WHILE (SELECT MAX(col_order) FROM @cols) > @col_cnt

    BEGIN;

        SELECT @col_cnt = @col_cnt + 1;

        SELECT @colname = name FROM @cols WHERE col_order = @col_cnt;

        SELECT @where = @where + ' s.' + QUOTENAME(@colname) + ' = p.' + QUOTENAME(@colname) + ' AND';

    END;

    /* remove the last AND of the WHERE string */

    SELECT @where = SUBSTRING(@where,0, LEN(@Where) - 3);

    /*

     Get the PK diff

    SELECT @command =     

    'SELECT @cnt = (SELECT COUNT(*)

     FROM [' + @LinkedServer + '].[' + @database + '].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' p

      WHERE NOT EXISTS (SELECT * FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) 

            + ' s ' + @where + '));'

    IF @debug = 1 PRINT @command;

    --Execute

  EXEC sp_executesql 

      @statement = @command, 

      @params         = N'@cnt int OUTPUT', 

      @cnt             = @cnt OUTPUT;

  */

    /* Tables with no identity column use a simple INSERT...SELECT command */

        IF (@ident = 0)

        BEGIN;

                SELECT @command =     

                'INSERT ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + CHAR(10) +

                'SELECT * FROM [' + @LinkedServer + '].[' + @database + '].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' p

                    WHERE NOT EXISTS (SELECT * FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) 

                        + ' s ' + @where + ');' + CHAR(10);

                PRINT @command;

        END

        /* Tables with identity column construct an explicit column list */

        ELSE IF (@ident = 1)

        BEGIN;

                SELECT @max = MAX(ORDINAL_POSITION), @min = MIN(ORDINAL_POSITION)     

                FROM    INFORMATION_SCHEMA.COLUMNS 

                WHERE TABLE_NAME = @table AND TABLE_SCHEMA = @schema;

                /* Loop through the columns to build the column list */

                SELECT @column = '', @columns_list = '';

                WHILE (@min <= @max )

                    BEGIN;

                        SELECT     

                             @column = QUOTENAME(COLUMN_NAME) + ','

                        FROM     INFORMATION_SCHEMA.COLUMNS

                        WHERE ORDINAL_POSITION = @min AND TABLE_NAME = @table AND TABLE_SCHEMA = @schema; 

                        SELECT @columns_list = @columns_list + @column, @min = @min + 1 ;

                    END;

                    /* Remove the last comma */

                    SELECT @columns_list = LEFT(@columns_list, LEN(@columns_list) - 1);

                    SELECT @command = 

                    'SET IDENTITY_INSERT ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' ON;

                    INSERT ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table)+ ' (' + @columns_list + ')'  + CHAR(10) +

                    'SELECT ' + @columns_list + CHAR(10) +

                    ' FROM [' + @LinkedServer + '].[' + @database + '].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' p

                        WHERE NOT EXISTS (SELECT * FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) 

                            + ' s ' + @where + ');

                    SET IDENTITY_INSERT ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' OFF;' + CHAR(10);

                    PRINT @command;

            END;

/*

    --Save the results to a table

    SELECT @command = 

    'INSERT dbo.#Data ([database], [schema], [table], [index], [columns], [cnt])

     SELECT '''

        + DB_NAME() + ''''

        + ',''' + @schema + ''''

        + ',''' + @table + ''''

        + ',''' + @index    + ''''

        + ',''' + @columns + ''''

        + ','        + CAST(@cnt AS sysname) + '';

    --PRINT @command;

    --EXEC (@command);

*/

    SELECT @command = '', @where = '';

    DELETE @cols;

    FETCH NEXT FROM cur INTO @schema, @table, @index, @columns, @ident;

    END;

CLOSE cur; DEALLOCATE cur;

USE master;

DECLARE @t TABLE (fname varchar(max));

INSERT @t EXEC xp_cmdshell 'dir /b /S N:\Backup\MyDB\*.trn'; --<---- edit path and database name here

SELECT 'RESTORE LOG [MyDB] FROM DISK = ''' + fname + ''' WITH NORECOVERY;' FROM @t WHERE fname IS NOT NULL;

/*

1. Generate the DROP FK constraint command

2. Generate the ADD WITH NOCHECK FK constraint command 

3. Generate the CHECK FK constraint command 

http://www.sqlserverutilities.com/

*/

SET NOCOUNT ON;

-- Generate the DROP FK constraint command 

SELECT DISTINCT 

    'ALTER TABLE [' + object_name(f.parent_object_id) + '] DROP CONSTRAINT [' + f.name + '];' + CHAR(13) + CHAR(10) + 'GO'

FROM sys.foreign_keys f 

WHERE f.is_not_for_replication = 0;

-- Generate the ADD FK constraint command WITH NOCHECK

DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR

SELECT object_id , parent_object_id, referenced_object_id FROM sys.foreign_keys WHERE is_not_for_replication = 0;

SET NOCOUNT ON;

OPEN cur;

DECLARE @object_id int, @parent_object_id int, @referenced_object_id int, @i int = 1;

DECLARE @fk_cols varchar(8000) = '', @ref_cols varchar(8000) = '';

FETCH NEXT FROM cur INTO @object_id, @parent_object_id, @referenced_object_id;

WHILE (@@FETCH_STATUS <> -1 ) 

BEGIN;

    SELECT  

             @fk_cols        = @fk_cols + COALESCE([fk_col_name] + ',', '')

            ,@ref_cols    = @ref_cols + COALESCE([ref_col_name] + ',', '')

        FROM    

            ( 

                        SELECT    

                                     '[' + c.name + ']' AS [fk_col_name]

                                    ,'[' + d.name + ']' AS [ref_col_name]

                            FROM      sys.foreign_keys f

                                                INNER JOIN sys.foreign_key_columns b ON f.object_id = b.constraint_object_id

                                                INNER JOIN sys.columns c ON b.parent_column_id = c.column_id AND f.parent_object_id = c.object_id

                                                INNER JOIN sys.columns d ON b.referenced_column_id = d.column_id AND f.referenced_object_id = d.object_id

                            WHERE     f.parent_object_id = @parent_object_id

                                                AND f.object_id = @object_id

                                                AND f.is_not_for_replication = 0

            ) d;

    SELECT @fk_cols =  LEFT(@fk_cols, LEN(@fk_cols) - 1), @ref_cols =  LEFT(@ref_cols, LEN(@ref_cols) - 1);

        PRINT '-- ' + CAST(@i AS sysname)

    PRINT 'ALTER TABLE ' + QUOTENAME(OBJECT_NAME(@parent_object_id)) + ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id)) + ' FOREIGN KEY (' + @fk_cols + ')  

  REFERENCES ' + QUOTENAME(OBJECT_NAME(@referenced_object_id)) + ' (' + @ref_cols + ')NOT FOR REPLICATION;' + CHAR(10) + 'GO'

        SELECT @fk_cols = '', @ref_cols = '';

        SELECT @i = @i + 1;

FETCH NEXT FROM cur INTO @object_id, @parent_object_id, @referenced_object_id;    

END;

CLOSE cur; DEALLOCATE cur;

-- Generate the CHECK FK constraint command 

SELECT DISTINCT 

    'ALTER TABLE [' + object_name(f.parent_object_id) + '] CHECK CONSTRAINT [' + f.name + '];' + CHAR(13) + CHAR(10) + 'GO'

FROM sys.foreign_keys f 

WHERE f.is_not_for_replication = 0;

/*

1. Generate the DROP CK constraint command

2. Generate the ADD WITH NOCHECK CK constraint command 

3. Generate the CHECK CK constraint command 

*/

SET NOCOUNT ON;

-- Generate the DROP CK constraint command 

SELECT 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(o.SCHEMA_ID)) + '.' + QUOTENAME(OBJECT_NAME(o.parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(CONSTRAINT_NAME) + CHAR(10) + 'GO'

FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS c

INNER JOIN sys.objects o ON c.CONSTRAINT_NAME = o.[name]

WHERE 1=1

AND OBJECTPROPERTY(o.object_id, N'IsCheckCnst') = 1

AND OBJECTPROPERTY(o.object_id, N'CnstIsNotRepl') = 0;

-- Generate the ADD CK constraint command WITH NOCHECK

SELECT  'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(o.SCHEMA_ID)) + '.' + QUOTENAME(OBJECT_NAME(o.parent_object_id)) 

        + ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(CONSTRAINT_NAME) + ' CHECK NOT FOR REPLICATION ' + CHECK_CLAUSE + CHAR(10) + 'GO'

FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS c

INNER JOIN sys.objects o ON c.CONSTRAINT_NAME = o.[name]

WHERE 1=1

AND OBJECTPROPERTY(o.object_id, N'IsCheckCnst') = 1

AND OBJECTPROPERTY(o.object_id, N'CnstIsNotRepl') = 0;

-- Generate the CHECK CK constraint command 

SELECT  'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(o.SCHEMA_ID)) + '.' + QUOTENAME(OBJECT_NAME(o.parent_object_id)) 

        + ' CHECK CONSTRAINT ' + QUOTENAME(CONSTRAINT_NAME) + CHAR(10) + 'GO'

FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS c

INNER JOIN sys.objects o ON c.CONSTRAINT_NAME = o.[name]

WHERE 1=1

AND OBJECTPROPERTY(o.object_id, N'IsCheckCnst') = 1

AND OBJECTPROPERTY(o.object_id, N'CnstIsNotRepl') = 0;

EXEC dbo.MonitorReplicationDeliveryLatency @DistributionThreshold = 5, @SubscriberThreshold = 4, @Publication = 'TEST', @Delay = '00:00:05', @Debug = 1;

USE Published_Database;

SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MonitorReplicationDeliveryLatency]') AND type IN (N'P', N'PC'))

    DROP PROCEDURE dbo.MonitorReplicationDeliveryLatency;

GO  

CREATE PROCEDURE [dbo].MonitorReplicationDeliveryLatency    

(

     @DistributionThreshold int

    ,@SubscriberThreshold        int

    ,@Publication                        sysname

    ,@Delay                                    char(8)= '00:00:10'

    ,@Debug                                    BIT = 0

)

/*

    Yaniv Etrogi

    http://www.sqlserverutilities.com

*/

AS      

SET NOCOUNT ON;     

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  

--DECLARE @DistributionThreshold int, @SubscriberThreshold int, @Publication sysname, @Delay char(8);

--SELECT @DistributionThreshold = 3, @SubscriberThreshold = 5, @Publication = 'test', @Delay = '00:00:03';

--EXEC dbo.MonitorReplicationDeliveryLatency @DistributionThreshold = 5, @SubscriberThreshold = 4, @Publication = 'TEST', @Delay = '00:00:05', @Debug = 1;

DECLARE @tokenid int;

-- Insert a tracer token at the publication database.

EXEC sys.sp_posttracertoken 

                         @publication            = @publication

                        ,@tracer_token_id = @tokenid OUTPUT;

-- Wait for the token to make it to the Subscriber.

WAITFOR DELAY @Delay;

-- Get latency information for the last inserted token.

CREATE TABLE #tokens (tracer_id    int, publisher_commit datetime);

INSERT #tokens (tracer_id, publisher_commit) EXEC sys.sp_helptracertokens @publication = @publication;

SELECT @tokenid = (SELECT TOP 1 tracer_id FROM #tokens ORDER BY publisher_commit DESC);

-- Get history for the tracer token.

CREATE TABLE #TokenHistory(distributor_latency bigint, subscriber    sysname, subscriber_db sysname, subscriber_latency bigint, overall_latency bigint);

INSERT #TokenHistory (distributor_latency, subscriber, subscriber_db, subscriber_latency, overall_latency )

EXEC sys.sp_helptracertokenhistory 

                         @publication = @publication

                        ,@tracer_id        = @tokenid;

IF @Debug = 1 

    SELECT distributor_latency, subscriber, subscriber_db, subscriber_latency, overall_latency FROM #TokenHistory;

/* If the threshold is crossed */

IF   ( ((SELECT distributor_latency FROM #TokenHistory) IS NULL) OR ((SELECT distributor_latency FROM #TokenHistory) > @DistributionThreshold) )

    OR ( ((SELECT subscriber_latency  FROM #TokenHistory) IS NULL) OR ((SELECT subscriber_latency FROM #TokenHistory)  > @SubscriberThreshold  ) )

BEGIN;

-- send mail

    DECLARE @recipients nvarchar(1000), @subject nvarchar(1000), @profile_name nvarchar(1000), @body_format nvarchar(100), @msg nvarchar(4000);

    SELECT @msg =        N'The replication delivery letancy has crossed the letancy threshod defined: ' + 

                                    N' Distribution Threshold - ' + CAST (@DistributionThreshold AS sysname) + 

                                    N' Subscriber Threshold - '        + CAST (@SubscriberThreshold AS sysname) +

                                    N'<br><br><table CELLSPACING="0" border="1">

                                    <tr><td width="100"><font color="red">

                                    <U>distributor_latency</U></td><td width="100"><font color="red">

                                    <U>subscriber</U></td><td width="100"><font color="red">

                                    <U>subscriber_db</U></td><td width="100"><font color="red">

                                    <U>subscriber_latency</U></td><td width="100"><font color="red">

                                    <U>overall_latency</U>

                                    </td></tr>';

    SELECT  @msg = @msg 

                                + '<tr><td>'  + CASE WHEN distributor_latency IS NULL THEN 'NULL' ELSE CAST(distributor_latency AS sysname) END

                                + '</td><td>' + subscriber 

                                + '</td><td>' + subscriber_db 

                                + '</td><td>' + CASE WHEN subscriber_latency IS NULL THEN 'NULL' ELSE CAST(subscriber_latency AS sysname) END

                                + '</td><td>' + CASE WHEN overall_latency IS NULL THEN 'NULL' ELSE CAST(overall_latency AS sysname) END

                                + '</td><tr>'

                                + '</table>'

    FROM #TokenHistory ;

    SELECT 

                 @recipients        = N'someone@domain.com' 

                ,@subject                =    @@SERVERNAME + N': MonitorReplicationDeliveryLatency'

                ,@profile_name    = N'SqlMail' 

                ,@body_format        =    N'HTML'

    EXEC msdb.dbo.sp_send_dbmail 

                 @recipients        = @recipients

                ,@body                    = @msg

                ,@subject                = @subject 

                ,@profile_name    = @profile_name 

                ,@body_format        = @body_format;

END;

-- Mark Ident column as NOT FOR REPLICATION

EXEC sp_msforeachtable 

    @command1 = 'declare @int int set @int =object_id("?") EXEC sys.sp_identitycolumnforreplication @int, 1';

CREATE PROCEDURE sys.sp_identitycolumnforreplication  

(  

 @object_id int,  

 @value  bit  

)  

AS  

BEGIN  

 DECLARE @identity_column sysname  

 IF IS_SRVROLEMEMBER('sysadmin') = 0  

  AND IS_MEMBER('db_owner') = 0  

 BEGIN  

  RAISERROR(21050, 14, -1)  

  RETURN 1  

 END  

 SELECT @identity_column = NULL  

 SELECT @identity_column = name   

  FROM sys.columns  

  WHERE object_id = @object_id  

   AND COLUMNPROPERTY(object_id, name, 'IsIdentity') = 1  

 IF @identity_column IS NOT NULL  

 BEGIN  

  EXEC %%ColumnEx(ObjectID = @object_id, Name = @identity_column).SetIdentityNotForRepl(Value = @value)  

  IF @@ERROR <> 0   

   RETURN 1  

 END  

 RETURN 0  

END