Programmatically Enumerating, Attaching, and Detaching SQL Server Databases

Mohammad Elsheimy
14y
11k
0
0

Article

This article was previously published in my blog, Just Like a Magic.

Overview

This writing is like an encyclopedia for the SQL statements and stored procedures used to enumerate, attach, and detach databases on a SQL Server instance. It lists the statements and stored procedures that you can use and discusses them in simple examples.

Introduction

Today we are going to talk about how to programmatically enumerate databases on a SQL Server instance and how you can attach and detach SQL Server databases.

Enumerating Databases on a Server

You can get a list of databases on a server using one of many ways:

INFORMATION_SCHEMA.SCHEMATA system view (SQL Server 2000 only)
sys.sysdatabases system table (a view in SQL Server 2005 and higher versions)
sys.databases system view (SQL Server 2005 and higher versions)
sys.sp_databases stored procedure

INFORMATION_SCHEMA.SCHEMATA System View

If you are using SQL Server 2000, you can query the system view INFORMATION_SCHEMA.SCHEMATA to get information about current databases on the server.

The following is the table diagram for INFORMATION_SCHEMA.SCHEMATA system view:

INFORMATION_SCHEMA.SCHEMATA

Actually, you don't need to worry about any of the view columns, just the first column, CATALOG_NAME, that you need to worry about, it is the database (i.e. catalog) name.

The following code simply prints out the databases currently found on the default SQL Server instance in the current machine:

    SqlConnection conn = new SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI");
    SqlCommand cmd = new SqlCommand("", conn);
    SqlDataReader rdr;

    cmd.CommandText = "SELECT DISTINCT    CATALOG_NAME    FROM    INFORMATION_SCHEMA.SCHEMATA";

    conn.Open();

    rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        Console.WriteLine(rdr.GetString(0));
    }

    rdr.Dispose();
    cmd.Dispose();
    conn.Dispose();

Again, this is for SQL Server 2000 only.

Check the MSDN documentation for INFORMATION_SCHEMA.SCHEMATA system view here.

sys.sysdatabases System Table/View

This is a system table specific to SQL Server 2000. In SQL Server 2005 it is provided for backward compatibility as a system view. Therefore, do not rely on this system view (or table) because it is expected to be removed in a future version of SQL Server.

The definition of this table/view is as following:

sys.sysdatabases

Only the first column, name, is the most important to us, it contains the database name. Other columns of importance (not for the subject of this topic) are:

dbid:
Database ID.
sid:
Security ID for the database.
crdate:
Creation date of the database.
filename:
Database filename.

You can change the line that sets the command text in the previous code to this line:

    cmd.CommandText = "SELECT [name]    FROM    sys.sysdatabases";

Again, using the sys.sysdatabases system table/view is not recommended because it would be removed in a future version of SQL Server.

Check the MSDN documentation for sys.sysdatabases system view/table here.

Check this MSDN article out: Mapping System Tables to System Views for more information about SQL Server 2000 tables mapped to SQL Server 2005 views or stored procedure.

sys.databases System View

This is the new version included in SQL Server 2005 (and higher versions) replaces the SQL Server 2000 sys.sysdatabases table.

This is a very lengthy system view, it includes tenths of columns, we are interested only on the first column, name, that contains the database name.

You can change the line that sets the command text in the first code to this line:

    cmd.CommandText = "SELECT [name]    FROM    sys. databases";

Check the MSDN documentation for sys.sysdatabases system view/table here.

sys.sp_databases Stored Procedure

This way is different from all others because it is not a system view or a system table, it is a system stored procedure.

This stored procedure accepts no parameters and returns a result set of three columns:

DATABASE_NAME:
The name of the database.
DATABASE_SIZE:
The size of the database (in kilobytes.)
REMARKS:
Always NULL. For the Database Engine.

The following code demonstrates this stored procedure:

    SqlConnection conn = new SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI");
    SqlCommand cmd = new SqlCommand("", conn);
    SqlDataReader rdr;

    cmd.CommandText = "exec sys.sp_databases";
    conn.Open();

    rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        Console.WriteLine(rdr.GetString(0));
    }

    rdr.Dispose();
    cmd.Dispose();
    conn.Dispose();

Check the MSDN documentation for sys.sysdatabases system view/table here.

Attaching Databases to the Server

You can programmatically attach a database to the server in two ways:

Using the CREATE DATABASE statement
Using the sys.sp_attach_db system stored procedure
Using the sys.sp_attach_single_file_db system stored procedure

CREATE DATABASE Statement

The CREATE DATABASE statement can be used to create databases into the server or to attach existing database files.

If you are going to attach a database, this statement should be formed as the following:

CREATE DATABASE database_name
    ON <filespec> [ ,...n ]
    FOR ATTACH [;]

The database_name is the name that you wish to give to the database. In addition, this statement takes filegroups of the database files.

Keep in mind that the database name should not be exist in the server or the function would fail.

The following example shows how you can attach the database database.mdf to the server and give it the name MyDatabase:

    SqlConnection conn = new SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI");
    SqlCommand cmd = new SqlCommand("", conn);

    cmd.CommandText =
        "CREATE DATABASE 'MyDatabase' ON " +
        "PRIMARY ( FILENAME =  'database.mdf' ) " +
        "FOR ATTACH";

    conn.Open();

    cmd.ExecuteNonQuery();

    cmd.Dispose();
    conn.Dispose();

If no log file (.LDF) can be found, SQL Server creates one for you.

The following code attaches the same database along with its log file. Just change the third line of the previous example that sets the command text with this line:

    cmd.CommandText =
        "CREATE DATABASE 'MyDatabase' ON " +
        "PRIMARY ( FILENAME =  'database.mdf' ), " +
        "FILEGROUP MyDatabase_Log ( FILENAME = 'database.ldf')" +
        "FOR ATTACH";

Check the MSDN documentation for the CREATE DATABASE statement here.

More about database files and filegroups can be found in the MSDN documentation here.

sys.sp_attach_db Stored Procedure

Another way that allows to attach a database to the server is the sys.sp_attach_db stored procedure. The definition of this stored procedure is as following:

sp_attach_db [ @dbname= ] 'dbname'
    , [ @filename1= ] 'filename_n' [ ,...16 ]

This function takes the database name as the first argument. In addition it accepts another 16 arguments (only the first is required) represent database files. The following code attaches the same database to the server. Again, just change the third line of the previous code to the following line:

    cmd.CommandText = "exec sys.sp_attach_db    MyDatabase,    'database.mdf'";

Check the MSDN documentation for the sys.sp_attach_db statement here.

sys.sp_attach_single_file_db Stored Procedure

This statement is the same as sys.sp_attach_db stored procedure. However, this statement accepts only one file, the database file.

Check the following code out:

    cmd.CommandText = "exec sys.sp_attach_single_file_db    MyDatabase,    'database.mdf'";

Check the MSDN documentation for the sys.sp_attach_single_file_db statement here.

Detaching Databases from the Server

Unlike attaching databases, you can detach a database from a server in only two ways:

DROP DATABASE statement
sys.sp_detach_db system stored procedure

DROP DATABASE Statement

This statement is used to remove one or more databases from SQL Server. It has the following syntax:

DROP DATABASE database_name [ ,...n ] [;]

The following code simply executes this statement against our database MyDatabase:

    SqlConnection conn = new SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI");
    SqlCommand cmd = new SqlCommand("", conn);

    cmd.CommandText = "DROP DATABASE MyDatabase";

    conn.Open();

    cmd.ExecuteNonQuery();

    cmd.Dispose();
    conn.Dispose();

Check the MSDN documentation for the sys.sp_attach_single_file_db statement here.

sys.sp_detach_db Stored Procedure

Huh, the last one. This stored procedure is used to detach a database from the server. It has the following syntax:

sp_detach_db [ @dbname= ] 'database_name'

It accepts a single argument, the database name. The following code removes our database, MyDatabase, from the server:

    SqlConnection conn = new SqlConnection("Server=(local);Data Source=;Integrated Security=SSPI");
    SqlCommand cmd = new SqlCommand("", conn);

    cmd.CommandText = "sys.sp_detach_db MyDatabase";

    conn.Open();

    cmd.ExecuteNonQuery();

    cmd.Dispose();
    conn.Dispose();

Check the MSDN documentation for the sys.sp_attach_single_file_db statement here.

Security Considerations

Every statement and stored procedure we discussed in this writing requires specific permissions. Check the MSDN documentation to get more information.

Up Next

Ebook Download

View all

Coding Principles

Read by 0 people

Download Now!

Learn

View all