backup the stored procedure of a database in sql server 2000.

Question

what is the query to backup the stored procedure of a database in sql server 2000.

Answer 1

In SQL Server 2000, you can use this query to list out the complete text of stored procedures, they can span multiple rows.

SELECT
    o.name,o.id,o.xtype, c.colid, c.text
    FROM dbo.sysobjects            o
        INNER JOIN dbo.syscomments c ON o.id = c.id
    WHERE o.xtype = 'p'
    ORDER BY o.Name,c.colid

I would be easier to use Enterprise Manager to script all the procedures though. In Enterprise Manager, right click on the database to you want to capture all the procedures from. An options list will pop-up, select "All Tasks" then "Generate SQL Script...". A dialogue box will appear, click on "show all", you can then refine the list of objects to script, by using the check boxes. Select the objects on the left side and click on the "Add>>" to move them to the script list. You can set formatting and other options, then click OK when done.

In SQl Server 2005+ you can use this query to list the complete text of all stored procedures, views and functions:

SELECT 
    LEFT(o.name, 100) AS Object_Name,o.type_desc,m.definition
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id

you can take this output and save it if you like.

However, it is easier to use SQL Server management Studio to script out all procedures.