Share via

Creating, Altering, and Removing Defaults

  • Article

In SQL Server Management Objects (SMO), the default constraint is represented by the Default object.

The TextBody property of the Default object is used to set the value to be inserted. This can be a constant or a Transact-SQL statement that returns a constant value, such as GETDATE(). The TextBody property cannot be modified by using the Alter method. Instead, the Default object must be dropped and re-created.

Example

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 Basic SMO Project in Visual Studio .NET or Create a Visual C# SMO Project in Visual Studio .NET.

Creating, Altering, and Removing a Default in Visual Basic

This code example shows how to create one default that is simple text, and another default that is a Transact-SQL statement. The default must be attached to the column by using the BindToColumn method and detached by using the UnbindFromColumn method.

'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")
'Define a Default object variable by supplying the parent database and the default name 
'in the constructor.
Dim def As [Default]
def = New [Default](db, "Test_Default2")
'Set the TextHeader and TextBody properties that define the default.
def.TextHeader = "CREATE DEFAULT [Test_Default2] AS"
def.TextBody = "GetDate()"
'Create the default on the instance of SQL Server.
def.Create()
'Declare a Column object variable and reference a column in the AdventureWorks2012 database.
Dim col As Column
col = db.Tables("SpecialOffer", "Sales").Columns("StartDate")
'Bind the default to the column.
def.BindToColumn("SpecialOffer", "StartDate", "Sales")
'Unbind the default from the column and remove it from the database.
def.UnbindFromColumn("SpecialOffer", "StartDate", "Sales")
def.Drop()

Creating, Altering, and Removing a Default in Visual C#

This code example shows how to create one default that is simple text, and another default that is a Transact-SQL statement. The default must be attached to the column by using the BindToColumn method and detached by using the UnbindFromColumn method.

{
          
          Server srv = new Server();

            //Reference the AdventureWorks2012 database. 
            Database  db = srv.Databases["AdventureWorks2012"];

            //Define a Default object variable by supplying the parent database and the default name 
            //in the constructor. 
            Default def = new Default(db, "Test_Default2");

            //Set the TextHeader and TextBody properties that define the default. 
            def.TextHeader = "CREATE DEFAULT [Test_Default2] AS";
            def.TextBody = "GetDate()";

            //Create the default on the instance of SQL Server. 
            def.Create();
            
            //Bind the default to a column in a table in AdventureWorks2012
            def.BindToColumn("SpecialOffer", "StartDate", "Sales");

            //Unbind the default from the column and remove it from the database. 
            def.UnbindFromColumn("SpecialOffer", "StartDate", "Sales");
            def.Drop();
        }

Creating, Altering, and Removing a Default in PowerShell

This code example shows how to create one default that is simple text, and another default that is a Transact-SQL statement. The default must be attached to the column by using the BindToColumn method and detached by using the UnbindFromColumn method.

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

#Define a Default object variable by supplying the parent database and the default name in the constructor.
$def = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Default `
-argumentlist $db, "Test_Default2"

#Set the TextHeader and TextBody properties that define the default. 
$def.TextHeader = "CREATE DEFAULT [Test_Default2] AS"
$def.TextBody = "GetDate()"

#Create the default on the instance of SQL Server. 
$def.Create()

#Bind the default to the column. 
$def.BindToColumn("SpecialOffer", "StartDate", "Sales")
          
#Unbind the default from the column and remove it from the database. 
$def.UnbindFromColumn("SpecialOffer", "StartDate", "Sales")
$def.Drop()

See Also

Reference

Default