IF OBJECT_ID (N'dbo.Enable_xp_cmdshell') IS NOT NULL
	DROP PROCEDURE dbo.Enable_xp_cmdshell
GO
CREATE PROCEDURE dbo.Enable_xp_cmdshell
	@value int
AS
BEGIN
	DECLARE @show_advanced_options_enabled int

	SELECT @show_advanced_options_enabled = CAST(value as int)
	FROM sys.configurations 
	WHERE name = 'show advanced options';
	
	EXEC sp_configure 'show advanced options', 1
	RECONFIGURE

	EXEC sp_configure 'xp_cmdshell', @value
	RECONFIGURE

	EXEC sp_configure 'show advanced options', @show_advanced_options_enabled
	RECONFIGURE
END
GO

IF OBJECT_ID (N'dbo.SaveTextToFile') IS NOT NULL
	DROP PROCEDURE dbo.SaveTextToFile
GO
CREATE PROCEDURE dbo.SaveTextToFile
	-- Based on http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/
	@Text nvarchar(MAX),
	@Filename varchar(255),
	@Unicode int=0
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @TemporaryTable varchar(255)
	DECLARE @Command nvarchar(4000)
	DECLARE @Result int

	-- First we create a global temp table with a unique name
	SELECT  @TemporaryTable = '##temp' + CONVERT(varchar(12), CONVERT(int, RAND() * 1000000))

	-- Then we create it using dynamic SQL, and insert a single row
	-- in it with the MAX varchar stocked with the string we want
	SELECT  @Command = 'CREATE TABLE ['
		+ @TemporaryTable
		+ '] (MyID int identity(1,1), BulkCol varchar(MAX)) INSERT INTO ['
		+ @TemporaryTable
		+ '](BulkCol) SELECT @Text'
	EXECUTE sp_executesql @Command, N'@Text varchar(MAX)',	@Text

	-- Then we execute the BCP to save the file
	SELECT  @Command = 'bcp "SELECT BulkCol FROM ['
		  + @TemporaryTable + ']'
		  + '" queryout '
		  + @Filename + ' '
		 + CASE WHEN @Unicode=0 THEN '-c' ELSE '-w' END
		  + ' -T -S localhost'
	EXECUTE @Result= master..xp_cmdshell @Command, no_output
	EXECUTE ('DROP TABLE ' + @TemporaryTable)

	RETURN @Result
END
GO

IF OBJECT_ID (N'dbo.CreateOutputFolders') IS NOT NULL
	DROP PROCEDURE dbo.CreateOutputFolders
GO
CREATE PROCEDURE dbo.CreateOutputFolders
	@OutputFolder varchar(255)
AS
BEGIN
	DECLARE @CreateFolderCommand varchar(255)
	DECLARE @ObjectTypeDescription nvarchar(60)

	DECLARE objectTypesCursor cursor
	FOR
		SELECT DISTINCT o.type_desc
		FROM sys.all_objects o
		INNER JOIN sys.sql_modules AS smsp ON smsp.object_id = o.object_id
		WHERE
			SCHEMA_NAME(o.schema_id) <> N'sys'
			AND smsp.definition IS NOT NULL

	SET @CreateFolderCommand = 'mkdir ' + @OutputFolder
	PRINT 'Creating folder ' + @OutputFolder 
	EXECUTE master..xp_cmdshell @CreateFolderCommand, no_output

	OPEN objectTypesCursor;
	FETCH NEXT FROM objectTypesCursor INTO @ObjectTypeDescription;
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		SET @CreateFolderCommand = 'mkdir ' + @OutputFolder + @ObjectTypeDescription + '\'
		PRINT 'Creating folder ' + @OutputFolder + @ObjectTypeDescription + '\'
		EXECUTE master..xp_cmdshell @CreateFolderCommand, no_output
		FETCH NEXT FROM objectTypesCursor INTO @ObjectTypeDescription;
	END;
	CLOSE objectTypesCursor;
	DEALLOCATE objectTypesCursor;	
END
GO

IF OBJECT_ID (N'dbo.ExportDatabaseObjectsToFiles') IS NOT NULL
	DROP PROCEDURE dbo.ExportDatabaseObjectsToFiles
GO
CREATE PROCEDURE dbo.ExportDatabaseObjectsToFiles
AS
BEGIN
	DECLARE @ObjectName varchar(100)
	DECLARE @ObjectTypeDescription nvarchar(60)
	DECLARE @Definition nvarchar(max)

	DECLARE @NumberOfFilesWritten int
	DECLARE @OutputFolder varchar(255)
	DECLARE @FileName varchar(255)
	DECLARE @Message varchar(500)

	DECLARE @xp_cmdshell_enabled int
	SELECT @xp_cmdshell_enabled = CAST(value AS int) FROM sys.configurations WHERE name = 'xp_cmdshell';
	-- Enable xp_cmdshell
	EXEC Enable_xp_cmdshell 1

	-- Create the folders we need
	SET @OutputFolder = 'C:\Objects\'
	EXEC dbo.CreateOutputFolders @OutputFolder

	DECLARE objectsCursor cursor
	FOR
		SELECT
			o.name AS Name,
			o.type_desc,
			smsp.Definition
		FROM
			sys.all_objects AS o
			-- We use the sys.sql_modules catalog because it's up to date
			-- http://blog.beyondrelational.com/2008/09/be-careful-when-renaming.html		
			INNER JOIN sys.sql_modules AS smsp ON smsp.object_id = o.object_id
		WHERE
			SCHEMA_NAME(o.schema_id) <> N'sys'
			AND smsp.definition IS NOT NULL

	SET @NumberOfFilesWritten = 0
	OPEN objectsCursor;
	FETCH NEXT FROM objectsCursor INTO @ObjectName, @ObjectTypeDescription, @Definition;
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		SET @FileName = @OutputFolder + @ObjectTypeDescription + '\' + @ObjectName + '.sql'
		EXEC dbo.SaveTextToFile @Definition, @FileName
		SET @NumberOfFilesWritten = @NumberOfFilesWritten + 1
		PRINT 'Writing ' + @FileName
		FETCH NEXT FROM objectsCursor INTO @ObjectName, @ObjectTypeDescription, @Definition;
	END;
	CLOSE objectsCursor;
	DEALLOCATE objectsCursor;

	-- Restore the xp_cmdshell setting
	EXEC Enable_xp_cmdshell @xp_cmdshell_enabled

	SELECT 'Written ' + CAST(@NumberOfFilesWritten AS varchar) + ' files.'
END
GO

EXEC dbo.ExportDatabaseObjectsToFiles
GO

DROP PROCEDURE dbo.Enable_xp_cmdshell
GO
DROP PROCEDURE dbo.SaveTextToFile
GO
DROP PROCEDURE dbo.CreateOutputFolders
GO
DROP PROCEDURE dbo.ExportDatabaseObjectsToFiles
GO