Quick way to backup SQL SP and Functions?

Question

I have a long list of SPs (stored procedure) and Functions in my SQL server db. I could save them one by one by right clicking and script XXX to Alter To. Is there any way in TSQL to query all SPs and functions save them to xxx.sql files?

For example, for sp_mySP1, I would like to save it to sp_mySP1.sql which is a text file. The database is too big and I would like save only SPs and functions as a backup of source codes.

Answer 1

In management studio; find the database, right-click, tasks, generate scripts;

next-next-next until you "Choose Object Types". Select "Stored procedures" and "User-defined functions", next, Select All; choose an output; go!

Answer 2

you could query syscomments to get your sql object creation text, but I don't know how to save them all in separate files using just TSQL.

select * from syscomments

Answer 3

1) Right-click on your Database name in the Object Explorer

2) Select "Tasks > Generate Scripts..." from the Context menu

3) Select your Database in the list and click Next

4) Click Next on the Chose Script Options

5) In Object Types, check Stored Procedures and User-defined functions, click Next

6) Click Select All on the Stored Procedures selection screen, click Next

7) Click Select All on the Functions selection screen, click Next

8) Select 'Script to New Query Window' and click Finish

Answer 4

Here's a proc that will export SOME types of data.

if exists ( select * from sysobjects where name = 'ExportData_P' )
    drop proc ExportData_P
go

CREATE PROC dbo.ExportData_P ( 
    @tableName varchar(500),
    @where varchar(5000) = '(1=1)'
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @sql varchar(8000)
    DECLARE @fieldList varchar(8000)
    DECLARE @valueList varchar(8000)
    SELECT @fieldList = '', @valueList = ''

    DECLARE @cols TABLE ( column_name nvarchar(250), data_type varchar(250) )
    DECLARE @c nvarchar(250), @data_type varchar(250)

    INSERT INTO @cols 
    select column_name, data_type
    from information_Schema.columns 
    where table_name = @tableName


    WHILE EXISTS ( SELECT TOP 1 * FROM @cols )
    BEGIN
     SELECT TOP 1 @c = column_name, @data_type = data_type FROM @cols


     SELECT 
     @fieldList = @fieldList + @c + ', ',
     @valueList = @valueList + CHAR(13) + 'case when ' + @c + ' is null then ''NULL'' else '''''''' + ' +
      case when @data_type in ('text','ntext','char', 'nvarchar', 'varchar' ) then
       ' REPLACE ( REPLACE ( REPLACE ( '
       else ''
      end +
      'IsNull ( convert(varchar' + 
      ( -- change this section to pass the length of varchar to convert
       case when @data_type in ( 'uniqueidentifier' ) then '(50)'
        when @data_type in ( 'text', 'ntext' ) then '(8000)'
       else '' end
      ) +
      ', ' +
      @c + 
      '), '''' )' + -- end is null
      case when @data_type in ('text','ntext','char', 'nvarchar', 'varchar' ) then
       ', CHAR(39), CHAR(39)+CHAR(39) ), CHAR(13), '''' + CHAR(13) + ''''), CHAR(9), '''' + CHAR(9) + '''') '
       else ''
      end +
      ' + '''''''' end + '', '' + '

     DELETE FROM @cols WHERE column_name = @c
    END

    SELECT @fieldList = LEFT ( @fieldList, LEN(@fieldList)-1 ),
     @valueList = LEFT ( @valueList, LEN(@valueList)-1 )

    SELECT @sql = 'select ''insert into ' + @tableName + ' (' + @fieldList + ') ' +
     ' VALUES ( ''+ ' + left ( @valueList, len(@valueList)-5) + ''') '' from ' + @tableName +
     ' WHERE ' + @where

    -- into [#mcoe_temp_export' + @tableName + '] 
    print @sql
    EXEC ( @sql )
    --EXEC ( 'select * from [#mcoe_temp_export' + @tableName + ']' )  

    SET NOCOUNT OFF
END

go

Use like:

exec ExportData_P 'tablename'