EXPORT

CREATE PROCEDURE ExportData_In_TextFile_UsingSQLCMD
@TableName Varchar(255), --To Get Table Name
@FilePath VARCHAR(1000), --To Get Valid FilePath
@Separator Varchar(1), --To Get Field Separator
@Result INT = 0 OUTPUT, --To get error no if any error ocurred during execution
@ErrDesc VARCHAR(500) OUTPUT --To get error description if any error ocurred during execution

AS

DECLARE
@SQLCmd Varchar(8000),
@Columns Varchar(4000),
@DBName Varchar(255)

SET NOCOUNT ON

BEGIN TRY

IF @TableName='' OR @TableName IS NULL
Begin
SET @Result = -1
SET @ErrDesc = 'Table Name can not be null'
RETURN
End

IF @FilePath='' OR @FilePath IS NULL
Begin
SET @Result = -1
SET @ErrDesc = 'File Path can not be null'
RETURN
End

IF @Separator IS Null
SET @Separator=','

Set @DBName=DB_NAME()

--To collect the columns information from the schema view
SELECT @Columns= COALESCE(@Columns + ',', '') + '''' + Column_Name + ''''
From Information_Schema.columns Where Table_Name=@TableName

--Export the data in text file
SET @SQLCmd ='sqlcmd -d ' + @DBName + ' -Q "set nocount on;Select ' + @Columns + ';Select * From ' + @TableName + '" -o "' + @FilePath + '" -W -s "' + @Separator + '" -h -1'
EXEC master..xp_cmdshell @SQLCmd


END TRY
BEGIN CATCH
SET @Result = Error_Number()
SET @ErrDesc = Error_Message()
RETURN
END CATCH

SET NOCOUNT OFF
SET @Result = 0

RETURN 0