转: CREATE proc spGenInsertSQL @TableName as varchar(100) as --declare @TableName varchar(100) --set @TableName = 'orders' --set @TableName = 'eeducation' DECLARE xCursor CURSOR FOR SELECT name,xusertype FROM syscolumns WHERE (id = OBJECT_ID(@TableName)) declare @F1 varchar(100) declare @F2 integer declare @SQL varchar(8000) set @sql ='SELECT ''INSERT INTO ' + @TableName + ' VALUES(''' OPEN xCursor FETCH xCursor into @F1,@F2 WHILE @@FETCH_STATUS = 0 BEGIN set @sql =@sql + + case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + ' else '+' end + 'replace(ISNULL(cast(' + @F1 + ' as varchar),''NULL''),'''''''','''''''''''')' + case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + ' else '+' end + char(13) + ''',''' FETCH NEXT FROM xCursor into @F1,@F2 END CLOSE xCursor DEALLOCATE xCursor set @sql = left(@sql,len(@sql) - 5) + ' + '')'' FROM ' + @TableName print @sql exec (@sql) GO
采用SQL DMO,可以很方便的做到。Dim oSQL As SQLDMO.SQLServer Dim oTable As SQLDMO.Table Dim oView As SQLDMO.View
Set oSQL = New SQLServer oSQL.Connect "localhost", "sa", "" Open "c:\1.sql" For Output As #1 '导出建表脚本 For Each oTable In oSQL.Databases("haichen").Tables If (oTable.Attributes And SQLDMOTabAtt_SystemObject) <> SQLDMOTabAtt_SystemObject Then Print #1, oTable.Script(SQLDMOScript_Default, , , SQLDMOScript2_Default) End If Next
'导出建视图脚本 For Each oView In oSQL.Databases("haichen").Views If Not oView.SystemObject Then Print #1, oView.Script(SQLDMOScript_Default, , SQLDMOScript2_Default) End If Next
Close #1
用这个函数,你在程序中调用就行了: /* 在查询分析器中调用sqldmo生成脚本--函数--调用实例 print dbo.fgetscript('zj','','','xzkh_sa','syscolumns') */ if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0) drop function fgetscript go create function fgetscript( @servername varchar(50) --服务器名 ,@userid varchar(50)='sa' --用户名,如果为nt验证方式,则为空 ,@password varchar(50)='' --密码 ,@databasename varchar(50) --数据库名称 ,@objectname varchar(250) --对象名) returns varchar(8000) as begin declare @re varchar(8000) --返回脚本 declare @srvid int,@dbsid int --定义服务器、数据库集id declare @dbid int,@tbid int --数据库、表id declare @err int,@src varchar(255), @desc varchar(255) --错误处理变量--创建sqldmo对象 exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output if @err<>0 goto lberr--连接服务器 if isnull(@userid,'')='' --如果是 Nt验证方式 begin exec @err=sp_oasetproperty @srvid,'loginsecure',1 if @err<>0 goto lberr exec @err=sp_oamethod @srvid,'connect',null,@servername end else exec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@password if @err<>0 goto lberr--获取数据库集 exec @err=sp_oagetproperty @srvid,'databases',@dbsid output if @err<>0 goto lberr--获取要取得脚本的数据库id exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename if @err<>0 goto lberr--获取要取得脚本的对象id exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname if @err<>0 goto lberr--取得脚本 exec @err=sp_oamethod @tbid,'script',@re output if @err<>0 goto lberr --print @re return(@re)lberr: exec sp_oageterrorinfo NULL, @src out, @desc out declare @errb varbinary(4) set @errb=cast(@err as varbinary(4)) exec master..xp_varbintohexstr @errb,@re out set @re='错误号: '+@re +char(13)+'错误源: '+@src +char(13)+'错误描述: '+@desc return(@re) end go
--调用例子,在查询分析器中,得到所有对象的创建脚本declare @name varchar(250) declare #aa cursor for select name from sysobjects open #aa fetch next from #aa into @name while @@fetch_status=0 begin print dbo.fgetscript('zj','','','xzkh_sa',@name) fetch next from #aa into @name end close #aa deallocate #aa
CREATE proc spGenInsertSQL
@TableName as varchar(100)
as
--declare @TableName varchar(100)
--set @TableName = 'orders'
--set @TableName = 'eeducation'
DECLARE xCursor CURSOR FOR
SELECT name,xusertype
FROM syscolumns
WHERE (id = OBJECT_ID(@TableName))
declare @F1 varchar(100)
declare @F2 integer
declare @SQL varchar(8000)
set @sql ='SELECT ''INSERT INTO ' + @TableName + ' VALUES('''
OPEN xCursor
FETCH xCursor into @F1,@F2
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql =@sql +
+ case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + ' else '+' end
+ 'replace(ISNULL(cast(' + @F1 + ' as varchar),''NULL''),'''''''','''''''''''')'
+ case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + ' else '+' end
+ char(13) + ''','''
FETCH NEXT FROM xCursor into @F1,@F2
END
CLOSE xCursor
DEALLOCATE xCursor
set @sql = left(@sql,len(@sql) - 5) + ' + '')'' FROM ' + @TableName
print @sql
exec (@sql)
GO
Dim oTable As SQLDMO.Table
Dim oView As SQLDMO.View
Set oSQL = New SQLServer
oSQL.Connect "localhost", "sa", ""
Open "c:\1.sql" For Output As #1
'导出建表脚本
For Each oTable In oSQL.Databases("haichen").Tables
If (oTable.Attributes And SQLDMOTabAtt_SystemObject) <> SQLDMOTabAtt_SystemObject Then
Print #1, oTable.Script(SQLDMOScript_Default, , , SQLDMOScript2_Default)
End If
Next
'导出建视图脚本
For Each oView In oSQL.Databases("haichen").Views
If Not oView.SystemObject Then
Print #1, oView.Script(SQLDMOScript_Default, , SQLDMOScript2_Default)
End If
Next
Close #1
/*
在查询分析器中调用sqldmo生成脚本--函数--调用实例
print dbo.fgetscript('zj','','','xzkh_sa','syscolumns')
*/
if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0)
drop function fgetscript
go
create function fgetscript(
@servername varchar(50) --服务器名
,@userid varchar(50)='sa' --用户名,如果为nt验证方式,则为空
,@password varchar(50)='' --密码
,@databasename varchar(50) --数据库名称
,@objectname varchar(250) --对象名) returns varchar(8000)
as
begin
declare @re varchar(8000) --返回脚本
declare @srvid int,@dbsid int --定义服务器、数据库集id
declare @dbid int,@tbid int --数据库、表id
declare @err int,@src varchar(255), @desc varchar(255) --错误处理变量--创建sqldmo对象
exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output
if @err<>0 goto lberr--连接服务器
if isnull(@userid,'')='' --如果是 Nt验证方式
begin
exec @err=sp_oasetproperty @srvid,'loginsecure',1
if @err<>0 goto lberr exec @err=sp_oamethod @srvid,'connect',null,@servername
end
else
exec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@password if @err<>0 goto lberr--获取数据库集
exec @err=sp_oagetproperty @srvid,'databases',@dbsid output
if @err<>0 goto lberr--获取要取得脚本的数据库id
exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename
if @err<>0 goto lberr--获取要取得脚本的对象id
exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname
if @err<>0 goto lberr--取得脚本
exec @err=sp_oamethod @tbid,'script',@re output
if @err<>0 goto lberr --print @re
return(@re)lberr:
exec sp_oageterrorinfo NULL, @src out, @desc out
declare @errb varbinary(4)
set @errb=cast(@err as varbinary(4))
exec master..xp_varbintohexstr @errb,@re out
set @re='错误号: '+@re
+char(13)+'错误源: '+@src
+char(13)+'错误描述: '+@desc
return(@re)
end
go
declare #aa cursor for select name from sysobjects
open #aa
fetch next from #aa into @name
while @@fetch_status=0
begin
print dbo.fgetscript('zj','','','xzkh_sa',@name)
fetch next from #aa into @name
end
close #aa
deallocate #aa
全部改成适用你程序调用的过程我是用VB的,如果你需要,我可以再做一个VB的给你.