10 个解决方案
#1
create proc 导出xls文件
@服务器名 varchar(255),
@库名 varchar(255),
@表名 varchar(255),
@用户名 varchar(100),
@密码 varchar(100),
@路径及文件名 varchar(255)
as
declare @temp1 nvarchar(4000),@temp2 varchar(8000)
set @temp1='select @value1='''',@value2=''''
select @value1=@value1+'',''''''+a.name+char(39),@value2=@value2+'',cast(''+a.name+'' as varchar(200))'' from '+@库名+'..syscolumns a,'+@库名+'..sysobjects d where a.id=d.id and d.name='''+@表名+''''
exec sp_executesql @temp1,N'@value1 nvarchar(4000) output , @value2 varchar(8000) output',@temp1 output,@temp2 output
select @temp1=right(@temp1,len(@temp1)-1),@temp2=right(@temp2,len(@temp2)-1)
set @temp2='bcp "select '+@temp1+' union all SELECT '+@temp2+' FROM '+@库名+'..'+@表名+'" queryout '+@路径及文件名+' -c -S'+@服务器名+' -U'+@用户名+' -P'+@密码
--print @temp2
EXEC master..xp_cmdshell @temp2
go
exec 导出xls文件 '服务器名','库名','表名','sa','密码','c:\文件.xls'
go
drop proc 导出xls文件
go
@服务器名 varchar(255),
@库名 varchar(255),
@表名 varchar(255),
@用户名 varchar(100),
@密码 varchar(100),
@路径及文件名 varchar(255)
as
declare @temp1 nvarchar(4000),@temp2 varchar(8000)
set @temp1='select @value1='''',@value2=''''
select @value1=@value1+'',''''''+a.name+char(39),@value2=@value2+'',cast(''+a.name+'' as varchar(200))'' from '+@库名+'..syscolumns a,'+@库名+'..sysobjects d where a.id=d.id and d.name='''+@表名+''''
exec sp_executesql @temp1,N'@value1 nvarchar(4000) output , @value2 varchar(8000) output',@temp1 output,@temp2 output
select @temp1=right(@temp1,len(@temp1)-1),@temp2=right(@temp2,len(@temp2)-1)
set @temp2='bcp "select '+@temp1+' union all SELECT '+@temp2+' FROM '+@库名+'..'+@表名+'" queryout '+@路径及文件名+' -c -S'+@服务器名+' -U'+@用户名+' -P'+@密码
--print @temp2
EXEC master..xp_cmdshell @temp2
go
exec 导出xls文件 '服务器名','库名','表名','sa','密码','c:\文件.xls'
go
drop proc 导出xls文件
go
#2
exec master..xp_cmdshell'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout d:\Authors.xls -c -Sservername -Usa -Ppassword '
#3
转载自碧血剑:
告诉你一个最快的方法,用SQLServer连接DBF
在SQLServer中执行
SELECT * into bmk
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="e:\share";User ID=Admin;Password=;Extended properties=dBase 5.0')...bmk
这样就可以把e:\share中的bmk.dbf表导入到Sqlserver中,
速度是最快的
把压箱底的方法都告诉大家
在Form上放一个ADOConnection,连结指向目标Access库
比如txt文件在c:\temp\aaaa.txt
就执行
ADOConnection.Connected := True;
ADOConnection.Execute('Select * Into abcd From [Text;Database=c:\temp].aaaa.txt');
就一切Ok了,这个方法一定行的,我好不容易才研究出来的
有了这两个例子,异构数据库之间导数据的问题就举一反三,迎刃而解了。
把Excel导入到Access中,同txt类似
select * into <table name> from [excel 8.0;database=<filename>].[<sheetname>$]
告诉你一个最快的方法,用SQLServer连接DBF
在SQLServer中执行
SELECT * into bmk
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="e:\share";User ID=Admin;Password=;Extended properties=dBase 5.0')...bmk
这样就可以把e:\share中的bmk.dbf表导入到Sqlserver中,
速度是最快的
把压箱底的方法都告诉大家
在Form上放一个ADOConnection,连结指向目标Access库
比如txt文件在c:\temp\aaaa.txt
就执行
ADOConnection.Connected := True;
ADOConnection.Execute('Select * Into abcd From [Text;Database=c:\temp].aaaa.txt');
就一切Ok了,这个方法一定行的,我好不容易才研究出来的
有了这两个例子,异构数据库之间导数据的问题就举一反三,迎刃而解了。
把Excel导入到Access中,同txt类似
select * into <table name> from [excel 8.0;database=<filename>].[<sheetname>$]
#4
to chpeng:你提供的方法导出的excel文件怎么没有字段名(表头),只有表的数据内容。
to zosky:能给个把sqlserver导出到excel的写法吗?
to zosky:能给个把sqlserver导出到excel的写法吗?
#5
将SQL SERVER中查询到的数据导成一个Excel文件
-- ======================================================
T-SQL代码:
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数:S 是SQL服务器名;U是用户;P是密码
说明:还可以导出文本文件等多种格式
实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
在VB6中应用ADO导出EXCEL文件代码:
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
-- ======================================================
T-SQL代码:
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数:S 是SQL服务器名;U是用户;P是密码
说明:还可以导出文本文件等多种格式
实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
在VB6中应用ADO导出EXCEL文件代码:
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
#6
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
可以成功导出excel文件,但没有导出表头,这excel文件也是没有用的。如何解决?
可以成功导出excel文件,但没有导出表头,这excel文件也是没有用的。如何解决?
#7
?
#8
==========================================================
倒出数据库用户表结构和一样本数据到EXCEL中
-- ======================================================
-- Export all user tables definition and one sample value
-- jan-13-2003,Dr.Zhang
-- ======================================================
SET ANSI_NULLS OFF
GO
SET NOCOUNT ON
GO
DECLARE @tbl varchar(100),@fld varchar(100),@sql nvarchar(4000),@maxlen int,@sample varchar(20)
SELECT d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL INTO #t
FROM syscolumns a, systypes b,sysobjects d
WHERE a.xtype=b.xusertype and a.id=d.id and d.xtype='U'
DECLARE read_cursor CURSOR
FOR SELECT TableName,FieldName FROM #t
SELECT TOP 1 '_TableName ' TableName,
'FieldName ' FieldName,'TypeName ' TypeName,
'Length' Length,'IS_NULL' IS_NULL,
'MaxLenUsed' AS MaxLenUsed,'Sample Value ' Sample,
'Comment ' Comment INTO #tc FROM #t
OPEN read_cursor
FETCH NEXT FROM read_cursor INTO @tbl,@fld
WHILE (@@fetch_status <> -1) --- failes
BEGIN
IF (@@fetch_status <> -2) -- Missing
BEGIN
SET @sql=N'SET @maxlen=(SELECT max(len('+@fld+')) FROM '+@tbl+')'
--PRINT @sql
EXEC SP_EXECUTESQL @sql,N'@maxlen int OUTPUT',@maxlen OUTPUT
--print @maxlen
SET @sql=N'SET @sample=(SELECT TOP 1 '+@fld+' FROM '+@tbl+' WHERE len('+@fld+')='+convert(varchar(5),@maxlen)+')'
EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT
--for quickly
--SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+
--@tbl+' order by 1 desc ))'
--PRINT @sql
print @sample
EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT
INSERT INTO #tc SELECT *,ltrim(ISNULL(@maxlen,0)) as MaxLenUsed,
convert(char(20),ltrim(ISNULL(@sample,' '))) as Sample,' ' Comment FROM #t where TableName=@tbl and FieldName=@fld
END
FETCH NEXT FROM read_cursor INTO @tbl,@fld
END
CLOSE read_cursor
DEALLOCATE read_cursor
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO
select count(*) from #t
DROP TABLE #t
GO
select count(*)-1 from #tc
select * into ##tx from #tc order by tablename
DROP TABLE #tc
--select * from ##tx
declare @db varchar(30),@sql varchar(3000)
set @db=db_name()
set @sql='exec master.dbo.xp_cmdshell ''bcp ..dbo.##tx out c:\'+@db+'_exp.xls -c -Usa -P '''
print @sql
exec(@sql)
GO
DROP TABLE ##tx
GO
倒出数据库用户表结构和一样本数据到EXCEL中
-- ======================================================
-- Export all user tables definition and one sample value
-- jan-13-2003,Dr.Zhang
-- ======================================================
SET ANSI_NULLS OFF
GO
SET NOCOUNT ON
GO
DECLARE @tbl varchar(100),@fld varchar(100),@sql nvarchar(4000),@maxlen int,@sample varchar(20)
SELECT d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL INTO #t
FROM syscolumns a, systypes b,sysobjects d
WHERE a.xtype=b.xusertype and a.id=d.id and d.xtype='U'
DECLARE read_cursor CURSOR
FOR SELECT TableName,FieldName FROM #t
SELECT TOP 1 '_TableName ' TableName,
'FieldName ' FieldName,'TypeName ' TypeName,
'Length' Length,'IS_NULL' IS_NULL,
'MaxLenUsed' AS MaxLenUsed,'Sample Value ' Sample,
'Comment ' Comment INTO #tc FROM #t
OPEN read_cursor
FETCH NEXT FROM read_cursor INTO @tbl,@fld
WHILE (@@fetch_status <> -1) --- failes
BEGIN
IF (@@fetch_status <> -2) -- Missing
BEGIN
SET @sql=N'SET @maxlen=(SELECT max(len('+@fld+')) FROM '+@tbl+')'
--PRINT @sql
EXEC SP_EXECUTESQL @sql,N'@maxlen int OUTPUT',@maxlen OUTPUT
--print @maxlen
SET @sql=N'SET @sample=(SELECT TOP 1 '+@fld+' FROM '+@tbl+' WHERE len('+@fld+')='+convert(varchar(5),@maxlen)+')'
EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT
--for quickly
--SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+
--@tbl+' order by 1 desc ))'
--PRINT @sql
print @sample
EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT
INSERT INTO #tc SELECT *,ltrim(ISNULL(@maxlen,0)) as MaxLenUsed,
convert(char(20),ltrim(ISNULL(@sample,' '))) as Sample,' ' Comment FROM #t where TableName=@tbl and FieldName=@fld
END
FETCH NEXT FROM read_cursor INTO @tbl,@fld
END
CLOSE read_cursor
DEALLOCATE read_cursor
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO
select count(*) from #t
DROP TABLE #t
GO
select count(*)-1 from #tc
select * into ##tx from #tc order by tablename
DROP TABLE #tc
--select * from ##tx
declare @db varchar(30),@sql varchar(3000)
set @db=db_name()
set @sql='exec master.dbo.xp_cmdshell ''bcp ..dbo.##tx out c:\'+@db+'_exp.xls -c -Usa -P '''
print @sql
exec(@sql)
GO
DROP TABLE ##tx
GO
#9
create proc 导出xls文件
@服务器名 varchar(255),
@库名 varchar(255),
@表名 varchar(255),
@用户名 varchar(100),
@密码 varchar(100),
@路径及文件名 varchar(255)
as
declare @temp1 nvarchar(4000),@temp2 varchar(8000)
set @temp1='select @value1='''',@value2='''' ;
select @value1=@value1+'',''''''+a.name+char(39),@value2=@value2+'',cast(''+a.name+'' as varchar(200))'' from '+@库名+'..syscolumns a,'+@库名+'..sysobjects d where a.id=d.id and d.name='''+@表名+''''
exec sp_executesql @temp1,N'@value1 nvarchar(4000) output , @value2 varchar(8000) output',@temp1 output,@temp2 output
select @temp1=right(@temp1,len(@temp1)-1),@temp2=right(@temp2,len(@temp2)-1)
set @temp2='bcp "select '+@temp1+' union all SELECT '+@temp2+' FROM '+@库名+'..'+@表名+'" queryout '+@路径及文件名+' -c -S'+@服务器名+' -U'+@用户名+' -P'+@密码
--print @temp2
EXEC master..xp_cmdshell @temp2
go
exec 导出xls文件 '服务器名','库名','表名','sa','密码','c:\文件.xls'
go
drop proc 导出xls文件
go
@服务器名 varchar(255),
@库名 varchar(255),
@表名 varchar(255),
@用户名 varchar(100),
@密码 varchar(100),
@路径及文件名 varchar(255)
as
declare @temp1 nvarchar(4000),@temp2 varchar(8000)
set @temp1='select @value1='''',@value2='''' ;
select @value1=@value1+'',''''''+a.name+char(39),@value2=@value2+'',cast(''+a.name+'' as varchar(200))'' from '+@库名+'..syscolumns a,'+@库名+'..sysobjects d where a.id=d.id and d.name='''+@表名+''''
exec sp_executesql @temp1,N'@value1 nvarchar(4000) output , @value2 varchar(8000) output',@temp1 output,@temp2 output
select @temp1=right(@temp1,len(@temp1)-1),@temp2=right(@temp2,len(@temp2)-1)
set @temp2='bcp "select '+@temp1+' union all SELECT '+@temp2+' FROM '+@库名+'..'+@表名+'" queryout '+@路径及文件名+' -c -S'+@服务器名+' -U'+@用户名+' -P'+@密码
--print @temp2
EXEC master..xp_cmdshell @temp2
go
exec 导出xls文件 '服务器名','库名','表名','sa','密码','c:\文件.xls'
go
drop proc 导出xls文件
go
#10
真够复杂的,慢慢看吧
#1
create proc 导出xls文件
@服务器名 varchar(255),
@库名 varchar(255),
@表名 varchar(255),
@用户名 varchar(100),
@密码 varchar(100),
@路径及文件名 varchar(255)
as
declare @temp1 nvarchar(4000),@temp2 varchar(8000)
set @temp1='select @value1='''',@value2=''''
select @value1=@value1+'',''''''+a.name+char(39),@value2=@value2+'',cast(''+a.name+'' as varchar(200))'' from '+@库名+'..syscolumns a,'+@库名+'..sysobjects d where a.id=d.id and d.name='''+@表名+''''
exec sp_executesql @temp1,N'@value1 nvarchar(4000) output , @value2 varchar(8000) output',@temp1 output,@temp2 output
select @temp1=right(@temp1,len(@temp1)-1),@temp2=right(@temp2,len(@temp2)-1)
set @temp2='bcp "select '+@temp1+' union all SELECT '+@temp2+' FROM '+@库名+'..'+@表名+'" queryout '+@路径及文件名+' -c -S'+@服务器名+' -U'+@用户名+' -P'+@密码
--print @temp2
EXEC master..xp_cmdshell @temp2
go
exec 导出xls文件 '服务器名','库名','表名','sa','密码','c:\文件.xls'
go
drop proc 导出xls文件
go
@服务器名 varchar(255),
@库名 varchar(255),
@表名 varchar(255),
@用户名 varchar(100),
@密码 varchar(100),
@路径及文件名 varchar(255)
as
declare @temp1 nvarchar(4000),@temp2 varchar(8000)
set @temp1='select @value1='''',@value2=''''
select @value1=@value1+'',''''''+a.name+char(39),@value2=@value2+'',cast(''+a.name+'' as varchar(200))'' from '+@库名+'..syscolumns a,'+@库名+'..sysobjects d where a.id=d.id and d.name='''+@表名+''''
exec sp_executesql @temp1,N'@value1 nvarchar(4000) output , @value2 varchar(8000) output',@temp1 output,@temp2 output
select @temp1=right(@temp1,len(@temp1)-1),@temp2=right(@temp2,len(@temp2)-1)
set @temp2='bcp "select '+@temp1+' union all SELECT '+@temp2+' FROM '+@库名+'..'+@表名+'" queryout '+@路径及文件名+' -c -S'+@服务器名+' -U'+@用户名+' -P'+@密码
--print @temp2
EXEC master..xp_cmdshell @temp2
go
exec 导出xls文件 '服务器名','库名','表名','sa','密码','c:\文件.xls'
go
drop proc 导出xls文件
go
#2
exec master..xp_cmdshell'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout d:\Authors.xls -c -Sservername -Usa -Ppassword '
#3
转载自碧血剑:
告诉你一个最快的方法,用SQLServer连接DBF
在SQLServer中执行
SELECT * into bmk
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="e:\share";User ID=Admin;Password=;Extended properties=dBase 5.0')...bmk
这样就可以把e:\share中的bmk.dbf表导入到Sqlserver中,
速度是最快的
把压箱底的方法都告诉大家
在Form上放一个ADOConnection,连结指向目标Access库
比如txt文件在c:\temp\aaaa.txt
就执行
ADOConnection.Connected := True;
ADOConnection.Execute('Select * Into abcd From [Text;Database=c:\temp].aaaa.txt');
就一切Ok了,这个方法一定行的,我好不容易才研究出来的
有了这两个例子,异构数据库之间导数据的问题就举一反三,迎刃而解了。
把Excel导入到Access中,同txt类似
select * into <table name> from [excel 8.0;database=<filename>].[<sheetname>$]
告诉你一个最快的方法,用SQLServer连接DBF
在SQLServer中执行
SELECT * into bmk
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="e:\share";User ID=Admin;Password=;Extended properties=dBase 5.0')...bmk
这样就可以把e:\share中的bmk.dbf表导入到Sqlserver中,
速度是最快的
把压箱底的方法都告诉大家
在Form上放一个ADOConnection,连结指向目标Access库
比如txt文件在c:\temp\aaaa.txt
就执行
ADOConnection.Connected := True;
ADOConnection.Execute('Select * Into abcd From [Text;Database=c:\temp].aaaa.txt');
就一切Ok了,这个方法一定行的,我好不容易才研究出来的
有了这两个例子,异构数据库之间导数据的问题就举一反三,迎刃而解了。
把Excel导入到Access中,同txt类似
select * into <table name> from [excel 8.0;database=<filename>].[<sheetname>$]
#4
to chpeng:你提供的方法导出的excel文件怎么没有字段名(表头),只有表的数据内容。
to zosky:能给个把sqlserver导出到excel的写法吗?
to zosky:能给个把sqlserver导出到excel的写法吗?
#5
将SQL SERVER中查询到的数据导成一个Excel文件
-- ======================================================
T-SQL代码:
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数:S 是SQL服务器名;U是用户;P是密码
说明:还可以导出文本文件等多种格式
实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
在VB6中应用ADO导出EXCEL文件代码:
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
-- ======================================================
T-SQL代码:
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数:S 是SQL服务器名;U是用户;P是密码
说明:还可以导出文本文件等多种格式
实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
在VB6中应用ADO导出EXCEL文件代码:
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
#6
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
可以成功导出excel文件,但没有导出表头,这excel文件也是没有用的。如何解决?
可以成功导出excel文件,但没有导出表头,这excel文件也是没有用的。如何解决?
#7
?
#8
==========================================================
倒出数据库用户表结构和一样本数据到EXCEL中
-- ======================================================
-- Export all user tables definition and one sample value
-- jan-13-2003,Dr.Zhang
-- ======================================================
SET ANSI_NULLS OFF
GO
SET NOCOUNT ON
GO
DECLARE @tbl varchar(100),@fld varchar(100),@sql nvarchar(4000),@maxlen int,@sample varchar(20)
SELECT d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL INTO #t
FROM syscolumns a, systypes b,sysobjects d
WHERE a.xtype=b.xusertype and a.id=d.id and d.xtype='U'
DECLARE read_cursor CURSOR
FOR SELECT TableName,FieldName FROM #t
SELECT TOP 1 '_TableName ' TableName,
'FieldName ' FieldName,'TypeName ' TypeName,
'Length' Length,'IS_NULL' IS_NULL,
'MaxLenUsed' AS MaxLenUsed,'Sample Value ' Sample,
'Comment ' Comment INTO #tc FROM #t
OPEN read_cursor
FETCH NEXT FROM read_cursor INTO @tbl,@fld
WHILE (@@fetch_status <> -1) --- failes
BEGIN
IF (@@fetch_status <> -2) -- Missing
BEGIN
SET @sql=N'SET @maxlen=(SELECT max(len('+@fld+')) FROM '+@tbl+')'
--PRINT @sql
EXEC SP_EXECUTESQL @sql,N'@maxlen int OUTPUT',@maxlen OUTPUT
--print @maxlen
SET @sql=N'SET @sample=(SELECT TOP 1 '+@fld+' FROM '+@tbl+' WHERE len('+@fld+')='+convert(varchar(5),@maxlen)+')'
EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT
--for quickly
--SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+
--@tbl+' order by 1 desc ))'
--PRINT @sql
print @sample
EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT
INSERT INTO #tc SELECT *,ltrim(ISNULL(@maxlen,0)) as MaxLenUsed,
convert(char(20),ltrim(ISNULL(@sample,' '))) as Sample,' ' Comment FROM #t where TableName=@tbl and FieldName=@fld
END
FETCH NEXT FROM read_cursor INTO @tbl,@fld
END
CLOSE read_cursor
DEALLOCATE read_cursor
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO
select count(*) from #t
DROP TABLE #t
GO
select count(*)-1 from #tc
select * into ##tx from #tc order by tablename
DROP TABLE #tc
--select * from ##tx
declare @db varchar(30),@sql varchar(3000)
set @db=db_name()
set @sql='exec master.dbo.xp_cmdshell ''bcp ..dbo.##tx out c:\'+@db+'_exp.xls -c -Usa -P '''
print @sql
exec(@sql)
GO
DROP TABLE ##tx
GO
倒出数据库用户表结构和一样本数据到EXCEL中
-- ======================================================
-- Export all user tables definition and one sample value
-- jan-13-2003,Dr.Zhang
-- ======================================================
SET ANSI_NULLS OFF
GO
SET NOCOUNT ON
GO
DECLARE @tbl varchar(100),@fld varchar(100),@sql nvarchar(4000),@maxlen int,@sample varchar(20)
SELECT d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL INTO #t
FROM syscolumns a, systypes b,sysobjects d
WHERE a.xtype=b.xusertype and a.id=d.id and d.xtype='U'
DECLARE read_cursor CURSOR
FOR SELECT TableName,FieldName FROM #t
SELECT TOP 1 '_TableName ' TableName,
'FieldName ' FieldName,'TypeName ' TypeName,
'Length' Length,'IS_NULL' IS_NULL,
'MaxLenUsed' AS MaxLenUsed,'Sample Value ' Sample,
'Comment ' Comment INTO #tc FROM #t
OPEN read_cursor
FETCH NEXT FROM read_cursor INTO @tbl,@fld
WHILE (@@fetch_status <> -1) --- failes
BEGIN
IF (@@fetch_status <> -2) -- Missing
BEGIN
SET @sql=N'SET @maxlen=(SELECT max(len('+@fld+')) FROM '+@tbl+')'
--PRINT @sql
EXEC SP_EXECUTESQL @sql,N'@maxlen int OUTPUT',@maxlen OUTPUT
--print @maxlen
SET @sql=N'SET @sample=(SELECT TOP 1 '+@fld+' FROM '+@tbl+' WHERE len('+@fld+')='+convert(varchar(5),@maxlen)+')'
EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT
--for quickly
--SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+
--@tbl+' order by 1 desc ))'
--PRINT @sql
print @sample
EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT
INSERT INTO #tc SELECT *,ltrim(ISNULL(@maxlen,0)) as MaxLenUsed,
convert(char(20),ltrim(ISNULL(@sample,' '))) as Sample,' ' Comment FROM #t where TableName=@tbl and FieldName=@fld
END
FETCH NEXT FROM read_cursor INTO @tbl,@fld
END
CLOSE read_cursor
DEALLOCATE read_cursor
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO
select count(*) from #t
DROP TABLE #t
GO
select count(*)-1 from #tc
select * into ##tx from #tc order by tablename
DROP TABLE #tc
--select * from ##tx
declare @db varchar(30),@sql varchar(3000)
set @db=db_name()
set @sql='exec master.dbo.xp_cmdshell ''bcp ..dbo.##tx out c:\'+@db+'_exp.xls -c -Usa -P '''
print @sql
exec(@sql)
GO
DROP TABLE ##tx
GO
#9
create proc 导出xls文件
@服务器名 varchar(255),
@库名 varchar(255),
@表名 varchar(255),
@用户名 varchar(100),
@密码 varchar(100),
@路径及文件名 varchar(255)
as
declare @temp1 nvarchar(4000),@temp2 varchar(8000)
set @temp1='select @value1='''',@value2='''' ;
select @value1=@value1+'',''''''+a.name+char(39),@value2=@value2+'',cast(''+a.name+'' as varchar(200))'' from '+@库名+'..syscolumns a,'+@库名+'..sysobjects d where a.id=d.id and d.name='''+@表名+''''
exec sp_executesql @temp1,N'@value1 nvarchar(4000) output , @value2 varchar(8000) output',@temp1 output,@temp2 output
select @temp1=right(@temp1,len(@temp1)-1),@temp2=right(@temp2,len(@temp2)-1)
set @temp2='bcp "select '+@temp1+' union all SELECT '+@temp2+' FROM '+@库名+'..'+@表名+'" queryout '+@路径及文件名+' -c -S'+@服务器名+' -U'+@用户名+' -P'+@密码
--print @temp2
EXEC master..xp_cmdshell @temp2
go
exec 导出xls文件 '服务器名','库名','表名','sa','密码','c:\文件.xls'
go
drop proc 导出xls文件
go
@服务器名 varchar(255),
@库名 varchar(255),
@表名 varchar(255),
@用户名 varchar(100),
@密码 varchar(100),
@路径及文件名 varchar(255)
as
declare @temp1 nvarchar(4000),@temp2 varchar(8000)
set @temp1='select @value1='''',@value2='''' ;
select @value1=@value1+'',''''''+a.name+char(39),@value2=@value2+'',cast(''+a.name+'' as varchar(200))'' from '+@库名+'..syscolumns a,'+@库名+'..sysobjects d where a.id=d.id and d.name='''+@表名+''''
exec sp_executesql @temp1,N'@value1 nvarchar(4000) output , @value2 varchar(8000) output',@temp1 output,@temp2 output
select @temp1=right(@temp1,len(@temp1)-1),@temp2=right(@temp2,len(@temp2)-1)
set @temp2='bcp "select '+@temp1+' union all SELECT '+@temp2+' FROM '+@库名+'..'+@表名+'" queryout '+@路径及文件名+' -c -S'+@服务器名+' -U'+@用户名+' -P'+@密码
--print @temp2
EXEC master..xp_cmdshell @temp2
go
exec 导出xls文件 '服务器名','库名','表名','sa','密码','c:\文件.xls'
go
drop proc 导出xls文件
go
#10
真够复杂的,慢慢看吧