none
returning all procedures

Ответы

  • Mina,

    As suggest by other forum members you can go along with sys.all_sql_modules, sys.sql_modules and sp_helptext. The following is a simple script to execute sp_helptext dynamically through out all your stored procedures to get the definitions of stored procedures.

    You can try as follows

    --DECLARING A TABLE VARIABLE
    DECLARE @SPNAME TABLE (ID INT IDENTITY(1,1),SP CHAR(200))
    --INSERTING ALL STORED PROCEUDRE NAMES IN TABLE VARIABLE
    INSERT INTO @SPNAME   SELECT NAME FROM SYS.PROCEDURES WHERE IS_MS_SHIPPED=0
    --DECLARING VARIABLES
    DECLARE @SQLTEXT NVARCHAR(200)
    DECLARE @I INT, @J INT
    SET @J=1
    SELECT @I=COUNT(NAME) FROM SYS.PROCEDURES WHERE IS_MS_SHIPPED=0
    WHILE @J<=@I
    BEGIN
    	SELECT @SQLTEXT='SP_HELPTEXT ' + SP FROM @SPNAME WHERE ID=@I
    	EXEC(@SQLTEXT)
    	SET @J=@J+1
    END


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Помечено в качестве ответа Iric WenModerator 18 июня 2012 г. 1:34
    11 июня 2012 г. 4:05
  • Take a look at this blog post

    How to script all stored procedures in a database


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Помечено в качестве ответа Iric WenModerator 18 июня 2012 г. 1:34
    14 июня 2012 г. 3:38
    Модератор
  • Hi mina,

    We can use sys.sql_modules, OBJECT_DEFINITION or sp_helptext to view definition of a stored procedure.

    For more information, please see:

    How to: View the Definition of a Stored Procedure (SQL Server Management Studio)


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Помечено в качестве ответа Iric WenModerator 18 июня 2012 г. 1:34
    11 июня 2012 г. 3:38
    Модератор

Все ответы