Get Object Script

Object | Script


/*


calls SQLDMO Build script in Query Analyzer--stored procedure





Jiangjian 2003.07-----------------* *





/*--Invocation Instance


DECLARE @str varchar (8000)


exec sp_getscript ' zj ', ', ', ' Xzkh_sa ', ' regional information ', @str output


Print @str


*/


if exists (select 1 from sysobjects where id=object_id (' Sp_getscript ') and OBJECTPROPERTY (ID, ' isprocedure ') =1)


drop procedure Sp_getscript


Go


CREATE PROCEDURE Sp_getscript


@servername varchar (50)--server name


, @userid varchar (50)--user name, NULL if NT authentication method


, @password varchar (50)--Password


, @databasename varchar (50)--database name


, @objectname varchar (250)--Object name


, @re varchar (8000) Output--Return script


as


declare @srvid int, @dbsid INT--Define server, database set ID


declare @dbid int, @tbid INT--database, table ID


declare @err int, @src varchar (255), @desc varchar (255)--Error handling variable





--Create SQLDMO Object


exec @err =sp_oacreate ' sqldmo.sqlserver ', @srvid output


if @err <>0 goto Lberr





--Connecting servers


If IsNull (@userid, ') = '--If the NT authentication method


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





--Get the database set


exec @err =sp_oagetproperty @srvid, ' databases ', @dbsid output


if @err <>0 goto Lberr





--Gets the database ID to get the script


exec @err =sp_oamethod @dbsid, ' item ', @dbid output, @databasename


if @err <>0 goto Lberr





--Gets the object ID to get the script


exec @err =sp_oamethod @dbid, ' getobjectbyname ', @tbid output, @objectname


if @err <>0 goto Lberr





--Get script


exec @err =sp_oamethod @tbid, ' script ', @re output


if @err <>0 goto Lberr





--print @re


return





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


Select error Number = @re, error Source = @src, error description = @desc


return





Go