DECLARE @sql VARCHAR(MAX),
@CreateAlter BIT = 1,
@GenerateDrop BIT = 1,
@PrintSQL BIT = 0,
@PrintXML BIT = 0,
@FileName VARCHAR(256) = 'C:\temp\all.sql'
SELECT @sql = Util.dbo.StringConcat('PRINT ''-- [' + SchemaName + '].[' + ObjectName + ']''
GO
' + CASE WHEN @CreateAlter = 0
THEN CASE WHEN @GenerateDrop = 1
THEN 'IF OBJECT_ID(''[' + SchemaName + '].[' + ObjectName + ']'') IS NOT NULL DROP ' + TypeName
+ ' [' + SchemaName + '].[' + ObjectName + '];
GO
' ELSE ''
END + definition
ELSE REPLACE(REPLACE(definition, 'CREATE ' + TypeName + ' ' + SchemaName + '.' + ObjectName,
'ALTER ' + TypeName + ' ' + SchemaName + '.' + ObjectName),
'CREATE ' + TypeName + ' [' + SchemaName + '].[' + ObjectName + ']',
'ALTER ' + TypeName + ' ' + SchemaName + '.' + ObjectName)
END, '
GO
')
FROM (SELECT TOP 9999999
S.name AS SchemaName,
o.Name AS ObjectName,
o.type,
tn.TypeName,
LTRIM(RTRIM(m.definition)) AS definition
FROM sys.sql_modules m (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.OBJECT_ID = m.OBJECT_ID
INNER JOIN sys.schemas s ON S.SCHEMA_ID = O.SCHEMA_ID
CROSS APPLY (SELECT CASE WHEN type IN ('FN', 'IF', 'TF') THEN 'FUNCTION'
WHEN type = 'P' THEN 'PROCEDURE'
WHEN type = 'U' THEN 'TABLE'
WHEN type = 'TR' THEN 'TRIGGER'
WHEN type = 'V' THEN 'VIEW'
ELSE 'UNKNOWN TYPE'
END AS TypeName) tn
--WHERE m.definition LIKE '%taxdelin%'
ORDER BY s.name,
o.name) k
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @PrintXML = 1
BEGIN
SELECT 1 AS tag,
NULL AS parent,
'
' + @SQL + '
' AS [Q!1!Q!CDATA]
FOR XML EXPLICIT
END
IF @FileName <> ''
SELECT @FileName AS FILENAME,
*
FROM [Util].[FS].[AppendAllTextToFile](@FileName, @Sql, 1)