if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_export]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_export]
GO
select top 1 @fmtfile=rtrim(reverse(filename))
from master.dbo.sysfiles where name=N'master'
select @fmtfile=stuff(@fmtfile,1,charindex('\',@fmtfile),N'')
,@fmtfile=reverse(stuff(@fmtfile,1,charindex('\',@fmtfile),N''))
+N'\BACKUP\'+cast(newid() as nvarchar(36))+N'.fmt'
set @s=N'bcp "select null union all select 0 union all select 0 union all select null union all select null"'
+N' queryout "'+@fmtfile+N'" /T /c'
exec master..xp_cmdshell @s,no_output
set @s=N'
declare tb cursor local
for
select N''bcp "select ''+quotename(@imgfd)
+'' from ''+quotename(db_name())
+''..''+quotename(@tbname)
+'' where ''+quotename(@keyfd)
+''=''+rtrim(pub_id)
+''" queryout "''+@path+rtrim(pub_id)+'
+case when left(@file,1)='.' then quotename(@file,'''')
else N'ltrim('+quotename(@file)+N')' end+N'
+''" /T /i"''+@fmtfile+''"''
from '+quotename(@tbname)
+case when isnull(@whereand,'')='' then ''
else N' where '+@whereand end
+N'
open tb
fetch tb into @s
while @@fetch_status=0
begin
exec master..xp_cmdshell @s--,no_output
fetch tb into @s
end
close tb
deallocate tb'
exec sp_executesql @s,N'
@tbname sysname,
@keyfd sysname,
@imgfd sysname,
@path nvarchar(1000),
@file nvarchar(10),
@fmtfile nvarchar(1000),
@s nvarchar(4000)',
@tbname,@keyfd,@imgfd,@path,@file,@fmtfile,@s
set @s='del "'+@fmtfile+N'"'
exec master..xp_cmdshell @s,no_output
go