使用BCP命令导出SQL Server表数据

使用BCP命令导出表数据到电子表格文件：

--EXEC master..xp_cmdshell 'BCP test..person out f:\backup\person.xls -c -T'

declare @tblname varchar(100)
declare @dbname varchar(100)
declare @dir varchar(200)
declare @str varchar(500)

set @dir = 'f:\backup\'
set @dbname = 'test'
set @tblname = ''

while exists(select name from sys.tables where SCHEMA_NAME(schema_id)=N'dbo' and @tblname < name)
begin
    SELECT @tblname = min(name) from sys.tables where SCHEMA_NAME(schema_id)=N'dbo' and @tblname < name
    set @str = 'BCP '+@dbname+'..'+@tblname+' out '+@dir+@tblname+'.xls -c -T'
    exec master..xp_cmdshell @str
end