Monday, April 29, 2013

Script All the Stored Procedures, Views, Triggers and Functions from The Database

Here I will show you how to generate script for database object programmatically or using query

In SQL Server 2005/2008 Management studio you can script all the database objectusing ‘SQL Server Script Wizards’. But sometime we do not have the permission to generate a script using wizard then we are help less. But don’t worry now we have a solution for that as well. I will show you how to script all database objects programmatically.

To script All the Stored Procedures from the Database:

DECLARE @ScriptText As VARCHAR(MAX) = ''
DECLARE @AllScriptText As VARCHAR(MAX) = ''

DECLARE script_sp CURSOR FOR 
SELECT SM.Definition
FROM SYS.SQL_Modules As SM  INNER JOIN SYS.Objects As Obj
ON SM.Object_ID = Obj.Object_ID  WHERE Obj.Type = 'P'
OPEN script_sp 

FETCH NEXT FROM script_sp INTO @ScriptText 

WHILE @@FETCH_STATUS = 0 
BEGIN 
        --Set @AllScriptText =  @AllScriptText + 'go' +@ScriptText;
        Print @ScriptText;
        FETCH NEXT FROM script_sp INTO @ScriptText
END 
--Select @AllScriptText;
CLOSE script_sp 
DEALLOCATE script_sp

But Remember if you have created all the procedure with encryption then you will get the null value.

 Now suppose you want all the views as a script then use below query,

DECLARE @ScriptText As VARCHAR(MAX) = ''
DECLARE @AllScriptText As VARCHAR(MAX) = ''

DECLARE script_sp CURSOR FOR 
SELECT SM.Definition
FROM SYS.SQL_Modules As SM  INNER JOIN SYS.Objects As Obj
ON SM.Object_ID = Obj.Object_ID  WHERE Obj.Type = 'V'
OPEN script_sp 

FETCH NEXT FROM script_sp INTO @ScriptText 

WHILE @@FETCH_STATUS = 0 
BEGIN 
        --Set @AllScriptText =  @AllScriptText + 'go' +@ScriptText;
        Print @ScriptText;
        FETCH NEXT FROM script_sp INTO @ScriptText
END 
--Select @AllScriptText;
CLOSE script_sp 
DEALLOCATE script_sp

Now suppose you want all the functions as a script then use below query,

DECLARE @ScriptText As VARCHAR(MAX) = ''
DECLARE @AllScriptText As VARCHAR(MAX) = ''

DECLARE script_sp CURSOR FOR 
SELECT SM.Definition
FROM SYS.SQL_Modules As SM  INNER JOIN SYS.Objects As Obj
ON SM.Object_ID = Obj.Object_ID  WHERE Obj.Type = 'FN'
OPEN script_sp 

FETCH NEXT FROM script_sp INTO @ScriptText 

WHILE @@FETCH_STATUS = 0 
BEGIN 
        --Set @AllScriptText =  @AllScriptText + 'go' +@ScriptText;
        Print @ScriptText;
        FETCH NEXT FROM script_sp INTO @ScriptText
END 
--Select @AllScriptText;
CLOSE script_sp 
DEALLOCATE script_sp

If you need to generate a script for all the triggers from the database then we have to make little bit changes in query. We will join sys.triggers table in place of sys.objects as below.

DECLARE @ScriptText As VARCHAR(MAX) = ''
DECLARE @AllScriptText As VARCHAR(MAX) = ''

DECLARE script_sp CURSOR FOR 
SELECT SM.Definition
FROM SYS.SQL_Modules As SM  INNER JOIN SYS.triggers As Obj
ON SM.Object_ID = Obj.Object_ID
OPEN script_sp 

FETCH NEXT FROM script_sp INTO @ScriptText 

WHILE @@FETCH_STATUS = 0 
BEGIN 
        --Set @AllScriptText =  @AllScriptText + 'go' +@ScriptText;
        Print @ScriptText;
        FETCH NEXT FROM script_sp INTO @ScriptText
END 
--Select @AllScriptText;
CLOSE script_sp 
DEALLOCATE script_sp

Happy Scripting…….

No comments:

Post a Comment