Now that we know how to connect to an instance of SQL Server we will probably want to do something useful. To start with, we may want to know what databases exist on the SQL Server we are connected to. To do this, we can enumerate the database collection of the Server object that we used when connecting to the SQL Server instance:
1 For Each db As Database In sqlServer.Databases
The above code snippet adds the name of each database to a list box. I recommend that you spend some time looking at the various properties and methods of the database object as there are a number of things that you can ascertain about each database and operations that you can perform. For example, you can query the owner and size of the database by looking at the Owner and Size properties respectively and you can shrink a database by calling the Shrink method.
Creating new databases and attaching existing databases
To create a database you need to create a new Database object and call it’s Create method:
1 Dim newDatabase As New Database(sqlServer, "MyNewDatabase")
Note that the constructor for the Database object requires that you pass the server object that is currently connected to the instance of the SQL Server and the name of the new database.
If you want to specify the owner of the database then you can do this once the database has been created by calling the SetOwner method and passing the name of the owner:
To attach an existing database file to the connected instance of SQL Server then you can use the AttachDatabase method of the Server object:
1 Dim files As New Collections.Specialized.StringCollection
3 sqlServer.AttachDatabase("MyNewDatabase", files)
Dropping and detaching existing databases
To drop an existing database you call the Drop method of the Database object that you wish to delete:
Alternatively, you can detach a database by calling the DetachDatabase method:
1 sqlServer.DetachDatabase("MyNewDatabase", False)
The DetachDatabase method has a couple of overloads allowing you to specify whether statistics for the database are updated and also to specify whether the full text index file should be removed or not prior to the database being detached.