USE River
GO

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CreateSqlObjectScript]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[CreateSqlObjectScript]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CreateSqlScripts]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[CreateSqlScripts]
GO

--
-- Connects to the provided server and generates the sql for the provided object to the script file specified.
--
CREATE PROCEDURE [dbo].CreateSqlObjectScript
	@ServerName		VARCHAR(30), 
	@DBName			VARCHAR(30), 
	@ObjectName		VARCHAR(50), 
	@ObjectType		VARCHAR(10), 
	@TableName		VARCHAR(50),
	@ScriptFile		VARCHAR(255),
	@ScriptingOptions	INT
AS

DECLARE @CmdStr VARCHAR(255)
DECLARE @object INT
DECLARE @hr	INT

SET NOCOUNT ON


SET @CmdStr = 'Connect('+@ServerName+')'
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE

EXEC @hr = sp_OAMethod @object,@CmdStr
SET @CmdStr = 
  CASE @ObjectType
    WHEN 'Database' 	THEN 'Databases("' 
    WHEN 'Procedure'	THEN 'Databases("' + @DBName + '").StoredProcedures("'
    WHEN 'UDF'		THEN 'Databases("' + @DBName + '").UserDefinedFunctions("'
    WHEN 'View' 	THEN 'Databases("' + @DBName + '").Views("'
    WHEN 'Table'	THEN 'Databases("' + @DBName + '").Tables("'
    WHEN 'Index'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Indexes("'
    WHEN 'Trigger'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Triggers("'
    WHEN 'Key'		THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Keys("'
    WHEN 'Check'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Checks("'
    WHEN 'Default'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Defaults("'
    WHEN 'Job'		THEN 'Jobserver.Jobs("'
  END





SET @CmdStr = @CmdStr + @ObjectName + '").Script(' + CAST (@ScriptingOptions AS VARCHAR) + ',"' + @ScriptFile + '")'
	PRINT @CmdStr
EXEC @hr = sp_OAMethod @object, @CmdStr
EXEC @hr = sp_OADestroy @object
GO

--
-- Scripts the database objects into a specified directory structure with custom sql script options.
--
CREATE PROCEDURE [dbo].[CreateSqlScripts] 
(
	@BaseDirectory 		NVARCHAR(2000),
	@Server 		NVARCHAR(200),
	@DB 			NVARCHAR(50),
	@Type 			VARCHAR(10) = NULL,
	@ScriptOptions		INT
)
AS
DECLARE @Object_Cursor CURSOR   

DECLARE @ObjectName			NVARCHAR(200) 
DECLARE @ObjectType			CHAR(2) 
DECLARE @ParentObjectId			INT
DECLARE @ParentObjectName		NVARCHAR(200) 

DECLARE @File				NVARCHAR(2000)
DECLARE @Directory			NVARCHAR(200)
DECLARE @ScriptType			NVARCHAR(50)
DECLARE @FolderExist			INT
DECLARE @query				VARCHAR(100)

DROP TABLE #ScriptDirectories
CREATE TABLE #ScriptDirectories (Files int, Folder int, parent int)

SET @query = 'RMDIR /Q /S "' + @BaseDirectory + '"'
EXEC master..xp_cmdshell @query

SET ROWCOUNT 0

-- Create a table of sql object types to directories.
DECLARE @Mappings TABLE
(
    Type 	CHAR(2),
    ScriptType 	VARCHAR(100),
    Directory 	VARCHAR(2000)
)

INSERT INTO @Mappings VALUES ('P', 'Procedure', 'Stored Procedures');
INSERT INTO @Mappings VALUES ('TF', 'UDF', 'User Defined Functions');
INSERT INTO @Mappings VALUES ('FN', 'UDF', 'User Defined Functions');
INSERT INTO @Mappings VALUES ('IF', 'UDF', 'User Defined Functions');
INSERT INTO @Mappings VALUES ('U', 'Table', 'Tables');
INSERT INTO @Mappings VALUES ('V', 'View', 'Views');
INSERT INTO @Mappings VALUES ('TR', 'Trigger', 'Triggers');
INSERT INTO @Mappings VALUES ('D', 'Default', 'Defaults');
INSERT INTO @Mappings VALUES ('PK', 'Key', 'Primary Keys');
INSERT INTO @Mappings VALUES ('F', 'Key', 'Foreign Keys');
INSERT INTO @Mappings VALUES ('I', 'Index', 'Indexes');


-- Create a table of sql objects in the system.
DECLARE @SqlObjects TABLE
(
    [Name] 	VARCHAR(500),
    Type 	CHAR(2),
    Parent 	INT
)

INSERT INTO @SqlObjects 
	SELECT SysObjects.[Name], XType, Parent_obj
	FROM SysObjects
	WHERE XType = @Type OR (@Type IS NULL)

INSERT INTO @SqlObjects
	SELECT SysIndexes.[Name] AS ObjectName, 'I', [id]
	FROM SysIndexes 
	WHERE indid > 1 -- no clustered indexes (generally the pk)
	AND @Type = 'I' OR @Type IS NULL

-- Create the object cursor to loop through the sql objects
SET @Object_Cursor = CURSOR FAST_FORWARD 
FOR 
	SELECT [Name], Type, Parent 
	FROM @SqlObjects
	ORDER BY Type ASC
   
OPEN @Object_Cursor FETCH NEXT FROM @Object_Cursor INTO @ObjectName, @ObjectType, @ParentObjectId

WHILE @@FETCH_STATUS = 0 
BEGIN
       
	SELECT @Directory = @BaseDirectory + Directory, @ScriptType = ScriptType FROM @Mappings WHERE type = @ObjectType	
		PRINT @Directory
	INSERT #ScriptDirectories EXEC master.dbo.xp_fileexist @Directory
	SELECT @FolderExist = Folder FROM #ScriptDirectories

	IF @FolderExist <> 1 
	BEGIN
		SET @query = 'MKDIR "' + @Directory + '"'
		EXEC master..xp_cmdshell @query
	END

	IF (@ObjectType = 'I') 
		SELECT @ParentObjectName = Name from SysObjects where id = @ParentObjectId
	ELSE
		SELECT @ParentObjectName = Name from SysObjects where object_id(name) = @ParentObjectId

	BEGIN
		SET @File =  @Directory + '\' + @ObjectName + '.sql'
			EXEC CreateSqlObjectScript @ServerName = @Server, 
				@DBName = @DB, 
				@ObjectName = @ObjectName, 
				@ObjectType = @ScriptType, 
				@TableName = @ParentObjectName,
				@ScriptFile = @File,
				@ScriptingOptions = @ScriptOptions

	END

	FETCH NEXT FROM @Object_Cursor 
	INTO @ObjectName, @ObjectType, @ParentObjectId
END

CLOSE @Object_Cursor 
DEALLOCATE @Object_Cursor
GO


-- 
-- Example Usage:

-- The scripting options are specified by bitwise and of various options. 
-- Check out http://fox.wikis.com/wc.dll?Wiki~SQLDMOScript for more info.

-- Ones Used Here:
-- 1 - default
-- 4 - drops
-- 73736 -> Include Indexes
-- 128 -> include bindings
--

EXEC dbo.CreateSqlScripts 'c:\temp\sql\', 'PC70027662\SQLLOCALMACHINE', 'RIVER', NULL, 73879