[整理分享技巧1] 利用sp_OA系列存儲過程，讀取建表語句

-- 測試環境

 

SELECT @@VERSION

/*

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   Nov 24 2008 13:01:59   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

*/

 

 

USE [master]

GO

EXEC sp_configure 'show advanced options' , 1

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'Ole Automation Procedures' , 1

RECONFIGURE WITH OVERRIDE

GO

USE [Test]

GO

-- 建測試表

 

IF OBJECT_ID ( 'TestTable' ) IS NOT NULL

    DROP TABLE TestTable

go

CREATE TABLE TestTable(

    ID INT IDENTITY ( 1, 1) CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED ,

    NAME NVARCHAR ( 50) CONSTRAINT Un_TestTable_Name UNIQUE ,

    Memo NVARCHAR ( 200)

)

 

go

 

-- 測試讀取創建表語句

 

DECLARE     @ObjectName varchar ( 50),

            @ObjectType varchar ( 10),

            @TableName varchar ( 50),

            @ScriptType int ,

            @TSQL varchar ( 4000)

 

SET @ObjectName = 'TestTable'

SET @ObjectType = 'Table'

SET @TableName = 'TestTable'

SET @ScriptType = 4

 

DECLARE @CmdStr varchar ( 255)

DECLARE @object int

DECLARE @hr int

 

DECLARE @ServerName varchar ( 255)

SET @ServerName = @@servername

DECLARE @DBName nvarchar ( 255)

SET @DBName = DB_NAME ()

 

SET NOCOUNT ON

SET @CmdStr = 'Connect(' + @ServerName+ ')'

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer' , @object OUT

 

EXEC @hr = sp_OASetProperty @object, 'LoginSecure' , TRUE

 

 

EXEC @hr = sp_OAMethod @object, @CmdStr

SET @CmdStr =

  CASE @ObjectType

    WHEN 'Database' THEN 'Databases("'

    WHEN 'Procedure' THEN 'Databases("' + @DBName + '").StoredProcedures("'

    WHEN 'View'      THEN 'Databases("' + @DBName + '").Views("'

    WHEN 'Table'     THEN 'Databases("' + @DBName + '").Tables("'

    WHEN 'Index'     THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Indexes("'

    WHEN 'ViewIndex' THEN 'Databases("' + @DBName + '").Views("' + @TableName + '").Indexes("'

    WHEN 'Trigger'     THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Triggers("'

    WHEN 'Key'         THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Keys("'

    WHEN 'Check'     THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Checks("'

    WHEN 'Job'         THEN 'Jobserver.Jobs("'

  END

 

SET @CmdStr = @CmdStr + @ObjectName + '").Script'

EXEC @hr = sp_OAMethod @object, @CmdStr, @TSQL OUTPUT , @ScriptType

EXEC @hr = sp_OADestroy @object

 

SELECT @TSQL

 

/*

CREATE TABLE [TestTable] (  

    [ID] [int] IDENTITY (1, 1) NOT NULL ,   [

    NAME] [nvarchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,  

    [Memo] [nvarchar] (200) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,  

    CONSTRAINT [PK_TestTable] PRIMARY KEY  CLUSTERED    (    [ID]   )  ON [PRIMARY] ,  

    CONSTRAINT [Un_TestTable_Name] UNIQUE  NONCLUSTERED    (    [NAME]   )  ON [PRIMARY]  

) ON [PRIMARY]  GO     

*/