Some Important SQL Queries

As a C# developer, I much prefer to perform this kind of task with SMO. The API is really nicely structured and gives quick, strongly-typed access to the nuts and bolts of SQL Server.

Using SQL 2008 and Visual Studio 2010, you need to add the following references from C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies to your project:

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.ManagementSdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum

The first thing is to create a connection to the server you want to look at. There are a number of overloads that specify full connection strings, usernames, passwords and the familiar SqlConnection for the ServerConnection constructor, but this example will assume you're running as the local admin for simplicity's sake:

Gets a reference to a SQL Server Instance:

private Server GetServer(string serverName)
{
    var conn = new ServerConnection(serverName);

    return new Server(conn);
}

Gets the tables for a given database:

public TableCollection GetTables(string serverName, string databaseName)
{
    var server = this.GetServer(serverName);

    var database = server.Databases[databaseName];

    return database.Tables;
}

Gets the stored procedures for a given database:

public StoredProcedureCollection GetStoredProcedures(string serverName, string databaseName)
{
    var server = this.GetServer(serverName);

    var database = server.Databases[databaseName];

    return database.StoredProcedures;
}

Gets a filtered list of the stored procedures for a given database:

public IEnumerable<StoredProcedure> GetStoredProcedures(string serverName, string databaseName, Func<StoredProcedure, bool> filter)
{
    var storedProcedures = this.GetStoredProcedures(serverName, databaseName);

    return storedProcedures.Cast<StoredProcedure>()
       .Where(filter)
       .ToList();
}

Call to get stored procedures created in the last 5 days:

var storedProceduresInLast5Days = manager.GetStoredProcedures("SERVERNAME", "DATABASENAME", sp => sp.CreateDate >= DateTime.Now.Subtract(new TimeSpan(5, 0, 0, 0)));

Gets a collection of user defined functions for a given database:

public UserDefinedFunctionCollection GetUserDefinedFunctions(string serverName, string databaseName)
{
    var server = this.GetServer(serverName);

    var database = server.Databases[databaseName];

    return database.UserDefinedFunctions;
}