Using Collections

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL database in Microsoft Fabric

A collection is a list of objects that have been constructed from the same object class and that share the same parent object. The collection object always contains the name of the object type with the Collection suffix. For example, to access the columns in a specified table, use the ColumnCollection object type. It contains all the Column objects that belong to the same Table object.

The Microsoft Visual Basic For...Each statement or the Microsoft C# foreach statement can be used to iterate through each member of the collection.

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.

Referencing an Object by Using a Collection in Visual Basic

This code example shows how to set a column property by using the Columns, Tables, and Databases properties. These properties represent collections, which can be used to identify a particular object when they are used with a parameter that specifies the name of the object. The name and the schema are required for the Tables collection object property.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Modify a property using the Databases, Tables, and Columns collections to reference a column.
srv.Databases("AdventureWorks2022").Tables("Person", "Person").Columns("ModifiedDate").Nullable = True
'Call the Alter method to make the change on the instance of SQL Server.
srv.Databases("AdventureWorks2022").Tables("Person", "Person").Columns("ModifiedDate").Alter()

Referencing an Object by Using a Collection in Visual C#

This code example shows how to set a column property by using the Columns, Tables, and Databases properties. These properties represent collections, which can be used to identify a particular object when they are used with a parameter that specifies the name of the object. The name and the schema are required for the Tables collection object property.

{   
//Connect to the local, default instance of SQL Server.   
Server srv;   
srv = new Server();   
//Modify a property using the Databases, Tables, and Columns collections to reference a column.   
srv.Databases["AdventureWorks2022"].Tables["Person", "Person"].Columns["LastName"].Nullable = true;   
//Call the Alter method to make the change on the instance of SQL Server.   
srv.Databases["AdventureWorks2022"].Tables["Person", "Person"].Columns["LastName"].Alter();   
}  

Iterating Through the Members of a Collection in Visual Basic

This code example iterates through the Databases collection property and displays all database connections to the instance of SQL Server.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
Dim count As Integer
Dim total As Integer
'Iterate through the databases and call the GetActiveDBConnectionCount method.
Dim db As Database
For Each db In srv.Databases
    count = srv.GetActiveDBConnectionCount(db.Name)
    total = total + count
    'Display the number of connections for each database.
    Console.WriteLine(count & " connections on " & db.Name)
Next
'Display the total number of connections on the instance of SQL Server.
Console.WriteLine("Total connections =" & total)

Iterating Through the Members of a Collection in Visual C#

This code example iterates through the Databases collection property and displays all database connections to the instance of SQL Server.

//Connect to the local, default instance of SQL Server.   
{   
Server srv = default(Server);   
srv = new Server();   
int count = 0;   
int total = 0;   
//Iterate through the databases and call the GetActiveDBConnectionCount method.   
Database db = default(Database);   
foreach ( db in srv.Databases) {   
  count = srv.GetActiveDBConnectionCount(db.Name);   
  total = total + count;   
  //Display the number of connections for each database.   
  Console.WriteLine(count + " connections on " + db.Name);   
}   
//Display the total number of connections on the instance of SQL Server.   
Console.WriteLine("Total connections =" + total);   
}