• [整理分享技巧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     

    */

  • 相关阅读:
    检索COM类工厂中CLSID为{00024500-0000-0000-C000-000000000046}的组件时失败
    VSTO 开发中 应用ActionPane、CustomTaskPane
    Thread.Join()的详解
    HBase笔记
    Hive命令详解
    视频地址
    几种表
    如何将数据导入到hive中
    hdfs笔记
    分区表简介
  • 原文地址:https://www.cnblogs.com/Roy_88/p/5463072.html
Copyright © 2020-2023  润新知