USE MASTER
GO
IF OBJECT_ID('dbo.sp_compile_proc') IS NULL
EXEC ('CREATE PROCEDURE dbo.sp_compile_proc AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_compile_proc
@WhereClause VARCHAR(MAX) = 'ObjectName = ObjectName AND SchemaName = SchemaName AND Type = Type',
@FilePath VARCHAR(1000) = 'C:\Temp\sp_compile_proc.sql',
@SQL VARCHAR(MAX) = NULL OUTPUT,
@ExecuteSQL BIT = 1
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
PRINT 'RETURN'
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_compile_proc',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
PRINT 'GO'
DECLARE @ROWCOUNT INT
SELECT @WhereClause = CASE WHEN wc LIKE 'WHERE%' THEN wc
ELSE 'WHERE ' + wc
END
FROM (SELECT Util.dbo.TrimBothEnds (@WhereClause) AS wc) w
IF OBJECT_ID('tempdb..#sp_compile_proc_Indexes') IS NOT NULL
DROP TABLE #sp_compile_proc_Indexes
IF OBJECT_ID('tempdb..#sp_compile_proc_Objects') IS NOT NULL
DROP TABLE #sp_compile_proc_Objects
CREATE TABLE #sp_compile_proc_Objects ([SchemaName] VARCHAR(128) NOT NULL,
[ObjectName] VARCHAR(128) NOT NULL,
[object_id] INT NOT NULL,
[CreateDefinition] VARCHAR(30) NOT NULL,
[TYPE] CHAR(2) NOT NULL)
SET @SQL = 'INSERT #sp_compile_proc_Objects WITH (TABLOCK)
([SchemaName],
[ObjectName],
[object_id],
[CreateDefinition],
[TYPE])
SELECT SchemaName,
ObjectName,
object_id,
CreateDefinition,
TYPE
FROM (SELECT s.name AS SchemaName,
o.NAME AS ObjectName,
o.object_id,
ot.CreateDefinition,
o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AS TYPE
FROM sys.objects o (NOLOCK)
INNER JOIN sys.schemas s (NOLOCK) ON s.schema_id = o.schema_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE o.Type IN (''FN'', ''IF'', ''P'', ''TF'', ''TR'', ''V'')
AND s.name NOT IN (''cdc'', ''sys'')
AND NOT (s.name = ''dbo''
AND (o.name LIKE ''sys%''
OR o.name = ''fn_diagramobjects''))) o
' + ISNULL(@Whereclause, '')
EXEC (@SQL)
--#region Index script section
;
WITH IndexColumns
AS (SELECT TOP 99999999
v.OBJECT_ID,
v.CreateDefinition,
v.SchemaName,
v.ObjectName,
ix.NAME AS IndexName,
ix.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
ds.type AS DataspaceType,
ds.name AS DataspaceName,
ix.index_id,
ix.is_unique,
ix.is_primary_key,
ix.is_unique_constraint,
ix.is_disabled,
ix.filter_definition,
ix.has_filter,
(SELECT ISNULL('WITH (' + Util.dbo.StringConcat(IndexOption, ', ') + ')', '')
FROM (SELECT CASE pr.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN 'ROW' THEN 'DATA_COMPRESSION = ROW'
WHEN 'PAGE' THEN 'DATA_COMPRESSION = PAGE'
END AS IndexOption
UNION ALL
SELECT 'SORT_IN_TEMPDB = ON' IndexOption
UNION ALL
SELECT CASE WHEN IGNORE_DUP_KEY = 1 THEN 'IGNORE_DUP_KEY = ON'
END AS IndexOption) k) AS DATA_COMPRESSION,
CASE WHEN ic.key_ordinal > 0 THEN QUOTENAME(cl.NAME) + ' ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC'
ELSE 'ASC'
END
ELSE NULL
END AS IndexCol,
CASE WHEN ic.is_included_column > 0 THEN QUOTENAME(cl.NAME)
ELSE NULL
END AS IncludeCol,
CASE WHEN ic.partition_ordinal > 0 THEN QUOTENAME(cl.name)
ELSE NULL
END AS PartitionedCol
FROM #sp_compile_proc_Objects v (NOLOCK)
INNER JOIN sys.indexes AS ix (NOLOCK) ON v.OBJECT_ID = ix.OBJECT_ID
LEFT OUTER JOIN sys.data_spaces AS ds (NOLOCK) ON ds.data_space_id = ix.data_space_id
LEFT OUTER JOIN sys.partitions AS pr (NOLOCK) ON pr.OBJECT_ID = ix.OBJECT_ID
AND pr.index_id = ix.index_id
AND pr.partition_number = 1
INNER JOIN sys.index_columns AS ic (NOLOCK) ON ic.OBJECT_ID = ix.OBJECT_ID
AND ic.index_id = ix.index_id
INNER JOIN sys.columns AS cl (NOLOCK) ON cl.OBJECT_ID = ic.OBJECT_ID
AND cl.column_id = ic.column_id
WHERE ix.index_id > 0
AND v.TYPE = 'V'
ORDER BY v.OBJECT_ID,
ix.index_id,
ic.key_ordinal,
ic.index_column_id),
Indexes
AS (SELECT TOP 99999999
OBJECT_ID,
'IF NOT EXISTS (SELECT * FROM sys.schemas s (NOLOCK) INNER JOIN sys.objects o (NOLOCK) ON o.schema_id = s.schema_id INNER JOIN sys.indexes i (NOLOCK) ON i.object_id = o.object_id WHERE s.name = '''
+ SchemaName + ''' AND o.name = ''' + ObjectName + ''' AND i.name = ''' + IndexName + ''')
' + CASE WHEN has_filter = 1 THEN 'SET ANSI_NULLS ON
' ELSE ''
END + CASE WHEN is_primary_key = 1
OR is_unique_constraint = 1
THEN 'ALTER ' + CreateDefinition + ' ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' ADD CONSTRAINT ' + QUOTENAME(IndexName) + ' '
+ CASE WHEN is_primary_key = 1 THEN 'PRIMARY KEY '
ELSE 'UNIQUE '
END + IndexType + ' (' + Util.dbo.StringConcat(IndexCol, ', ') + ')' + data_compression + ' ' + CASE DataspaceType
WHEN 'FG'
THEN 'ON ' + QUOTENAME(DataspaceName)
WHEN 'PS'
THEN 'ON ' + QUOTENAME(DataspaceName)
+ '('
+ Util.dbo.StringConcat(PartitionedCol,
'') + ')'
WHEN 'FD' THEN ''
ELSE ''
END
ELSE 'CREATE ' + CASE WHEN is_unique = 1 THEN 'UNIQUE '
ELSE ''
END + IndexType + ' INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' ('
+ Util.dbo.StringConcat(IndexCol, ', ') + ')' + ISNULL(' INCLUDE (' + Util.dbo.StringConcat(IncludeCol, ', ') + ')', '') + ISNULL(' WHERE '
+ filter_definition,
'')
+ data_compression + ' ' + CASE DataspaceType
WHEN 'FG' THEN 'ON ' + QUOTENAME(DataspaceName) + ''
WHEN 'PS' THEN 'ON ' + QUOTENAME(DataspaceName) + '(' + Util.dbo.StringConcat(PartitionedCol, '') + ')'
WHEN 'FD' THEN ''
ELSE ''
END
END + '
' AS Script
FROM IndexColumns ic
GROUP BY OBJECT_ID,
SchemaName,
ObjectName,
index_id,
CreateDefinition,
is_disabled,
has_filter,
IndexType,
is_unique,
is_primary_key,
is_unique_constraint,
IndexName,
filter_definition,
data_compression,
DataspaceType,
DataspaceName)
SELECT OBJECT_ID,
Util.dbo.StringConcat(script, '
') AS Script
INTO #sp_compile_proc_Indexes
FROM Indexes
GROUP BY OBJECT_ID
OPTION (RECOMPILE)
--#endregion
IF OBJECT_ID('tempdb..#sp_compile_proc_Result') IS NOT NULL
DROP TABLE #sp_compile_proc_Result
CREATE TABLE #sp_compile_proc_Result (SchemaName VARCHAR(128) NOT NULL,
ObjectName VARCHAR(128) NOT NULL,
ErrorNumber INT,
ErrorSeverity INT,
ErrorState INT,
ErrorProcedure VARCHAR(256),
ErrorLine INT,
ErrorMessage VARCHAR(8000),
StartTime DATETIME NOT NULL,
EndTime DATETIME NOT NULL,
PRIMARY KEY CLUSTERED (SchemaName, ObjectName))
IF OBJECT_ID('tempdb..#sp_compile_proc_temp') IS NOT NULL
DROP TABLE #sp_compile_proc_temp
SELECT o.TYPE,
o.SchemaName,
o.ObjectName,
o.CreateDefinition,
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW,
(SELECT COUNT (*) FROM #sp_compile_proc_Objects) AS Cnt,
AnsiSQL,
TrimmedDef AS CommandText,
ix.Script AS IndexScript
INTO #sp_compile_proc_temp
FROM #sp_compile_proc_Objects o
INNER JOIN sys.sql_modules sm (NOLOCK) ON sm.OBJECT_ID = o.OBJECT_ID
CROSS APPLY (SELECT Util.dbo.GenerateAlterScript (sm.definition)
AS TrimmedDef) td
LEFT OUTER JOIN #sp_compile_proc_Indexes ix (NOLOCK) ON ix.OBJECT_ID = o.OBJECT_ID
AND sm.is_schema_bound = 1
AND o.type = 'V'
CROSS APPLY (SELECT CASE WHEN sm.uses_ansi_nulls = 0 THEN 'SET ANSI_NULLS OFF'
ELSE 'SET ANSI_NULLS ON'
END + '
' + CASE WHEN sm.uses_quoted_identifier = 0 THEN 'SET QUOTED_IDENTIFIER OFF
' ELSE 'SET QUOTED_IDENTIFIER ON'
END + '
' AS AnsiSQL) an
OPTION (RECOMPILE)
SET @ROWCOUNT = @@ROWCOUNT
IF @ROWCOUNT = 0
BEGIN
PRINT 'NO OBJECTS TO FOUND !!!'
RETURN 30
END
ELSE
PRINT '-- ' + CAST(@ROWCOUNT AS VARCHAR) + ' procedure(s) being compiled'
SELECT @SQL = Util.dbo.StringConcat('EXEC(''' + REPLACE(SQL, '''', '''''') + ''')', '
')
FROM (SELECT 'DECLARE @StartTime DATETIME = GETDATE()
BEGIN TRY
-- PRINT ''-- ' + CAST(ROW AS VARCHAR) + ' / ' + CAST(Cnt AS VARCHAR) + ' [' + Schema2 + '].[' + Object2 + '] -- '' + CAST(GETDATE() AS VARCHAR)
' + ISNULL(AnsiSQL + '
', '') + 'EXEC(''' + REPLACE(CommandText, '''', '''''') + ''')
' + ISNULL(IndexScript + '
', '') + '
INSERT #sp_compile_proc_Result (SchemaName, ObjectName, StartTime, EndTime)
VALUES (''' + Schema2 + ''', ''' + Object2 + ''', @StartTime, GETDATE())
END TRY
BEGIN CATCH
INSERT #sp_compile_proc_Result
(SchemaName,
ObjectName,
ErrorNumber,
ErrorSeverity,
ErrorState,
ErrorProcedure,
ErrorLine,
ErrorMessage,
StartTime,
EndTime)
VALUES (''' + Schema2 + ''',
''' + Object2 + ''',
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE(),
@StartTime,
GETDATE())
END CATCH
' AS SQL
FROM #sp_compile_proc_temp
CROSS APPLY (SELECT REPLACE (SchemaName, '''', '''''') AS Schema2, REPLACE (ObjectName, '''', '''''') AS Object2) p) k
IF ISNULL(@ExecuteSQL, 0) <> 1
RETURN
EXEC (@SQL)
DECLARE @Failure INT,
@Complete INT
SELECT @Failure = SUM(CASE WHEN ErrorNumber IS NOT NULL THEN 1
ELSE 0
END),
@Complete = COUNT(*)
FROM #sp_compile_proc_Result
IF @Failure > 0
BEGIN
SELECT ErrorNumber,
ErrorMessage,
COUNT(*) AS Counter
FROM #sp_compile_proc_Result
WHERE ErrorNumber IS NOT NULL
GROUP BY ErrorNumber,
ErrorMessage
SELECT @SQL = '---- +++++++++ TOTAL ' + CAST(COUNT(*) AS VARCHAR) + ' ERROR(S) OCCURED +++++++++ ----
' + Util.dbo.StringConcat('---- ' + t.CreateDefinition + ' [' + REPLACE(t.SchemaName, '''', '''''') + '].[' + REPLACE(t.Objectname, '''', '''''') + '] ----
' + ISNULL(ErrorMessage, 'N/A') + '
' + ISNULL('Line ' + CAST(a.ErrorLine AS VARCHAR) + ': ', '') + ISNULL(b.Field, ''), '
') + '
---- vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv PROCEDURE LIST vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv ----
GO
' + Util.dbo.StringConcat(ISNULL(AnsiSQL + '
GO
', '') + CommandText + ISNULL('
GO
' + IndexScript, ''), '
GO
') + '
GO
'
FROM #sp_compile_proc_Result a
INNER JOIN #sp_compile_proc_temp t ON a.SchemaName = t.SchemaName
AND a.ObjectName = t.Objectname
OUTER APPLY (SELECT Field
FROM Util.dbo.ParseDelimited(CommandText, '
')
WHERE a.ErrorLine = FieldNum
AND a.ErrorLine > 0) b
WHERE ErrorNumber IS NOT NULL
IF @SQL <> ''
BEGIN
IF @FilePath <> ''
SELECT ReturnVal,
MESSAGE,
@FilePath AS FilePath,
'Saved to ' + @FilePath AS Info
FROM Util.FS.AppendAllTextToFile(@FilePath, @SQL, 1)
EXEC Util.dbo.PrintLargeText
@SQL
END
END
ELSE
PRINT 'ALL ' + CAST(@Complete AS VARCHAR) + ' OBJECTS ARE VALID'
GO
EXEC sys.sp_ms_marksystemobject
sp_compile_proc
GO