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

<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!-- [if gte mso 10]> <mce:style><!-- /* Style Definitions */ table.MsoNormalTable {mso-style-name:表格內文; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} --> <!-- [endif]-->

-- 測試環境

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

*/