通常情况下,SQL Server里面的生成SQL脚本,只会包含数据库及表的字段结构,而不会包含表的数据,也就是SQL脚本里面只有Create database,Create table 这样的语句,没有insert into。
因为SQL Server并不包含这个功能,只能靠第三方的代码了。
以下存储过程可以实现:
CREATE
PROCEDURE dbo.UspOutputData
@tablename sysname
AS
declare @ column varchar(1000)
declare @columndata varchar(1000)
declare @sql varchar(4000)
declare @xtype tinyint
declare @ name sysname
declare @objectId int
declare @objectname sysname
declare @ident int
set nocount on
set @objectId= object_id(@tablename)
if @objectId is null -- 判断对象是否存在
begin
print 'The object not exists'
return
end
set @objectname= rtrim( object_name(@objectId))
if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密
begin
print 'object not in current database'
return
end
if OBJECTPROPERTY(@objectId, 'IsTable') < > 1 -- 判断对象是否是table
begin
print 'The object is not table'
return
end
select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80
if @ident is not null
print 'SET IDENTITY_INSERT '+@TableName+ ' ON'
declare syscolumns_cursor cursor
for select c. name,c.xtype from syscolumns c where c.id=@objectid order by c.colid
open syscolumns_cursor
set @ column= ''
set @columndata= ''
fetch next from syscolumns_cursor into @ name,@xtype
while @@fetch_status < >-1
begin
if @@fetch_status < >-2
begin
if @xtype not in(189,34,35,99,98) --timestamp不需处理,image,text,ntext,sql_variant 暂时不处理
begin
set @ column=@ column+ case when len(@ column)=0 then '' else ',' end+@ name
set @columndata=@columndata+ case when len(@columndata)=0 then '' else ',' ',' ','
end
+ case when @xtype in(167,175) then '' '' '' '' '+'+@ name+ '+' '' '' '' '' --varchar,char
when @xtype in(231,239) then '' 'N' '' '' '+'+@ name+ '+' '' '' '' '' --nvarchar,nchar
when @xtype=61 then '' '' '' '' '+convert(char(23),'+@ name+ ',121)+' '' '' '' '' --datetime
when @xtype=58 then '' '' '' '' '+convert(char(16),'+@ name+ ',120)+' '' '' '' '' --smalldatetime
when @xtype=36 then '' '' '' '' '+convert(char(36),'+@ name+ ')+' '' '' '' '' --uniqueidentifier
else @ name end
end
end
fetch next from syscolumns_cursor into @ name,@xtype
end
close syscolumns_cursor
deallocate syscolumns_cursor
set @sql= 'set nocount on select ' 'insert '+@tablename+ '('+@ column+ ') values(' 'as ' '--'','+@columndata+','')'' from '+@tablename
print '--'+@sql
exec(@sql)
if @ident is not null
print 'SET IDENTITY_INSERT '+@TableName+' OFF'
GO
@tablename sysname
AS
declare @ column varchar(1000)
declare @columndata varchar(1000)
declare @sql varchar(4000)
declare @xtype tinyint
declare @ name sysname
declare @objectId int
declare @objectname sysname
declare @ident int
set nocount on
set @objectId= object_id(@tablename)
if @objectId is null -- 判断对象是否存在
begin
print 'The object not exists'
return
end
set @objectname= rtrim( object_name(@objectId))
if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密
begin
print 'object not in current database'
return
end
if OBJECTPROPERTY(@objectId, 'IsTable') < > 1 -- 判断对象是否是table
begin
print 'The object is not table'
return
end
select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80
if @ident is not null
print 'SET IDENTITY_INSERT '+@TableName+ ' ON'
declare syscolumns_cursor cursor
for select c. name,c.xtype from syscolumns c where c.id=@objectid order by c.colid
open syscolumns_cursor
set @ column= ''
set @columndata= ''
fetch next from syscolumns_cursor into @ name,@xtype
while @@fetch_status < >-1
begin
if @@fetch_status < >-2
begin
if @xtype not in(189,34,35,99,98) --timestamp不需处理,image,text,ntext,sql_variant 暂时不处理
begin
set @ column=@ column+ case when len(@ column)=0 then '' else ',' end+@ name
set @columndata=@columndata+ case when len(@columndata)=0 then '' else ',' ',' ','
end
+ case when @xtype in(167,175) then '' '' '' '' '+'+@ name+ '+' '' '' '' '' --varchar,char
when @xtype in(231,239) then '' 'N' '' '' '+'+@ name+ '+' '' '' '' '' --nvarchar,nchar
when @xtype=61 then '' '' '' '' '+convert(char(23),'+@ name+ ',121)+' '' '' '' '' --datetime
when @xtype=58 then '' '' '' '' '+convert(char(16),'+@ name+ ',120)+' '' '' '' '' --smalldatetime
when @xtype=36 then '' '' '' '' '+convert(char(36),'+@ name+ ')+' '' '' '' '' --uniqueidentifier
else @ name end
end
end
fetch next from syscolumns_cursor into @ name,@xtype
end
close syscolumns_cursor
deallocate syscolumns_cursor
set @sql= 'set nocount on select ' 'insert '+@tablename+ '('+@ column+ ') values(' 'as ' '--'','+@columndata+','')'' from '+@tablename
print '--'+@sql
exec(@sql)
if @ident is not null
print 'SET IDENTITY_INSERT '+@TableName+' OFF'
GO
使用方法:
exec UspOutputData 你的表名
选择【执行模式】为“以文本显示结果”,然后将运行后的结果存成.sql,加上用SQL Server生成的数据库脚本就可以了。
exec UspOutputData 你的表名
选择【执行模式】为“以文本显示结果”,然后将运行后的结果存成.sql,加上用SQL Server生成的数据库脚本就可以了。
另外可以利用第三方工具,导出数据可以用powerbuilder。在database painter里面,用SQL选出,或者直接打开表,点击生成的list datawindow,然后在菜单file->save rows as->选择SQL,那么生成的SQL语句就包括建表和insert数据的SQL了。
本文转自周金桥51CTO博客,原文链接:
http://blog.51cto.com/zhoufoxcn/166427
,如需转载请自行联系原作者