Dec 17, 2011

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


In my previous post I have show you how to generate database script using Wizard. In this article 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 object using ‘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…….



4 comments:

Cebu City Jobs

Thanks, I learn a lot of your site.

Cebu City Jobs

Hi Mr. Tarun,

Do you a have a sample stored procedure that automatically create a backup for SQL Server?

Regards,

John

Anonymous

Thanks for any other magnificent article. Where
else may anybody get that type of info in such an ideal way of writing?

I have a presentation subsequent week, and
I am at the search for such information.
Here is my web site www.kfzversicherungsvergleich1.org

Anonymous

Gr8..its really useful..

Post a Comment

Author Profile

Total Pageviews

Categories

Followers

 

Sponsors