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…….
