SQLServer获得所有用户存储过程、根据存储过程名称获得内容、获得数据库对象创建脚本
/*在查询分析器中调用sqldmo生成脚本--函数邹建 2003.07-----------------*//*--调用实例print dbo.fgetscript('zj','','','xzkh_sa','syscolumns')/*--得到所有对象的脚本declare @name varchar(250)declare #aa cursor forselect name from sysobjects where xtype not in('S','PK','D','X','L')open #aafetch next from #aa into @namewhile @@fetch_status=0beginprint dbo.fgetscript('zj','','','xzkh_sa',@name)fetch next from #aa into @nameendclose #aadeallocate #aa--*/*/if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0)drop function fgetscriptgocreate function fgetscript(@servername varchar(50) --服务器名,@userid varchar(50)='sa' --用户名,如果为nt验证方式,则为空,@password varchar(50)='' --密码,@databasename varchar(50) --数据库名称,@objectname varchar(250) --对象名) returns varchar(8000)asbegindeclare @re varchar(8000) --返回脚本declare @srvid int,@dbsid int --定义服务器、数据库集iddeclare @dbid int,@tbid int --数据库、表iddeclare @err int,@src varchar(255), @desc varchar(255) --错误处理变量--创建sqldmo对象exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid outputif @err<>0 goto lberr--连接服务器if isnull(@userid,'')='' --如果是 Nt验证方式beginexec @err=sp_oasetproperty @srvid,'loginsecure',1if @err<>0 goto lberrexec @err=sp_oamethod @srvid,'connect',null,@servernameendelseexec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@passwordif @err<>0 goto lberr--获取数据库集exec @err=sp_oagetproperty @srvid,'databases',@dbsid outputif @err<>0 goto lberr--获取要取得脚本的数据库idexec @err=sp_oamethod @dbsid,'item',@dbid output,@databasenameif @err<>0 goto lberr--获取要取得脚本的对象idexec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectnameif @err<>0 goto lberr--取得脚本exec @err=sp_oamethod @tbid,'script',@re outputif @err<>0 goto lberr--print @rereturn(@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 outset @re='错误号: '+@re+char(13)+'错误源: '+@src+char(13)+'错误描述: '+@descreturn(@re)endgo执行SQL函数要使用select
如：select function