SQL Server BCP Utility with sp_HelpText to Generate Script File for Each Stored Procedure in a Database
SQL Server BCP utility is used to write sql query results to text files into a file folder.
If T-SQL developers use SQL BCP with sp_HelpText, it is possible to generate script files for SQL Server objects too.
The question "How to download all stored procedures create scripts from a database" was asked recently in the MSDN Transact-SQL forum where I'm one of the moderators.
The question was easy to answer with the use of Generate SQL Server Scripts Wizard.
But in the T-SQL tutorial, I'll share T-SQL codes that use T-SQL xp_cmdshell command to execute SQL Server BCP utility within a SQL Cursor (SQL loop).
Within the SQL BCP command, Transact-SQL developers are required to run the following stored procedure which selects create script text using sp_helptext and then returns generated script as table data.
CREATE PROCEDURE sp_GenerateScriptForStoredProcedure (
@storedprocedure_name sysname
)
AS
DECLARE @sphelptext TABLE(script NVARCHAR(MAX));
INSERT INTO @sphelptext EXEC sp_helptext @storedprocedure_name;
SELECT script FROM @sphelptext;
GO
Then on the target SQL Server database developers can execute the following SQL cursor code.
This transact-sql cursor will generate script for all stored procedures one by one and for each stored procedure will create separate text file including CREATE STORED PROCEDURE for each one.
The T-SQL cursor spCursor is formed of the stored procedures within the target SQL Server database. So the sql codes inside the sample cursor runs once for each stored procedure.
Inside cursor, a dynamic sql statement is formed.
The dynamic sql code creates a SQL Server BCP statement.
BCP command is formed of a valid sql statement as text, then "queryout" hint indicating that the result set of the execution of the sql statement will be handled by the BCP command.
Also we specify the target file name and the path of the file that will be generated by using the SQL BCP command.
I choose the create sql create script files with ".sql" extension and set the file name same as the stored procedure itself.
The remaining parameters are for SQL Server and authentication methods, etc.
Programmers and database administrators can refer to SQL Server Books Online entry for SQL BCP Utility from here
DECLARE @storedprocedure_name varchar(100)
DECLARE @cmd VARCHAR(4000)
DECLARE spCursor CURSOR FAST_FORWARD FOR SELECT name FROM sys.procedures
OPEN spCursor
FETCH NEXT FROM spCursor INTO @storedprocedure_name
WHILE @@fetch_status = 0
BEGIN
SET @cmd = 'bcp "exec [' + DB_NAME() + '].dbo.sp_GenerateScriptForStoredProcedure ' +
@storedprocedure_name + '" queryout "c:\' + @storedprocedure_name + '.sql" -c -UTF8 -T -Slocalhost'
-- PRINT @cmd
EXEC master..xp_cmdshell @cmd
FETCH NEXT FROM spCursor INTO @storedprocedure_name
END
CLOSE spCursor
DEALLOCATE spCursor
The last functional sql code within the stored procedure list cursor is executing xp_cmdshell extended stored prodecure in order to reach file system on the server.
For more detail and sample on how to create SQL Cursor syntax please refer to related t-sql tutorial.
If you execute the xp_cmdshell first time on the SQL Server instance, you may get the following SQL Server error message.
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
As the SQL Server error message indicates this is because the xp_cmdshell is not enabled by default on SQL Server installations. So you should enable xp_cmdshell using sp_configure on SQL Server instance
And when I execute the above SQL cursor to generate create script for all stored procedures in the sample AdventureWorks2008 database, I got the following list of .sql files generated with SQL Server BCP command and sp_helptext procedure by using xp_cmdshell.
Database administrator and developers can also check following SQL Server article and guides for related SQL Server tutorial to generate database object scripts:
1) How to Script MS SQL Server 2008 Database using SQL Server Script Wizard
2) Script Data in MS SQL Server 2008 Database Tables using Generate SQL Server Script Wizard
3) How to Script Data in MS SQL Server 2012 using Generate and Publish Scripts Wizard
4) SQL Server BCP Utility with sp_HelpText to Generate Script File for Each Stored Procedure in a Database
5) Create SQL Server Database Object Script using Visual Studio 2015