if (SELECT OBJECT_ID('tempdb..##t')) IS NOT NULL DROP TABLE ##t if (SELECT OBJECT_ID('tempdb..#p')) IS NOT NULL DROP TABLE #p declare @files int = 0 ,@path varchar(100) = '\\SRV-DBY-EHFSSQL\E$\' create table ##t ( id int identity , sch varchar(100) , n varchar(100) , t varchar(max) ) create table #p ( id int identity , sch varchar(100) , n varchar(100) , type varchar(100) ) truncate table ##t truncate table #p --drop table ##t --drop table #p --select * from sys.objects where type not in ( 'D ', 'PK', 'S ', 'U') and name = 'EventNotificationErrorsQueue' -- insert names insert into #p (sch, n, type) select S.name, O.name, O.type_desc from sys.objects O inner join sys.schemas S on O.schema_id = S.schema_id where type not in ( 'D', 'PK', 'S ', 'U', 'IT', 'FS','AF','UQ','SQ') --and -- (O.name not like 'SqlQuery%' -- and -- O.name not like '%AspNet%') -- insert text declare @i int, @total int, @n varchar(100) select @i = 1, @total = count(*) from #p while @i <= @total begin select @n = sch + '.' + n from #p where id = @i insert into ##t (t) exec sp_helptext @n update ##t set n = @n where n is null select @i = @i + 1 end -- create Declare @AdvanceValue int Declare @Config_Value_xp_cmd int SET @AdvanceValue=0 SET @Config_Value_xp_cmd=0 SET @AdvanceValue=0 SELECT @AdvanceValue=CONVERT(INT, ISNULL(value, value_in_use)) FROM sys.configurations WHERE name = 'show advanced options' ; IF @AdvanceValue=0 BEGIN EXEC sp_configure 'show advanced options', 1 RECONFIGURE END SELECT @Config_Value_xp_cmd=CONVERT(INT, ISNULL(value, value_in_use)) FROM sys.configurations WHERE name = 'xp_cmdshell' ; IF @Config_Value_xp_cmd=0 BEGIN EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE END -- unload declare @sql varchar(8000), @tmp varchar(1000), @file_name varchar(1000) declare @i2 int, @total2 int, @n2 varchar(100) select @i2 = 1, @total2 = count(*) from #p set nocount off if @files = 1 while @i2 <= @total2 begin select @n2 = sch + '.' + n from #p where id = @i2 select @tmp = 'select t from ##t where n = ''' + @n2 + '''' --replace(replace(t,char(13),''''),char(10),'''') select @file_name = replace(@path,'\','') + @n2 + '.sql' select @sql = 'BCP "' + @tmp + '" queryout "'+ @file_name + '" -S' + @@SERVERNAME + ' -T -t; -r \n -C RAW -c' exec master.dbo.xp_cmdshell @sql select @i2 = @i2 + 1 end else begin select @tmp = 'select t from ##t' -- \\SRV-DBY-EHFSSQL\E$\ select @file_name = replace(@path,'\','') + '_ALLSQLCode.sql' select @sql = 'BCP "' + @tmp + '" queryout "'+ @file_name + '" -S' + @@SERVERNAME + ' -T -t; -r \n -C RAW -c' exec master.dbo.xp_cmdshell @sql end