共用方式為


建立、改變和移除檢視

在 SQL Server 管理物件 (SMO) 中,SQL Server 檢視是由 View 物件表示。

View 物件的 TextBody 屬性會定義檢視。該屬性等於用於建立檢視的 Transact-SQL SELECT 陳述式。

範例

如果要使用所提供的任何程式碼範例,您必須選擇建立應用程式用的程式設計環境、程式設計範本,及程式設計語言。如需詳細資訊,請參閱<如何:在 Visual Studio .NET 中建立 Visual Basic SMO 專案>或<如何:在 Visual Studio .NET 中建立 Visual C# SMO 專案>。

在 Visual Basic 中建立、改變和移除檢視

此程式碼範例示範如何使用內部聯結建立兩個資料表的檢視。該檢視是利用文字模式建立,所以必須設定 TextHeader 屬性。

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2008R2 database.
Dim db As Database
db = srv.Databases("AdventureWorks2008R2")
'Define a View object variable by supplying the parent database, view name and schema in the constructor.
Dim myview As View
myview = New View(db, "Test_View", "Sales")
'Set the TextHeader and TextBody property to define the view.
myview.TextHeader = "CREATE VIEW [Sales].[Test_View] AS"
myview.TextBody = "SELECT h.SalesOrderID, d.OrderQty FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID"
'Create the view on the instance of SQL Server.
myview.Create()
'Remove the view.
myview.Drop()

在 Visual C# 中建立、改變和移除檢視

此程式碼範例示範如何使用內部聯結建立兩個資料表的檢視。該檢視是利用文字模式建立,所以必須設定 TextHeader 屬性。

{
        //Connect to the local, default instance of SQL Server. 
        Server srv; 
        srv = new Server(); 
        //Reference the AdventureWorks2008R2 database. 
        Database db; 
        db = srv.Databases["AdventureWorks2008R2"]; 
        //Define a View object variable by supplying the parent database, view name and schema in the constructor. 
        View myview; 
        myview = new View(db, "Test_View", "Sales"); 
        //Set the TextHeader and TextBody property to define the view. 
        myview.TextHeader = "CREATE VIEW [Sales].[Test_View] AS"; 
        myview.TextBody = "SELECT h.SalesOrderID, d.OrderQty FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID"; 
        //Create the view on the instance of SQL Server. 
        myview.Create(); 
        //Remove the view. 
        myview.Drop(); 
        }

在 PowerShell 中建立、改變和移除檢視

此程式碼範例示範如何使用內部聯結建立兩個資料表的檢視。該檢視是利用文字模式建立,所以必須設定 TextHeader 屬性。

# Set the path context to the local, default instance of SQL Server and get a reference to AdventureWorks2008R2
CD \sql\localhost\default\databases
$db = get-item Adventureworks2008R2

# Define a View object variable by supplying the parent database, view name and schema in the constructor. 
$myview  = New-Object -TypeName Microsoft.SqlServer.Management.SMO.View `
-argumentlist $db, "Test_View", "Sales"
      
# Set the TextHeader and TextBody property to define the view. 
$myview.TextHeader = "CREATE VIEW [Sales].[Test_View] AS"
$myview.TextBody ="SELECT h.SalesOrderID, d.OrderQty FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID"
        
# Create the view on the instance of SQL Server. 
$myview.Create()

# Remove the view. 
$myview.Drop();

請參閱

參考