在查询分析器中,通过SQLDMO来得到脚本

--用以下脚本,可以得到任意对象的创建时的脚本,网上好像也流传有,但属于自己的东西用着还是感觉舒坦.
--增加快捷键后,然后在查询分析器中按相应的快捷键就能显示对象的脚本.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

 

ALTER    procedure sp_script
        @objectname varchar(50)
as
declare @databasename varchar(50)

set @databasename = DB_NAME(DB_ID())

declare        @str varchar(4000)


DECLARE @object int
declare @objectdatabase int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @DataBaseCount int, @Current int, @DataBase int

declare @table int, @tables int

EXEC @hr = sp_OACreate @#SQLDMO.SQLServer@#, @object OUT
IF @hr <> 0
BEGIN
     EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

EXEC @hr = sp_OAMethod @object, @#Connect@#, NULL, @@servername,
    @#sa@#, @#@#

IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object
    RETURN
END

EXEC @hr = sp_OAGetProperty @object, @#Databases@#, @objectdatabase output

IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @objectdatabase, @src OUT, @desc OUT
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

 

EXEC @hr = sp_OAGetProperty @objectdatabase, @#count@#, @DataBaseCount output
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @objectdatabase, @src OUT, @desc OUT
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END


set @Current = 1
loop1:

  EXEC @hr = sp_OAMethod @objectdatabase, @#Item@#, @Database output, @databasename

  IF @hr <> 0
  BEGIN
    EXEC sp_OAGetErrorInfo @objectdatabase
     RETURN
  END


EXEC @hr = sp_OAMethod @Database, @#GetObjectByName@#, @table output, @objectname

  IF @hr <> 0
  BEGIN
    EXEC sp_OAGetErrorInfo @Database
     RETURN
  END

Exec @hr = sp_OAMethod @table, @#Script@#, @str output, 5
  IF @hr <> 0
  BEGIN
    EXEC sp_OAGetErrorInfo @table
     RETURN
  END


print @str

 

 


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

原文转自：http://www.ltesting.net