list store procedures contents

• elkinfortiz

  Mr or Mrs. 500

  Points: 533

  More actions

  July 27, 2015 at 9:01 pm

  #319269

  Hi, who i can to list the content of all store procedures and funtions in text file ?

  Thanks flor your help

  Elkin

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  July 28, 2015 at 7:46 am

  #1815416

  The actual text of stored proc can be found in sys.sql_modules.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 28, 2015 at 7:58 am

  #1815424

  SQL does not have anything native to send anything to file.

  something external like bcp or sqlcmd, a Common Language Runtime Procedure, Powershell or a proper programming language have the ability to do what you are asking.

  I've seen some nice scripts posted in powershell forum, if you are comfortable with that.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Justin Manning SA

  Hall of Fame

  Points: 3190

  More actions

  July 28, 2015 at 9:39 am

  #1815469

  Similar to what I posted yesterday:

  -- ENABLE XP_CMDSHELL

  SP_CONFIGURE 'XP_CMDSHELL', 1

  RECONFIGURE WITH OVERRIDE

  GO

  SET NOCOUNT ON;

  DECLARE

  @FILENAME VARCHAR(200)

  , @BCPCOMMAND VARCHAR(2000)

  , @SERVER_NAME SYSNAME = @@SERVERNAME

  , @DBNAME SYSNAME = DB_NAME()

  , @CNT_START INT

  , @CNT_END INT

  , @OBJ_NAME VARCHAR(100)

  , @DEFINITION NVARCHAR(MAX)

  SET @CNT_START = 1

  SET @CNT_END = (SELECT COUNT(*)

  FROM SYS.SQL_MODULES A

  INNER JOIN SYS.OBJECTS B ON A.OBJECT_ID = B.OBJECT_ID)

  CREATE TABLE #TMP_OBJECT(

  ID INT IDENTITY(1,1)

  , OBJNAME VARCHAR(100)

  , DEFINITION NVARCHAR(MAX)

  )

  INSERT INTO #TMP_OBJECT

  SELECT B.NAME, A.DEFINITION

  FROM SYS.SQL_MODULES A

  INNER JOIN SYS.OBJECTS B ON A.OBJECT_ID = B.OBJECT_ID

  WHILE (@CNT_START) <= @CNT_END

  BEGIN

  -- QUERY FOR OBJECT DEFINITION

  SELECT DEFINITION

  INTO TEMP

  FROM #TMP_OBJECT

  WHERE ID = @CNT_START

  -- SET OBJ_NAME

  SET @OBJ_NAME = (SELECT OBJNAME FROM #TMP_OBJECT WHERE ID = @CNT_START)

  -- GET OBJECT TEXT

  SET @DEFINITION = (SELECT DEFINITION FROM #TMP_OBJECT WHERE ID = @CNT_START)

  -- FILE PATH WHERE YOUR SQL SCRIPT WILL BE STORED

  SET @FILENAME ='C:\XXX\SQL_OBJECTS_['+@SERVER_NAME+']_['+@DBNAME+']_'+@OBJ_NAME+''+CONVERT(VARCHAR, GETDATE(), 112)+'.SQL'

  -- BUILD BCP COMMAND

  SET @BCPCOMMAND = 'BCP "SELECT * FROM '+@DBNAME+'..TEMP" QUERYOUT "'

  SET @BCPCOMMAND = @BCPCOMMAND + @FILENAME + '" -c -T'

  -- EXECUTE BCP COMMAND

  EXEC MASTER..XP_CMDSHELL @BCPCOMMAND

  SET @CNT_START = @CNT_START + 1

  DROP TABLE TEMP

  END

  -- CLEAN UP

  DROP TABLE #TMP_OBJECT

  GO

  -- DISABLE XP_CMDSHELL

  SP_CONFIGURE 'XP_CMDSHELL', 0

  RECONFIGURE WITH OVERRIDE

  GO