Page Split :ZT

SQL存储过程操作数据库对象

最新推荐文章于 2025-06-27 20:08:54 发布

dhz123

最新推荐文章于 2025-06-27 20:08:54 发布

阅读量1k

点赞数

分类专栏： SQL Server 文章标签： database object output table go null

SQL Server 专栏收录该内容

12 篇文章

订阅专栏

博客展示了一个SQL存储过程sp_script，用于操作数据库对象。通过一系列SQL语句，连接到服务器，获取数据库信息，根据对象名获取对应表，并对表进行脚本操作，最后输出脚本内容。

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