[SQL Server] 모든 사용자 테이블을 파일로 익스포트하기
MS-SQL / SQL Server
모든 사용자 테이블을 파일로 익스포트하기
데이터베이스에 있는 모든 사용자 테이블을 익스포트해서 파일로 만드는 스크립트로 각각의 테이블마다 하나씩 파일을 생성한다.
if exists(select name from sysobjects where name = 'BCP_out_AllTables')
begin
drop procedure BCP_out_AllTables
end
GO
CREATE PROCEDURE BCP_out_AllTables
@dbname varchar(30),
@path varchar(50) = "C:\Temp"
AS
SET NOCOUNT ON
DECLARE @tablename varchar(30)
DECLARE @cmdline varchar(125)
DECLARE @ssql varchar(255)
DECLARE @tabcount smallint
SELECT @tabcount = 0
EXEC ('USE ' + @dbname)
create table #dumptables ([name] varchar(255))
set @ssql = 'insert into #dumptables SELECT [name] from ' + @dbname + '..sysobjects where type = ''U'''
exec (@ssql)
DECLARE cnames CURSOR FOR
select [name] from #dumptables
OPEN cnames
FETCH NEXT FROM cnames INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM cnames INTO @tablename
CONTINUE
END
PRINT 'Exporting table: ' + @tablename
/* build commandline */
-- Add "-S<servername>" for a remoteserver, terminator used = ~ (tilde), specify terminator after '-t', '-T' is used for trusted connection,
-- use -U<username> -P<password> for standard security
SELECT @cmdline = 'bcp ' + @dbname + '..' + @tablename + ' out ' + @path + '\' + @tablename + '.dat -c -t~ -T'
EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT
SELECT @tabcount = @tabcount + 1
FETCH NEXT FROM cnames INTO @tablename
END
DEALLOCATE cnames
/* Print usermessage */
SELECT CONVERT(varchar(10),@tabcount) + ' tables from database '+ @dbname + ' exported to ' + @path
GO
sp_help "BCP_out_AllTables"
GO