--- title: "Setting Properties - SMO | Microsoft Docs" ms.custom: "" ms.date: "08/06/2017" ms.prod: "sql-server-2016" ms.reviewer: "" ms.suite: "" ms.technology: - "docset-sql-devref" ms.tgt_pltfrm: "" ms.topic: "reference" helpviewer_keywords: - "SMO [SQL Server], properties" - "SQL Server Management Objects, properties" - "properties [SMO]" ms.assetid: 342569ba-d2f7-44d2-8f3f-ae9c701c7f0f caps.latest.revision: 50 author: "JennieHubbard" ms.author: "jhubbard" manager: "jhubbard" --- # Setting Properties - SMO Properties are values that store descriptive information about the object. For example, [!INCLUDE[msCoName](../../../includes/msconame-md.md)] [!INCLUDE[ssNoVersion](../../../includes/ssnoversion-md.md)] configuration options are represented by the object's properties. Properties can be accessed either directly or indirectly by using the property collection. Accessing properties directly uses the following syntax: `objInstance.PropertyName` A property value can be modified or retrieved depending on whether the property has read/write access or read-only access. It is also necessary to set certain properties before an object can be created. For more information, see the SMO reference for the particular object. > [!NOTE] > Collections of child objects appear as the property of an object. For example, the **Tables** collection is a property of a **Server** object. For more information, see [Using Collections](../../../relational-databases/server-management-objects-smo/create-program/using-collections.md). The properties of an object are members of the Properties collection. The Properties collection can be used to iterate through every property of an object. Sometimes a property is not available for the following reasons: - The server version does not support the property, such as if you try to access a property that represents a new [!INCLUDE[ssNoVersion](../../../includes/ssnoversion-md.md)] feature on an older version of [!INCLUDE[ssNoVersion](../../../includes/ssnoversion-md.md)]. - The server does not provide data for the property, such as if you try to access a property that represents a [!INCLUDE[ssNoVersion](../../../includes/ssnoversion-md.md)] component that is not installed. You can handle these circumstances by catching the and the SMO exceptions. ## Setting Default Initialization Fields SMO performs an optimization when retrieving objects. The optimization minimizes the number of properties loaded by automatically scaling between the following states: 1. Partially loaded. When an object is first referenced it has a minimum of properties available (such as Name and Schema). 2. Fully loaded. When any property is referenced, the remaining properties that are quick to load, are initialized and are made available. 3. Properties that use lots of memory. The remaining unavailable properties use lots of memory and have an property value of true (such as ). These properties are loaded only when specifically referenced. If your application does fetch extra properties, besides the ones provided in the partially loaded state, it submits a query to retrieve these extra properties and scales up to the fully loaded state. This can cause unnecessary traffic between the client and server. More optimization can be achieved by calling the method. The method allows specification of the properties that are loaded when the object is initialized. The method sets the property loading behavior for the rest of application or until it is reset. You can save the original behavior by using the method and restore it as required. ## Examples 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 [Create a Visual C# SMO Project in Visual Studio .NET](../../../relational-databases/server-management-objects-smo/how-to-create-a-visual-csharp-smo-project-in-visual-studio-net.md). ## Getting and Setting a Property in Visual Basic This code example shows how to get the property of the object and how to set the property of the property to the **ExecuteSql** member of the enumerated type. ```VBNET 'Connect to the local, default instance of SQL Server. Dim srv As Server srv = New Server 'Get a property. Console.WriteLine(srv.Information.Version) 'Set a property. srv.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql ``` ## Getting and Setting a Property in Visual C# This code example shows how to get the property of the object and how to set the property of the property to the **ExecuteSql** member of the enumerated type. ```csharp { //Connect to the local, default instance of SQL Server. Server srv; srv = new Server(); //Get a property. Console.WriteLine(srv.Information.Version); //Set a property. srv.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql; } ``` ## Setting Various Properties Before an Object is Created in Visual Basic This code example shows how to directly set the property of the object, and how to create and add columns before you create the object. ```VBNET 'Connect to the local, default instance of SQL Server. Dim srv As Server srv = New Server 'Create a new table in the AdventureWorks2012 database. Dim db As Database db = srv.Databases("AdventureWorks2012") Dim tb As Table 'Specify the parent database, table schema and the table name in the constructor. tb = New Table(db, "Test_Table", "HumanResources") 'Add columns because the table requires columns before it can be created. Dim c1 As Column 'Specify the parent table, the column name and data type in the constructor. c1 = New Column(tb, "ID", DataType.Int) tb.Columns.Add(c1) c1.Nullable = False c1.Identity = True c1.IdentityIncrement = 1 c1.IdentitySeed = 0 Dim c2 As Column c2 = New Column(tb, "Name", DataType.NVarChar(100)) c2.Nullable = False tb.Columns.Add(c2) tb.AnsiNullsStatus = True 'Create the table on the instance of SQL Server. tb.Create() ``` ## Setting Various Properties Before an Object is Created in Visual C# This code example shows how to directly set the property of the object, and how to create and add columns before you create the object. ```csharp { //Connect to the local, default instance of SQL Server. Server srv; srv = new Server(); //Create a new table in the AdventureWorks2012 database. Database db; db = srv.Databases("AdventureWorks2012"); Table tb; //Specify the parent database, table schema, and the table name in the constructor. tb = new Table(db, "Test_Table", "HumanResources"); //Add columns because the table requires columns before it can be created. Column c1; //Specify the parent table, the column name, and data type in the constructor. c1 = new Column(tb, "ID", DataType.Int); tb.Columns.Add(c1); c1.Nullable = false; c1.Identity = true; c1.IdentityIncrement = 1; c1.IdentitySeed = 0; Column c2; c2 = new Column(tb, "Name", DataType.NVarChar(100)); c2.Nullable = false; tb.Columns.Add(c2); tb.AnsiNullsStatus = true; //Create the table on the instance of SQL Server. tb.Create(); } ``` ## Iterating Through All Properties of an Object in Visual Basic This code example iterates through the **Properties** collection of the object and displays them on the [!INCLUDE[vsprvs](../../../includes/vsprvs-md.md)] Output screen. In the example, the object has been put in square parentheses because it is also a [!INCLUDE[vbprvb](../../../includes/vbprvb-md.md)] keyword. ```VBNET 'Connect to the local, default instance of SQL Server. Dim srv As Server srv = New Server 'Set properties on the uspGetEmployeeManagers stored procedure on the AdventureWorks2012 database. Dim db As Database db = srv.Databases("AdventureWorks2012") Dim sp As StoredProcedure sp = db.StoredProcedures("uspGetEmployeeManagers") sp.AnsiNullsStatus = False sp.QuotedIdentifierStatus = False 'Iterate through the properties of the stored procedure and display. 'Note the Property object requires [] parentheses to distinguish it from the Visual Basic key word. Dim p As [Property] For Each p In sp.Properties Console.WriteLine(p.Name & p.Value) Next ``` ## Iterating Through All Properties of an Object in Visual C# This code example iterates through the **Properties** collection of the object and displays them on the [!INCLUDE[vsprvs](../../../includes/vsprvs-md.md)] Output screen. ```csharp { //Connect to the local, default instance of SQL Server. Server srv; srv = new Server(); //Set properties on the uspGetEmployeedManagers stored procedure on the AdventureWorks2012 database. Database db; db = srv.Databases("AdventureWorks2012"); StoredProcedure sp; sp = db.StoredProcedures("uspGetEmployeeManagers"); sp.AnsiNullsStatus = false; sp.QuotedIdentifierStatus = false; //Iterate through the properties of the stored procedure and display. Property p; foreach ( p in sp.Properties) { Console.WriteLine(p.Name + p.Value); } } ``` ## Setting Default Initialization Fields in Visual Basic This code example shows how to minimize the number of object properties initialized in an SMO program. You have to include the `using System.Collections.Specialized`; statement to use the object. [!INCLUDE[ssSqlProfiler](../../../includes/sssqlprofiler-md.md)] can be used to compare the number statements sent to the instance of [!INCLUDE[ssNoVersion](../../../includes/ssnoversion-md.md)] with this optimization. ```VBNET 'Connect to the local, default instance of SQL Server. Dim srv As Server srv = New Server 'Reference the AdventureWorks2012 database. Dim db As Database db = srv.Databases("AdventureWorks2012") 'Assign the Table object type to a System.Type object variable. Dim tb As Table Dim typ As Type tb = New Table typ = tb.GetType 'Assign the current default initialization fields for the Table object type to a 'StringCollection object variable. Dim sc As StringCollection sc = srv.GetDefaultInitFields(typ) 'Set the default initialization fields for the Table object type to the CreateDate property. srv.SetDefaultInitFields(typ, "CreateDate") 'Retrieve the Schema, Name, and CreateDate properties for every table in AdventureWorks2012. 'Note that the improvement in performance can be viewed in SQL Profiler. For Each tb In db.Tables Console.WriteLine(tb.Schema + "." + tb.Name + " " + tb.CreateDate) Next 'Set the default initialization fields for the Table object type back to the original settings. srv.SetDefaultInitFields(typ, sc) ``` ## Setting Default Initialization Fields in Visual C# This code example shows how to minimize the number of object properties initialized in an SMO program. You have to include the `using System.Collections.Specialized`; statement to use the object. [!INCLUDE[ssSqlProfiler](../../../includes/sssqlprofiler-md.md)] can be used to compare the number statements sent to the instance of [!INCLUDE[ssNoVersion](../../../includes/ssnoversion-md.md)] with this optimization. ```csharp { //Connect to the local, default instance of SQL Server. Server srv; srv = new Server(); //Reference the AdventureWorks2012 database. Database db; db = srv.Databases("AdventureWorks2012"); //Assign the Table object type to a System.Type object variable. Table tb; Type typ; tb = new Table(); typ = tb.GetType; //Assign the current default initialization fields for the Table object type to a //StringCollection object variable. StringCollection sc; sc = srv.GetDefaultInitFields(typ); //Set the default initialization fields for the Table object type to the CreateDate property. srv.SetDefaultInitFields(typ, "CreateDate"); //Retrieve the Schema, Name, and CreateDate properties for every table in AdventureWorks2012. //Note that the improvement in performance can be viewed in SQL Server Profiler. foreach ( tb in db.Tables) { Console.WriteLine(tb.Schema + "." + tb.Name + " " + tb.CreateDate); } //Set the default initialization fields for the Table object type back to the original settings. srv.SetDefaultInitFields(typ, sc); } ```