Dependencies for System Stored Procedure: master.dbo.sp_CompareDBProcedures

ReferencingFQN ReferencingType ReferencedFQN ReferencedType Depth RedHierarchy
master.dbo.sp_CompareDBProcedures Stored procedure master.dbo.sp_ExecTemplate Stored procedure 1 [master].[dbo].[sp_ExecTemplate]
master.dbo.sp_CompareDBProcedures Stored procedure msdb.dbo.sp_send_dbmail Stored procedure 1 [msdb].[dbo].[sp_send_dbmail]
master.dbo.sp_CompareDBProcedures Stored procedure Util.dbo.ParseDelimited SQL inline table-valued function 1 [Util].[dbo].[ParseDelimited]
master.dbo.sp_CompareDBProcedures Stored procedure Util.dbo.PrintLargeText Stored procedure 1 [Util].[dbo].[PrintLargeText]
master.dbo.sp_CompareDBProcedures Stored procedure Util.dbo.Servers View 1 [Util].[dbo].[Servers]
master.dbo.sp_CompareDBProcedures Stored procedure Util.dbo.StringConcat Aggregate function (CLR) 1 [Util].[dbo].[StringConcat]
master.dbo.sp_CompareDBProcedures Stored procedure Util.dbo.TrimBothEnds SQL scalar function 1 [Util].[dbo].[TrimBothEnds]
master.dbo.sp_CompareDBProcedures Stored procedure Util.FS.AppendAllTextToFile Assembly (CLR) table-valued function 1 [Util].[FS].[AppendAllTextToFile]
master.dbo.sp_CompareDBProcedures Stored procedure Util.FS.GetFileInfo Assembly (CLR) table-valued function 1 [Util].[FS].[GetFileInfo]
master.dbo.sp_CompareDBProcedures Stored procedure Util.SE.SQLExecution Table 1 [Util].[SE].[SQLExecution]
master.dbo.sp_CompareDBProcedures Stored procedure Util.SE.SQLExecutionGetGroup SQL inline table-valued function 1 [Util].[SE].[SQLExecutionGetGroup]
master.dbo.sp_CompareDBProcedures Stored procedure Util.SE.SQLExecutionGroup Table 1 [Util].[SE].[SQLExecutionGroup]
master.dbo.sp_CompareDBProcedures Stored procedure Util.SE.SQLExecutionWait Stored procedure 1 [Util].[SE].[SQLExecutionWait]
master.dbo.sp_ExecTemplate Stored procedure Util.dbo.GetColumnType SQL inline table-valued function 2 [master].[dbo].[sp_ExecTemplate], [Util].[dbo].[GetColumnType]
master.dbo.sp_ExecTemplate Stored procedure Util.dbo.ParseSPDefaults Assembly (CLR) table-valued function 2 [master].[dbo].[sp_ExecTemplate], [util].[dbo].[ParseSPDefaults]
msdb.dbo.sp_send_dbmail Stored procedure msdb.dbo.get_principal_id SQL scalar function 2 [msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[get_principal_id]
msdb.dbo.sp_send_dbmail Stored procedure msdb.dbo.sp_validate_user Stored procedure 2 [msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[sp_validate_user]
msdb.dbo.sp_send_dbmail Stored procedure msdb.dbo.sysmail_principalprofile Table 2 [msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[sysmail_principalprofile]
msdb.dbo.sp_send_dbmail Stored procedure msdb.dbo.sysmail_verify_profile_sp Stored procedure 2 [msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[sysmail_verify_profile_sp]
Util.dbo.ParseDelimited SQL inline table-valued function Util.dbo.ParseDelimited_CLR Assembly (CLR) table-valued function 2 [Util].[dbo].[ParseDelimited], [Util].[dbo].[ParseDelimited_CLR]
Util.dbo.PrintLargeText Stored procedure Util.dbo.PrintLargeText_CLR Assembly (CLR) stored-procedure 2 [Util].[dbo].[PrintLargeText], [Util].[dbo].[PrintLargeText_CLR]
Util.dbo.StringConcat Aggregate function (CLR) Util.UtilClr ASSEMBLY 2 [Util].[dbo].[StringConcat], [Util].[UtilClr]
Util.FS.AppendAllTextToFile Assembly (CLR) table-valued function Util.UtilClr ASSEMBLY 2 [Util].[FS].[AppendAllTextToFile], [Util].[UtilClr]
Util.FS.GetFileInfo Assembly (CLR) table-valued function Util.UtilClr ASSEMBLY 2 [Util].[FS].[GetFileInfo], [Util].[UtilClr]
Util.SE.SQLExecutionGetGroup SQL inline table-valued function Util.SE.SQLExecutionAll View 2 [Util].[SE].[SQLExecutionGetGroup], [Util].[SE].[SQLExecutionAll]
Util.SE.SQLExecutionWait Stored procedure Util.SE.SQLExecutionAll View 2 [Util].[SE].[SQLExecutionWait], [Util].[SE].[SQLExecutionAll]
Util.SE.SQLExecutionWait Stored procedure Util.SE.SQLExecutionTracker Table 2 [Util].[SE].[SQLExecutionWait], [Util].[SE].[SQLExecutionTracker]
Util.SE.SQLExecutionWait Stored procedure Util.SE.SQLExecutionTrackerPivot View 2 [Util].[SE].[SQLExecutionWait], [Util].[SE].[SQLExecutionTrackerPivot]
msdb.dbo.sysmail_verify_profile_sp Stored procedure msdb.dbo.sysmail_profile Table 3 [msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[sysmail_verify_profile_sp], [msdb].[dbo].[sysmail_profile]
Util.SE.SQLExecutionTrackerPivot View Util.SE.SQLExecutionStatus Table 3 [Util].[SE].[SQLExecutionWait], [Util].[SE].[SQLExecutionTrackerPivot], [Util].[SE].[SQLExecutionStatus]

System Stored Procedure: master.dbo.sp_CompareDBProcedures

USE master
GO
IF OBJECT_ID('sp_CompareDBProcedures') IS NULL EXEC ('CREATE PROCEDURE sp_CompareDBProcedures AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_CompareDBProcedures
    @SourceDatabase VARCHAR(256) = NULL,
    @DestinationDatabaseList VARCHAR(MAX) = NULL,
    @MetadataWhereClause VARCHAR(MAX) = NULL,
    @SaveToFile BIT = 0,
    @PrintReport BIT = 1,
    @recipients NVARCHAR(1000) = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @SQLExec NVARCHAR(MAX),
    @WhereClause VARCHAR(MAX),
    @SQL NVARCHAR(MAX) = NULL,
    @Error INT,
    @CompCount INT,
    @NCD VARCHAR(MAX),
    @cmd VARCHAR(1000),
    @ListFile VARCHAR(256),
    @ZipFile VARCHAR(256),
    --@NewID VARCHAR(128) = LOWER(CAST(NEWID() AS VARCHAR(128))),
    @NewID VARCHAR(128) = '8c91a03d-f9b4-46c0-a305-b5dcc79ff907',
    --@Created VARCHAR(100) = CONVERT(VARCHAR(22), GETDATE(), 126) + '-07:00',
    @Created VARCHAR(100) = '2011-09-16T13:23:33.63-04:00',
    @UserName VARCHAR(256) = SUSER_NAME(),
    @IntVal INT,
    @Directory VARCHAR(256) = 'C:\Temp\',
    @ProjectFileContents VARCHAR(MAX),
    @ProjectFileName VARCHAR(1000),
    @IsSysAdmin INT = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0),
    @FilePrefix VARCHAR(256) = 'ObjectComp',
    @Postfix VARCHAR(30) = '_' + REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(CONVERT(VARCHAR(100), GETDATE(), 121), 5, 128), '-', ''), ' ', '_'), ':', ''), '.',
                                         '_'),
    @ProcessStart DATETIME = GETDATE()

--#region Print Exec Template
IF ISNULL(@SourceDatabase, '') = ''
    OR ISNULL(@DestinationDatabaseList, '') = ''
    BEGIN
        EXEC master.dbo.sp_ExecTemplate
            @ObjectName = 'sp_CompareDBProcedures',
            @PrintDefault = 0,
            @DeclareReturn = 0,
            @PrintReturnLine = 0,
            @DoNotDeclareVar = 1
        RETURN 30
    END
--#endregion
--#region Format Input
IF OBJECT_ID('tempdb..#tempinput') IS NOT NULL
    DROP TABLE #tempinput ;
WITH    s AS (SELECT    1 AS IsSource,
                        PARSENAME(Util.dbo.TrimBothEnds(@SourceDatabase), 2) AS SERVERNAME,
                        PARSENAME(@SourceDatabase, 1) AS DatabaseName
              UNION ALL
              SELECT DISTINCT
                        0 AS IsSource,
                        PARSENAME(Util.dbo.TrimBothEnds(Field), 2) AS SERVERNAME,
                        PARSENAME(Util.dbo.TrimBothEnds(Field), 1) AS DatabaseName
              FROM      Util.dbo.ParseDelimited(@DestinationDatabaseList, ',')
              WHERE     field <> '')
    SELECT  k.IsSource,
            k.SERVERNAME,
            k.DatabaseName,
            '##' + REPLACE(k.SERVERNAME, '\', '_') + '_' + k.DatabaseName + CASE WHEN k.IsSource = 1 THEN '_' + @FilePrefix + '_From'
                                                                                 ELSE '_To'
                                                                            END + @PostFix AS TableName,
            '##' + REPLACE(k.SERVERNAME, '\', '_') + '_' + k.DatabaseName + '_Assembly' + CASE WHEN k.IsSource = 1 THEN '_' + @FilePrefix + '_From'
                                                                                               ELSE '_To'
                                                                                          END + @PostFix AS AssemblyTableName,
            CASE WHEN k.IsSource = 0 THEN '##' + REPLACE(k.SERVERNAME, '\', '_') + '_' + k.DatabaseName + CASE WHEN k.IsSource = 1 THEN '_From'
                                                                                                               ELSE '_' + @FilePrefix + '_To_Result'
                                                                                                          END + @PostFix
            END AS ResultTableName,
            CASE WHEN k.IsSource = 0
                 THEN '##' + REPLACE(k.SERVERNAME, '\', '_') + '_' + k.DatabaseName + '_Assembly' + CASE WHEN k.IsSource = 1 THEN '_From'
                                                                                                         ELSE '_' + @FilePrefix + '_To_Result'
                                                                                                    END + @PostFix
            END AS AssemblyResultTableName
    INTO    #tempinput
    FROM    (SELECT s.IsSource,
                    u.SQLServerName AS SERVERNAME,
                    CASE WHEN u.SQLServerName = @@SERVERNAME THEN DB_NAME(DB_ID(s.DatabaseName))
                         ELSE s.DatabaseName
                    END AS DatabaseName
             FROM   s
             LEFT OUTER JOIN Util.dbo.Servers u ON u.SQLServerName = ISNULL(s.SERVERNAME, @@SERVERNAME)) k
--#endregion
IF @@ROWCOUNT > 2
    OR @recipients <> ''
    SET @SaveToFile = 1
--#region Populate Database List
DECLARE @DatabaseList TABLE (IsSource BIT NOT NULL,
                             SERVERNAME VARCHAR(256) NOT NULL,
                             DatabaseName VARCHAR(256) NOT NULL,
                             TableName NVARCHAR(MAX) NOT NULL,
                             AssemblyTableName NVARCHAR(MAX) NOT NULL,
                             ResultTableName NVARCHAR(MAX) NULL,
                             AssemblyResultTableName NVARCHAR(MAX) NULL,
                             FILENAME VARCHAR(1000) NULL PRIMARY KEY CLUSTERED (SERVERNAME, DatabaseName)) ;
INSERT  @DatabaseList
        (IsSource,
         SERVERNAME,
         DatabaseName,
         TableName,
         AssemblyTableName,
         ResultTableName,
         AssemblyResultTableName,
         FileName)
        SELECT  a.IsSource,
                a.SERVERNAME,
                a.DatabaseName,
                a.TableName,
                a.AssemblyTableName,
                a.ResultTableName,
                a.AssemblyResultTableName,
                CASE WHEN a.IsSource = 0
                          AND @SaveToFile = 1
                     THEN @FilePrefix + @PostFix + '_' + REPLACE(b.SERVERNAME, '\', '_') + '-' + b.DatabaseName + '_VS_' + REPLACE(a.SERVERNAME, '\', '_') + '-'
                          + a.DatabaseName + '.sql'
                END AS FILENAME
        FROM    #tempinput a
        LEFT OUTER JOIN #tempinput b ON a.IsSource = 0
                                        AND b.IsSource = 1
SELECT  @Error = @@ERROR,
        @CompCount = @@ROWCOUNT - 1
--#endregion
--#region On Error or no target select resultset and return
IF @Error <> 0
    OR @CompCount <= 0
    BEGIN
        SELECT  IsSource,
                SERVERNAME,
                DatabaseName
        FROM    #tempinput
        RETURN 30
    END
--#endregion
IF @CompCount > 1
    AND @IsSysAdmin = 0
    BEGIN
        RAISERROR('Only sysadmin can run multiple comparisons.', 16, 1)
        SELECT  *
        FROM    @DatabaseList
        RETURN 40
    END
--#region Validate and format Where clause
SELECT  @MetadataWhereClause = n.NMT,
        @WhereClause = 'WHERE SchemaName NOT IN (''sys'', ''cdc'', ''Metadata'')' + ISNULL('
AND (' + @MetadataWhereClause + ')', '')
FROM    (SELECT NULLIF(Util.dbo.TrimBothEnds(@MetadataWhereClause), '') AS MetadataWhereClause,
                '[' + CHAR(10) + CHAR(13) + CHAR(9) + CHAR(32) + ']' AS WhiteSpace) m
CROSS APPLY (SELECT PATINDEX('WHERE' + whitespace + '%', MetadataWhereClause) AS WPI,
                    PATINDEX('AND' + whitespace + '%', MetadataWhereClause) AS API,
                    PATINDEX('OR' + whitespace + '%', MetadataWhereClause) AS OPI) PPP
CROSS APPLY (SELECT CASE WHEN WPI > 0 THEN SUBSTRING(MetadataWhereClause, 7, LEN(MetadataWhereClause))
                         WHEN API > 0 THEN SUBSTRING(MetadataWhereClause, 5, LEN(MetadataWhereClause))
                         WHEN OPI > 0 THEN SUBSTRING(MetadataWhereClause, 4, LEN(MetadataWhereClause))
                         ELSE MetadataWhereClause
                    END NMT) AS n
--#endregion

DECLARE @ProcParameters NVARCHAR(MAX) = 'EXEC sp_CompareDBProcedures
    @SourceDatabase = ''' + @SourceDatabase + ''',
    @DestinationDatabaseList = ''' + @DestinationDatabaseList + ''',
    @MetadataWhereClause = ' + ISNULL('''' + REPLACE(@MetadataWhereClause, '''', '''''') + '''', 'NULL') + '
GO
'
--#region Table Creation and table selects to print for further analysis
SELECT  @SQL = 'RETURN
' + @ProcParameters + '/* *********************************** To query results refer to below tables *********************************** */
'
        + Util.dbo.StringConcat(CASE WHEN IsSource = 1
                                     THEN '-- Source objects
SELECT SchemaName, ObjectName, CreateDefinition, Definition, IndexScript, AnsiSQL,
(SELECT 1 AS tag, NULL AS parent, CHAR (13) + Definition + CHAR (13) AS [Q!1!Q!CDATA] FOR XML EXPLICIT, TYPE) as DefinitionXML,
CASE WHEN IndexScript <> '''' THEN (SELECT 1 AS tag, NULL AS parent, CHAR (13) + IndexScript + CHAR (13) AS [Q!1!Q!CDATA] FOR XML EXPLICIT, TYPE) END as IndexScriptXML
FROM ' + TableName + '

-- Source Assembly
SELECT AssemblyName, Principal, PermSQL, PermissionSet, CLRName, CreateOrderId, Content FROM ' + AssemblyTableName
                                END + '

', '')
        + Util.dbo.StringConcat(CASE WHEN IsSource = 0
                                     THEN '-- Target Objects
SELECT SchemaName, ObjectName, CreateDefinition, Definition, IndexScript, DropScript,
(SELECT 1 AS tag, NULL AS parent, CHAR (13) + Definition + CHAR (13) AS [Q!1!Q!CDATA] FOR XML EXPLICIT, TYPE) as DefinitionXML,
CASE WHEN IndexScript <> '''' THEN (SELECT 1 AS tag, NULL AS parent, CHAR (13) + IndexScript + CHAR (13) AS [Q!1!Q!CDATA] FOR XML EXPLICIT, TYPE) END as IndexScriptXML
FROM ' + TableName + '

-- Target Assembly
SELECT AssemblyName, Principal, PermSQL, PermissionSet, CLRName, CreateOrderId, Content FROM ' + AssemblyTableName + '

-- Comparison
SELECT SchemaName, ObjectName, CreateDefinition, DiffType, SynchScript,
(SELECT 1 AS tag, NULL AS parent, CHAR (13) + SynchScript + CHAR (13) AS [Q!1!Q!CDATA] FOR XML EXPLICIT, TYPE) as SynchScriptXML
FROM ' + ResultTableName + '

-- Assembly Comparison
SELECT DiffType, AssemblyName, PermSQL, Principal, Content, CLRName, CreateOrderId, SynchScript,
(SELECT 1 AS tag, NULL AS parent, CHAR (13) + SynchScript + CHAR (13) AS [Q!1!Q!CDATA] FOR XML EXPLICIT, TYPE) as SynchScriptXML
FROM ' + AssemblyResultTableName
                                END, '

') + '
/* *********************************** To query results refer to above tables *********************************** */
GO
',
        @SQLExec = Util.dbo.StringConcat(CASE WHEN IsSource = 1 THEN 'CREATE TABLE ' + TableName + ' (SchemaName SYSNAME NOT NULL,
ObjectName SYSNAME NOT NULL,
CreateDefinition VARCHAR(256) NOT NULL,
Definition VARCHAR(MAX) NULL,
AlterScript VARCHAR(MAX) NULL,
IndexScript VARCHAR(MAX) NULL,
AnsiSQL VARCHAR(MAX) NULL,
PRIMARY KEY CLUSTERED (SchemaName, ObjectName))

CREATE TABLE ' + AssemblyTableName + ' (
    AssemblyName sysname NOT NULL,
    Principal sysname NOT NULL,
    PermSQL varchar(15) NOT NULL,
    PermissionSet tinyint NULL,
    CLRName varchar(4000) NULL,
    CreateOrderId int NOT NULL,
    Content varbinary(MAX) NULL)

'
                                         END, '') + Util.dbo.StringConcat(CASE WHEN IsSource = 0
                                                                               THEN 'CREATE TABLE ' + TableName + ' (SchemaName SYSNAME NOT NULL,
ObjectName SYSNAME NOT NULL,
CreateDefinition VARCHAR(256) NOT NULL,
Definition VARCHAR(MAX) NULL,
AlterScript VARCHAR(MAX) NULL,
IndexScript VARCHAR(MAX) NULL,
DropScript VARCHAR(MAX) NULL,
PRIMARY KEY CLUSTERED (SchemaName, ObjectName))

CREATE TABLE ' + AssemblyTableName + ' (
    AssemblyName sysname NOT NULL,
    Principal sysname NOT NULL,
    PermSQL varchar(15) NOT NULL,
    PermissionSet tinyint NULL,
    CLRName varchar(4000) NULL,
    CreateOrderId int NOT NULL,
    Content varbinary(MAX) NULL)

CREATE TABLE ' + ResultTableName + ' (SchemaName SYSNAME NULL,
ObjectName SYSNAME NULL,
CreateDefinition VARCHAR(256) NULL,
DiffType VARCHAR(30) NULL,
SynchScript VARCHAR(MAX) NULL)

CREATE TABLE ' + AssemblyResultTableName + ' (SchemaName SYSNAME NULL,
    DiffType varchar(30) NULL,
    AssemblyName sysname NULL,
    PermSQL varchar(30) NULL,
    Principal sysname NULL,
    Content varbinary(MAX) NULL,
    CLRName varchar(4000) NULL,
    CreateOrderId int NULL,
    SynchScript varchar(MAX) NULL)'
                                                                          END, '')
FROM    @DatabaseList

EXEC Util.dbo.PrintLargeText
    @SQL

EXEC(@SQLExec)
--#endregion

--#region Create temp exec comparison tables
INSERT  Util.SE.SQLExecutionGroup
        (Description, UseTransaction)
VALUES  ('CompareDBProcedures', 0)

DECLARE @ExecutionGroupId INT = SCOPE_IDENTITY()
SET CONTEXT_INFO @ExecutionGroupId

INSERT  Util.SE.SQLExecution
        (ExecutionGroupId,
         Description,
         DatabaseName,
         CommandText)
        SELECT  @ExecutionGroupId AS ExecutionGroupId,
                @FilePrefix + ' Step 1 ' + SERVERNAME + '.' + DatabaseName AS Description,
                CASE WHEN SERVERNAME = @@SERVERNAME THEN DatabaseName
                     ELSE DB_NAME()
                END AS DatabaseName,
                ct.CommandText
        FROM    @DatabaseList
        CROSS APPLY (SELECT CASE WHEN IsSource = 1 THEN 'SchemaName, ObjectName, CreateDefinition, Definition, AlterScript, IndexScript, AnsiSQL'
                                 ELSE 'SchemaName, ObjectName, CreateDefinition, Definition, AlterScript, IndexScript, DropScript'
                            END AS ColumnList) cl
        CROSS APPLY (SELECT CASE WHEN IsSource = 1 THEN 'INSERT ' + TableName + ' WITH (TABLOCK) (' + ColumnList + ')
SELECT SchemaName, ObjectName, CreateDefinition, Util.dbo.TrimBothEnds(Definition) AS Definition, AlterScript, IndexScript, AnsiSQL
'                                ELSE 'INSERT ' + TableName + ' WITH (TABLOCK) (' + ColumnList + ')
SELECT SchemaName, ObjectName, CreateDefinition, Util.dbo.TrimBothEnds(Definition) AS Definition, AlterScript, IndexScript, DropScript
'
                            END + CASE WHEN SERVERNAME = @@SERVERNAME THEN 'FROM Metadata.ProcedureScripts a (NOLOCK)
' + @WhereClause                       ELSE 'FROM OPENROWSET(''SQLNCLI'', ''Server=' + SERVERNAME + ';Connect Timeout=3;UID=Metadata;PWD=dorot@6132;'',
''SELECT ' + ColumnList + '
FROM ' + DatabaseName + '.Metadata.ProcedureScripts (NOLOCK)
' + REPLACE(@WhereClause, '''', '''''') + ''') AS a
'
                                  END + '
INSERT ' + AssemblyTableName + ' WITH (TABLOCK) (AssemblyName, Principal, PermSQL, PermissionSet, CLRName, CreateOrderId, Content)
SELECT AssemblyName, Principal, PermSQL, PermissionSet, CLRName, CreateOrderId, Content
' + CASE WHEN SERVERNAME = @@SERVERNAME THEN 'FROM Metadata.Assemblies a (NOLOCK)'
         ELSE 'FROM OPENROWSET(''SQLNCLI'', ''Server=' + SERVERNAME + ';Connect Timeout=3;UID=Metadata;PWD=dorot@6132;'',
''SELECT AssemblyName, Principal, PermSQL, PermissionSet, CLRName, CreateOrderId, Content
FROM ' + DatabaseName + '.Metadata.Assemblies (NOLOCK)'') AS a
'
    END AS CommandText) ct

EXEC Util.SE.SQLExecutionWait
    @ExecutionGroupId = @ExecutionGroupId,
    @PrintError = 1

IF EXISTS ( SELECT  *
            FROM    Util.SE.SQLExecutionGetGroup(@ExecutionGroupId)
            WHERE   ISNULL(Status, '') <> 'Completed' )
    BEGIN
        SELECT  *
        FROM    Util.SE.SQLExecutionGetGroup(@ExecutionGroupId)
        RETURN 30
    END
--#endregion
--#region Compare and insert into comparison tables
IF OBJECT_ID('tempdb..#step2') IS NOT NULL
    DROP TABLE #step2

SELECT  @FilePrefix + ' Step 2 ' + s.SERVERNAME + '.' + s.DatabaseName + ' vs ' + t.SERVERNAME + '.' + t.DatabaseName AS Description,
        ns.NoChangeStr,
        CAST(k.CommandText AS NVARCHAR(MAX)) AS CommandText
INTO    #step2
FROM    @DatabaseList t
INNER JOIN @DatabaseList s ON s.IsSource = 1
                              AND t.IsSource = 0
CROSS APPLY (SELECT '---- CompareDBProcedures ' + s.SERVERNAME + '.' + s.DatabaseName + ' vs ' + t.SERVERNAME + '.' + t.DatabaseName
                    + ' NO CHANGES DETECTED !!! ----' AS NoChangeStr) ns
CROSS APPLY (SELECT CASE WHEN @CompCount > 1 THEN 'DECLARE @SQL VARCHAR(MAX)
'                        ELSE ''
                    END + ';WITH    S1
          AS (SELECT    dt.DiffType,
                        ISNULL(l.SchemaName, r.SchemaName) AS SchemaName,
                        ISNULL(l.ObjectName, r.ObjectName) AS ObjectName,
                        ISNULL(l.CreateDefinition, r.CreateDefinition) AS CreateDefinition,
                        l.IndexScript,
                        l.AnsiSQL,
                        l.DEFINITION AS LDefinition,
                        l.AlterScript AS LAlterScript,
                        r.DEFINITION AS RDefinition,
                        r.AlterScript as RAlterScript,
                        r.DropScript
              FROM      ' + s.TableName + ' l
              FULL OUTER JOIN ' + t.TableName + ' r ON l.SchemaName = r.SchemaName
                                                           AND l.ObjectName = r.ObjectName
              CROSS APPLY (SELECT   CASE WHEN r.SchemaName IS NULL THEN ''CREATE''
                                         WHEN L.SchemaName IS NULL THEN ''DROP''
                                         WHEN LEN(l.Definition) <> LEN(r.Definition)
                                              OR l.Definition <> r.Definition THEN ''DIFF''
                                    END AS DiffType) dt
              WHERE     dt.DiffType IS NOT NULL)

    INSERT ' + t.ResultTableName + ' WITH (TABLOCK) (DiffType, SchemaName, ObjectName, CreateDefinition, SynchScript)
    SELECT  DiffType, SchemaName, ObjectName, CreateDefinition, SynchScript
    FROM    s1
    OUTER APPLY (SELECT ISNULL(LAlterScript, Util.dbo.GenerateAlterScript(LDefinition)) AS LAlter,
                        ISNULL(RAlterScript, Util.dbo.GenerateAlterScript(RDefinition)) AS RAlter
                 WHERE  DiffType = ''DIFF'') b
    CROSS APPLY (SELECT CASE DiffType
                          WHEN ''CREATE'' THEN ISNULL(AnsiSQL + ''
GO
'','''') + LDefinition + ISNULL(''
GO
'' + IndexScript, '''')      WHEN ''DROP'' THEN DropScript
                          WHEN ''DIFF'' THEN ISNULL(AnsiSQL + ''
GO
'', '''') + LAlter + ISNULL(''
GO
'' + IndexScript, '''')
                        END AS SynchScript) d
    WHERE   ((DiffType = ''DIFF''
              AND LAlter <> RAlter)
             OR DiffType <> ''Diff'')

INSERT ' + t.AssemblyResultTableName + ' WITH (TABLOCK) (DiffType, AssemblyName, PermSQL, Principal, Content, CLRName, CreateOrderId, SynchScript)
SELECT  DiffType,
        ISNULL(L.AssemblyName, r.AssemblyName) AS AssemblyName,
        ISNULL(l.PermSQL, r.PermSQL) AS PermSQL,
        ISNULL(l.Principal, r.Principal) AS Principal,
        ISNULL(l.CONTENT, r.CONTENT) AS [Content],
        ISNULL(l.CLRName, r.CLRName) AS CLRName,
        ISNULL(l.CreateOrderId, r.CreateOrderId) AS CreateOrderId,
        ss.SynchScript
FROM    ' + s.AssemblyTableName + ' l
FULL OUTER JOIN ' + t.AssemblyTableName + ' r ON l.AssemblyName = r.AssemblyName
        CROSS APPLY (SELECT CASE WHEN r.AssemblyName IS NULL THEN ''CREATE''
                                 WHEN L.AssemblyName IS NULL THEN ''DROP''
                                 WHEN l.PermSQL <> r.PermSQL
                                      OR l.Principal <> r.Principal
                                      OR l.Content <> r.Content THEN ''DIFF''
                            END AS DiffType) dt
        CROSS APPLY (SELECT CASE DiffType
                              WHEN ''CREATE'' THEN ''CREATE ASSEMBLY ['' + l.AssemblyName + '']
AUTHORIZATION ['' + l.Principal + '']
FROM '' + CONVERT(VARCHAR(MAX), l.Content, 1) + ''
WITH PERMISSION_SET = '' + l.PermSQL
                              WHEN ''DIFF''
                              THEN CASE WHEN (l.PermSQL <> r.PermSQL
                                              OR l.Content <> r.CONTENT)
                                             AND l.Principal <> r.Principal THEN ''ALTER ASSEMBLY ['' + L.AssemblyName + '']
FROM '' + CONVERT(VARCHAR(MAX), l.Content, 1) + ''
WITH PERMISSION_SET = '' + l.PermSQL + ''
GO
ALTER AUTHORIZATION ON ['' + L.AssemblyName + ''] TO ['' + l.Principal + '']''
                                        WHEN l.PermSQL <> r.PermSQL
                                             OR l.Content <> r.Content THEN ''ALTER ASSEMBLY ['' + L.AssemblyName + '']
FROM '' + CONVERT(VARCHAR(MAX), l.Content, 1) + ''
WITH PERMISSION_SET = '' + l.PermSQL     WHEN l.Principal <> r.Principal THEN ''ALTER AUTHORIZATION ON ['' + L.AssemblyName + ''] TO ['' + L.Principal + '']''
                                   END
                              WHEN ''DROP'' THEN ''DROP ASSEMBLY ['' + r.AssemblyName + '']''
                            END AS SynchScript) ss
        WHERE   DiffType IS NOT NULL

SELECT @SQL = Util.dbo.StringConcat(SynchScript + ''
GO
'','''') FROM ' + t.AssemblyResultTableName + '

;WITH Ord as (SELECT  CreateDefinition, OrderId
FROM    ( VALUES ( ''SCHEMA'', 1), ( ''TYPE'', 5), ( ''SYNONYM'', 10), ( ''VIEW'', 15), ( ''FUNCTION'', 20), ( ''PROCEDURE'', 25), ( ''TRIGGER'', 30) )
AS d (CreateDefinition, OrderId))

SELECT @SQL  =  ISNULL(@SQL, '''') + ''RETURN
' + REPLACE(@ProcParameters, '''', '''''') + '
USE [' + t.DatabaseName + '] -- ServerName: ' + t.SERVERNAME
                    + '
GO
'' + Util.dbo.StringConcat(''---- '' + DiffType + '' '' + CreateDefinition + '' '' + FullName + '' ----
GO
'' + SynchScript, ''
GO
'') + ''
GO
''
FROM (SELECT TOP 999999 a.SchemaName, a.ObjectName, a.CreateDefinition, a.DiffType, a.SynchScript, CASE WHEN a.CreateDefinition = ''SCHEMA'' THEN QUOTENAME(SchemaName) ELSE QUOTENAME(SchemaName) + ''.'' + QUOTENAME(ObjectName) END AS FullName
FROM ' + t.ResultTableName + ' a LEFT OUTER JOIN Ord o ON o.CreateDefinition = a.CreateDefinition
ORDER BY CASE WHEN DiffType = ''DROP'' THEN -1 ELSE 1 END * ISNULL(OrderId, 255), SchemaName, ObjectName)k

SET @SQL = ISNULL(@SQL, '''')
' + CASE WHEN @SaveToFile = 1 THEN 'DECLARE @I INT
SELECT  @I = COUNT(*)
FROM    Util.FS.AppendAllTextToFile(''' + @Directory + t.FileName + ''', @SQL, 1)

SET @SQL = CASE WHEN @SQL = '''' THEN ''' + NoChangeStr + ''' ELSE @SQL END

'        ELSE ''
    END + CASE WHEN @CompCount = 1
               THEN 'IF EXISTS(SELECT * FROM ' + t.AssemblyResultTableName
                    + ') SELECT DiffType, AssemblyName, PermSQL, Principal, Content, CLRName, CreateOrderId FROM ' + t.AssemblyResultTableName + '

    SELECT SchemaName, ObjectName, CreateDefinition, DiffType FROM ' + t.ResultTableName
               ELSE ''
          END AS CommandText) k
--#endregion

--#region Create Solution
IF @SaveToFile = 1
    BEGIN
        SELECT  @ProjectFileName = @Directory + @FilePrefix + @Postfix + '.ssmssqlproj',
                @ProjectFileContents = '
+ @FilePrefix + @Postfix
                + '">
  
    
      
' + Util.dbo.StringConcat(DISTINCT '         + SERVERNAME + ':' + @UserName + '">
          ' + @Created + '
          SQL
          ' + SERVERNAME + '
          
          Windows Authentication
          Corestore
          3
          0
          NotSpecified
          
        ', '
') + '
      
    
    
      
' + Util.dbo.StringConcat(CASE WHEN IsSource = 0 THEN '         + FILENAME + '">
          ' + @NewID + ':' + SERVERNAME + ':True
          ' + SERVERNAME + '
          
          ' + FILENAME + ''
                          END + '
        ', '
') + '
      
    
    
      
    
  
'
        FROM    @DatabaseList

        SELECT  @IntVal = COUNT(*)
        FROM    Util.FS.AppendAllTextToFile(@ProjectFileName, @ProjectFileContents, 1)
    END
    --#endregion

--#region Do multi comparison
IF @CompCount > 1
    BEGIN
        INSERT  Util.SE.SQLExecutionGroup
                (Description, UseTransaction)
        VALUES  ('CompareDBProcedures', 0)

        SET @ExecutionGroupId = SCOPE_IDENTITY()
        SET CONTEXT_INFO @ExecutionGroupId

        INSERT  Util.SE.SQLExecution
                (ExecutionGroupId,
                 Description,
                 DatabaseName,
                 CommandText)
                SELECT  @ExecutionGroupId AS ExecutionGroupId,
                        Description,
                        DB_NAME() AS DatabaseName,
                        CommandText
                FROM    #step2 t
        EXEC Util.SE.SQLExecutionWait
            @ExecutionGroupId = @ExecutionGroupId,
            @PrintError = 1
        PRINT '-- ProcessStart: ' + CAST(@ProcessStart AS VARCHAR) + ', Duration: ' + CAST(DATEDIFF(SECOND, @ProcessStart, GETDATE()) AS VARCHAR) + '
GO'
        IF EXISTS ( SELECT  *
                    FROM    Util.SE.SQLExecutionGetGroup(@ExecutionGroupId)
                    WHERE   ISNULL(Status, '') <> 'Completed' )
            BEGIN
                SELECT  *
                FROM    Util.SE.SQLExecutionGetGroup(@ExecutionGroupId)
                RETURN 30
            END
    END
--#endregion
--#region Do single comparison
ELSE
    IF @CompCount = 1
        BEGIN
            SELECT  @SQLExec = Commandtext,
                    @NCD = NoChangeStr
            FROM    #step2

            EXEC sp_executesql
                @SQLExec,
                N'@SQL VARCHAR(MAX) OUTPUT',
                @SQL OUTPUT
            SET @IntVal = @@ROWCOUNT
            PRINT '-- ProcessStart: ' + CAST(@ProcessStart AS VARCHAR) + ', Duration: ' + CAST(DATEDIFF(SECOND, @ProcessStart, GETDATE()) AS VARCHAR) + '
GO'
            IF @IntVal = 0
                BEGIN
                    PRINT @NCD
                    RETURN
                END
            IF @PrintReport = 1
                EXEC Util.dbo.PrintLargeText
                    @SQL
        END
--#endregion
IF @SaveToFile = 1
    SELECT  d.FileName,
            f.SIZE,
            f.SizeMB,
            f.CreateDate
    FROM    (SELECT @Directory + FileName AS FileName
             FROM   @DatabaseList d
             WHERE  IsSource = 0
             UNION ALL
             SELECT @ProjectFileName
             WHERE  @ProjectFileName IS NOT NULL) d
    CROSS APPLY Util.FS.GetFileInfo(d.FileName) f

IF @recipients <> ''
    BEGIN
        BEGIN TRY

            SELECT  @SQL = Util.dbo.StringConcat(l.FileName, '
')
            FROM    (SELECT @Directory + FileName AS FileName
                     FROM   @DatabaseList
                     WHERE  IsSource = 0
                     UNION ALL
                     SELECT @ProjectFileName AS FILENAME
                     WHERE  @ProjectFileName IS NOT NULL) l
            IF @SQL <> ''
                BEGIN
                    SELECT  @ListFile = @Directory + 'List' + @Postfix + '.txt',
                            @ZipFile = @Directory + @FilePrefix + @Postfix + '.zip'
                    BEGIN
                        SELECT  @IntVal = COUNT(*)
                        FROM    Util.FS.AppendAllTextToFile(@ListFile, @SQL, 1)

                        SET @cmd = '"C:\Program Files\7-Zip\7z.exe" a -tzip ' + @ZipFile + ' @' + @ListFile

                        EXEC xp_cmdshell
                            @cmd,
                            no_output

                        EXEC msdb.dbo.sp_send_dbmail
                            @recipients = @recipients,
                            @subject = 'CompareDBProcedures',
                            @body = @ProcParameters,
                            @file_attachments = @ZipFile
                    END
                END
        END TRY
        BEGIN CATCH
            SELECT  @SQL = Util.dbo.StringConcat(k.FileName, ';')
            FROM    (SELECT @Directory + FileName AS FileName
                     FROM   @DatabaseList
                     WHERE  IsSource = 0
                     UNION ALL
                     SELECT @ProjectFileName AS FILENAME
                     WHERE  @ProjectFileName IS NOT NULL) k
            IF @SQL <> ''
                EXEC msdb.dbo.sp_send_dbmail
                    @recipients = @recipients,
                    @subject = 'CompareDBProcedures',
                    @body = @ProcParameters,
                    @file_attachments = @SQL
        END CATCH
    END
GO
EXEC sys.sp_ms_marksystemobject sp_CompareDBProcedures
GO

Description for System Stored Procedure: master.dbo.sp_CompareDBProcedures

Todo
Site Map | Printable View | © 2008 - 2012 NuRoN Consulting, INC | Powered by mojoPortal | HTML 5 | CSS | Original design by Andreas Viklund
Share This Using Popular Bookmarking Services