You are here:Home » tsql » Generate Script files for all database objects in SQL Server

Generate Script files for all database objects in SQL Server

--Create a database

CREATE DATABASE [GenerateScript_Test]
go


EXEC SP_CONFIGURE 'Ole Automation Procedures',1
GO
RECONFIGURE
GO

USE [GenerateScript_Test]
GO

-- This is the procedure which is use to generate Script to generate script file for all objects of databases
-- and it will use in last final script
-- EXEC [proc_GenerateScriptFilesForAllObjects] 'Mac01' ,'MyDatabase','Tablename','TABLE','','C:\','sa','pwdsa'
CREATE PROCEDURE [dbo].[proc_GenerateScriptFilesForAllObjects] 
@ServerName varchar(30), 
@DBName varchar(30), 
@ObjectName varchar(8000), 
@ObjectType varchar(10), 
@TableName varchar(50), 
@ScriptFile varchar(255) ,
@LoginUser varchar(50),
@LoginPwd varchar(50)
AS 

BEGIN 
BEGIN TRY 
BEGIN TRANSACTION 
SET NOCOUNT ON 

DECLARE @CmdStr varchar(255) 
DECLARE @D DATETIME 
declare @context varchar(255) 
SET @D = GETDATE() 
SET @scriptfile=@scriptfile+@ObjectName+'_'+CONVERT (VARCHAR(10),@D,110)+'.SQL' 
PRINT @ScriptFile 
DECLARE @object int 
DECLARE @hr int 
declare @return varchar(200) 
SET NOCOUNT ON 
SET @CmdStr = 'Connect('+@ServerName+')' 
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT 

--Comment out for standard login 
--EXEC @hr = sp_OASetProperty @object, 'LoginSecure',TRUE 

-- Uncomment for Standard Login 
EXEC @hr = sp_OASetProperty @object, 'Login', @LoginUser 
EXEC @hr = sp_OASetProperty @object, 'password', @LoginPwd 


EXEC @hr = sp_OAMethod @object,@CmdStr 
SET @CmdStr = 
CASE @ObjectType 
WHEN 'Database' THEN 'Databases("' 
WHEN 'Procedure' THEN 'Databases("' + @DBName + '").StoredProcedures("' 
WHEN 'Functions' 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 'Job' THEN 'Jobserver.Jobs("' 
END 

SET @CmdStr = @CmdStr + @ObjectName + '").Script(5,"' + @ScriptFile + '")' 
PRINT @cmdstr 

EXEC @hr = sp_OAMethod @object, @CmdStr,@return OUTPUT 
EXEC @hr = sp_OADestroy @object 

COMMIT TRANSACTION 
END TRY 

BEGIN CATCH 
ROLLBACK TRANSACTION 

-------------------------------------
-- Get Error details in case of Rollback
-------------------------------------
SELECT 
ERROR_PROCEDURE() as ProcedureName ,
SYSTEM_USER as UserName, 
Error_Number() as ErrorNumber, 
Error_Severity() as ErrorSeverity, 
Error_State() as ErrorSate, 
Error_Message() as ErrorMessage,
GETDATE() as ErrorDate 

END CATCH 


END 

GO

-- Create one table
CREATE TABLE [dbo].[ScriptFileTable]
(
[Id] INT,
[Name] CHAR(10)
)

GO


INSERT INTO [dbo].[ScriptFileTable]
SELECT 1,'Test123'
GO

-- Create one Procedure
CREATE PROCEDURE [dbo].[ScriptFileProcedure]
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM SCRIPTFILETABLE
SET NOCOUNT OFF
END
GO


-- Create one Function
CREATE FUNCTION [dbo].[ScriptFileFunction] 
(
@id INT
)
RETURNS CHAR(10)
AS
BEGIN
DECLARE @name CHAR(10)
SELECT @name = [Name] FROM [dbo].[ScriptFileTable] where [Id] = @id
RETURN @name
END

GO

--------------------------------------------------------------
-- Script to generate script file for all objects of databases
-- Procedures, Tables, Functions
--------------------------------------------------------------
DECLARE @sql varchar(8000)
DECLARE @Object VARCHAR(4000)
DECLARE @ObjectType VARCHAR(50)
DECLARE c_Obj cursor FAST_FORWARD FOR 
SELECT 
[name],
CASE 
WHEN [type] = 'P' THEN 'Procedure' 
WHEN [type] = 'U' THEN 'Table' 
WHEN [type] = 'FN' THEN 'Functions' 
END
FROM SYS.OBJECTS WHERE [type] IN ('P','U','FN')

OPEN c_Obj 
FETCH NEXT FROM c_Obj INTO @Object,@ObjectType 
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 
'
EXEC [proc_GenerateScriptFilesForAllObjects] ''Mac01'' ,''GenerateScript_Test'','''+@Object+''','''+@ObjectType+''','''',''D:\SciptFiles\'',''sa'',''pwdsa'' 
'
PRINT (@SQL)
EXEC(@SQL)
FETCH NEXT FROM c_Obj INTO @Object,@ObjectType 
END 
CLOSE c_Obj
DEALLOCATE c_Obj

GO

-- After Running above script you can see the result for the generated file in Specified directory