Creating a Table (SQL-DMO)

This example illustrates table creation. Storage for large text and BLOB data in the table is assigned from a non-default filegroup.

Dim oDatabase As SQLDMO.Database

Dim tableCategories As New SQLDMO.Table

Dim colCategoryID As New SQLDMO.Column

Dim colCategoryName As New SQLDMO.Column

Dim colDescription As New SQLDMO.Column

Dim colPicture As New SQLDMO.Column

' Get the Northwind database. Note: Create and connect of SQLServer

' object used is not illustrated in this example.

Set oDatabase = oSQLServer.Databases("Northwind")

' Populate the Column objects to define the table columns.

colCategoryID.Name = "CategoryID"

colCategoryID.Datatype = "int"

colCategoryID.Identity = True

colCategoryID.IdentityIncrement = 1

colCategoryID.IdentitySeed = 1

colCategoryID.AllowNulls = False

colCategoryName.Name = "CategoryName"

colCategoryName.Datatype = "varchar"

colCategoryName.Length = 15

colCategoryName.AllowNulls = False

colDescription.Name = "Description"

colDescription.Datatype = "text"

colDescription.AllowNulls = True

colPicture.Name = "Picture"

colPicture.Datatype = "image"

colPicture.AllowNulls = True

' Name the table, then set desired properties to control eventual table

' construction.

tableCategories.Name = "Categories"

tableCategories.FileGroup = "PRIMARY"

tableCategories.TextFileGroup = "fgNorthwindTxtImg"

' Add populated Column objects to the Columns collection of the

' Table object.

tableCategories.Columns.Add colCategoryID

tableCategories.Columns.Add colCategoryName

tableCategories.Columns.Add colDescription

tableCategories.Columns.Add colPicture

' Create the table by adding the Table object to its containing

' collection.

oDatabase.Tables.Add tableCategories

See Also