Click here to Skip to main content
Click here to Skip to main content
Alternative Tip/Trick

Tagged as

Some Important SQL Queries

, 6 Nov 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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:
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;
}

License

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

Share

About the Author

jim lahey
Software Developer (Senior)
United Kingdom United Kingdom
First batch file in 1987
 
Messed around with the Bullfrog C++ Libraries for Syndicate in 1994
 
Web Developer since 2000
 
.net Developer since 2001
 
MCTS: Microsoft® .NET Framework 2.0 - Web-based Client Development
 
MCTS: Web Applications Development with Microsoft .NET Framework 4

Comments and Discussions

 
GeneralReason for my vote of 5 cool Pinmembercid_moossaa2-Nov-11 18:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141022.2 | Last Updated 6 Nov 2011
Article Copyright 2011 by jim lahey
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid