USE master
GO
IF OBJECT_ID('dbo.sp_ScriptDB') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_ScriptDB AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_ScriptDB
@DatabaseName VARCHAR(256) = NULL,
@ParentFolder VARCHAR(500) = NULL,
@WhereClause VARCHAR(MAX) = NULL,
@ObjectCount INT = NULL OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ANSI_WARNINGS OFF
DECLARE @DBName VARCHAR(256) = DB_NAME(DB_ID(@DatabaseName))
IF @DBName IS NULL
OR @ParentFolder IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_ScriptDB',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 30
END
DECLARE @InstanceName VARCHAR(256) = REPLACE(@@SERVERNAME, '\', '_') + '.' + @DBName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30), GETDATE(), 20), '-',
''), ':', ''), ' ', '_'),
@SQL VARCHAR(MAX)
SET @ParentFolder = CASE WHEN RIGHT(@ParentFolder, 1) < > '\' THEN @ParentFolder + '\'
ELSE @ParentFolder
END
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp ([SchemaName] SYSNAME NOT NULL,
[ObjectName] SYSNAME NOT NULL,
[OrderId] INT NOT NULL,
[CreateDefinition] VARCHAR(30) NULL,
[AnsiSQL] VARCHAR(MAX) NULL,
[CreateScript] VARCHAR(MAX) NULL PRIMARY KEY CLUSTERED ([SchemaName], [ObjectName], [OrderId]))
SET @SQL = 'INSERT #temp WITH (TABLOCK)
([SchemaName],
[ObjectName],
[OrderId],
[CreateDefinition],
[CreateScript])
SELECT SchemaName,
ObjectName,
CASE WHEN TYPE IN (''PK'', ''UQ'', ''I'') THEN 1000000 + Row
WHEN TYPE = ''D'' THEN 1000 + Row
WHEN type = ''C'' THEN 10000 + Row
WHEN type = ''F'' THEN 10000000 + Row
ELSE 0
END AS OrderId,
CASE WHEN TYPE IN (''PK'', ''UQ'', ''I'', ''D'', ''C'', ''F'') THEN ''TABLE''
ELSE CreateDefinition
END AS CreateDefinition,
ISNULL(AnsiSQL + ''
GO
'', '''') + CreateScript + ISNULL(''
GO
'' + IndexScript, '''') AS CreateScript
FROM (SELECT *,
ROW_NUMBER() OVER (PARTITION BY SchemaName, ObjectName ORDER BY indexid, SecondaryName) AS Row
FROM ' + @DBName + '.Metadata.ObjectScripts' + ISNULL('
' + @WhereClause, '') + ') r'
EXEC(@SQL)
SELECT @SQL = 'EXEC xp_cmdshell ''mkdir ' + @ParentFolder + ''', no_output
EXEC xp_cmdshell ''mkdir ' + @ParentFolder + @InstanceName + ''', no_output
' + Util.dbo.StringConcat(DISTINCT 'EXEC xp_cmdshell ''mkdir ' + @ParentFolder + @InstanceName + '\' + CreateDefinition + ''', no_output', '
')
FROM #temp
EXEC (@SQL)
IF NOT EXISTS ( SELECT *
FROM [Util].[FS].[GetDirectoryInfo](@ParentFolder + @InstanceName, '*.*')
WHERE isdirectory = 1 )
BEGIN
RAISERROR ('Failed to write to output directory',16,1)
RETURN 40
END
SELECT @ObjectCount = COUNT(*)
FROM (SELECT [SchemaName],
[ObjectName],
CreateDefinition,
Util.dbo.StringConcat([CreateScript] + '
GO
', '') AS Contents
FROM #temp
GROUP BY [SchemaName],
[ObjectName],
CreateDefinition) k
CROSS APPLY Util.[FS].[AppendAllTextToFile](@ParentFolder + @InstanceName + '\' + CreateDefinition + '\' + [SchemaName] + '.' + [ObjectName] + '.sql', Contents,
1) b
GO
EXEC sys.sp_ms_marksystemobject
sp_ScriptDB
GO