Write Text File

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_WriteTextFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_WriteTextFile]
GO

/*

Author:amcleod
Adapted from script by Rodrigo Acosta 

Date:4.6.04

Accepts any table name (may include a WHERE clause), any output file path/name,  and an optional field list (may include udfs,cast/converts,etc...).

*/
Create Procedure usp_WriteTextFile @varTableName varchar(100),@varOutFile varchar(100),@varFieldList varchar(100) = '*'
As

SET NOCOUNT ON

DECLARE @bcp AS VARCHAR(8000)
DECLARE @Status AS INT


SELECT @bcp = 'bcp "SELECT ' + @varFieldList + ' FROM ' + @varTableName + '" QUERYOUT "' + @varOutFile + '" -T -c'
EXEC @Status = master.dbo.xp_cmdshell @bcp, no_output

IF @Status <> 0
BEGIN
PRINT 'An error ocurred while generating the file ' + @varOutFile + '.'
RETURN(-1)
END 
ELSE
Begin
PRINT @varOutFile + ' file generated succesfully.'
End

GO

/*

--  To use with Rodrigo Acosta's script to Export text of 
--  SPs, place this after the cursor is deallocated:
Declare @varTableName varchar(100),@varOutFile varchar(100)

Select @varTableName = DB_NAME() + '.dbo.##SPs', @varOutFile  = 'C:\StoredProcs_' + @@SERVERNAME + '.txt'

Exec usp_WriteTextFile @varTableName = @varTableName,@varOutFile  = @varOutFile,@varFieldList = '*'

*/