La idea del código es crear un backup, usando BCP de SQL,
en mi caso estoy usando Ms. SQL SERVER 2008 R2.
CREATE PROCEDURE [dbo].[SP_TB_BACKUP]
@FILTRO VARCHAR(20),
@RUTA VARCHAR(200),
@EXT VARCHAR(3)
AS
BEGIN
--1.- CREACION DE LA TABLA
CREATE TABLE #TABLE_TEMP (ITEM INT IDENTITY(1,1), NOMBRE_TB NVARCHAR(100), NOMBRE NVARCHAR(100), ONLYNAME NVARCHAR(100), ONLYSCHEMA NVARCHAR(100))
--2.- INSERTANDO EN LA TABLA
INSERT INTO #TABLE_TEMP (NOMBRE_TB,NOMBRE,ONLYNAME,ONLYSCHEMA)
SELECT ('['+table_catalog+'].['+table_schema+'].['+table_name+']') AS TABLENAME1,(table_schema+'.'+table_name) AS TABLENAME2, table_name AS ONLYNAME, TABLE_SCHEMA as ONLYSCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name like '%'+@FILTRO+'%'
ORDER BY table_name
--3.- BUBLE TABLAS
DECLARE @columnHeader VARCHAR(8000), @Union VARCHAR(8000),@name_tb VARCHAR(200),@name_tb_cod VARCHAR(200);
DECLARE @columnHeader2 VARCHAR(8000)
DECLARE @FileName VARCHAR(250), @bcpCommand VARCHAR(8000), @fecha VARCHAR(10);
DECLARE @TB_ONLY_SCHEMA VARCHAR(100), @TB_ONLY_NAME VARCHAR (100);
DECLARE @i INT, @f INT;
SET @fecha= (SELECT(CONVERT(char(10),GETDATE(),103)));
SET @i=1;
SET @f=(SELECT COUNT(*) FROM #TABLE_TEMP);
WHILE (@i<=@f)
BEGIN
SET @name_tb = (SELECT NOMBRE FROM #TABLE_TEMP WHERE ITEM=@i)
SET @name_tb_cod = (SELECT NOMBRE_TB FROM #TABLE_TEMP WHERE ITEM=@i)
SET @FileName = REPLACE(@RUTA+'\jrbkp_'+@fecha+'-'+@name_tb+'.'+@EXT+'','/','-')
SET @TB_ONLY_NAME = (SELECT ONLYNAME FROM #TABLE_TEMP WHERE ITEM=@i)
SET @TB_ONLY_SCHEMA = (SELECT ONLYSCHEMA FROM #TABLE_TEMP WHERE ITEM=@i)
SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''''+column_name+'''''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TB_ONLY_NAME AND TABLE_SCHEMA=@TB_ONLY_SCHEMA
SELECT @columnHeader2 = COALESCE(@columnHeader2+',' ,'')+ 'CONVERT(varchar(MAX),['+column_name+'])' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TB_ONLY_NAME AND TABLE_SCHEMA=@TB_ONLY_SCHEMA
SET @Union ='SELECT '+ @columnHeader +' UNION ALL '
SET @bcpCommand = 'bcp "'+@Union+' SELECT '+@columnHeader2+' FROM '+@name_tb_cod+'" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -c -T -t";" -r"\n"'
SET @bcpCommand = 'master..xp_cmdshell '+ ''''+@bcpCommand+'''';
EXEC (@bcpCommand);
SET @i=@i+1;
SET @name_tb =''; SET @name_tb_cod=''; SET @FileName=''; SET @TB_ONLY_NAME='';
SET @columnHeader=null;SET @columnHeader2=null;
SET @Union=''; SET @bcpCommand=''; SET @name_tb_cod=''; SET @TB_ONLY_SCHEMA='';
END
END
Comentarios