Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Some Important SQL Queries

5.00/5 (1 vote)
6 Nov 2011CPOL 10.2K  
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...
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:
C#
private Server GetServer(string serverName)
{
    var conn = new ServerConnection(serverName);

    return new Server(conn);
}


Gets the tables for a given database:
C#
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:
C#
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:
C#
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:
C#
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:
C#
public UserDefinedFunctionCollection GetUserDefinedFunctions(string serverName, string databaseName)
{
    var server = this.GetServer(serverName);

    var database = server.Databases[databaseName];

    return database.UserDefinedFunctions;
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)