Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Creating, Altering, and Removing Views

In SQL Server Management Objects (SMO), SQL Server views are represented by the View object.

The TextBody property of the View object defines the view. It is the equivalent of the Transact-SQL SELECT statement for creating a view.

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see How to: Create a Visual Basic SMO Project in Visual Studio .NET or How to: Create a Visual C# SMO Project in Visual Studio .NET.

This code sample shows how to create a view of two tables by using an inner join. The view is created by using text mode, so the TextHeader property must be set.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'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()


This code sample shows how to create a view of two tables by using an inner join. The view is created by using text mode, so the TextHeader property must be set.

{
        //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(); 
        }

This code sample shows how to create a view of two tables by using an inner join. The view is created by using text mode, so the TextHeader property must be set.

# 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();

Reference

Community Additions

ADD
Show:
© 2015 Microsoft