Geeks With Blogs
Benjamin Howarth, Code Gecko

I'm about to set up a new website, powered by DotNetNuke (my favourite CMS at the moment, mainly because it's free), and I want to install a Counter-Strike game server onto the same machine. I also want a little module that will show visitors to my site how many people are logged onto the CS server and some basic stats.
Sounds simple right?
The problem is that when Counter-Strike server is installed, the database name is generated at random. So I can't create a connection string that points to a data source because I don't know the name of the database. However, I do know what the CS tables look like.
In SQL Server, there's no way of obtaining all the table names in all the database just by running one query. So, I created a bit of code which:

  1. Gets all the databases on the server;
  2. Queries the INFORMATION_SCHEMA.tables table, which holds the names of all the tables in the database;
  3. Return the name of the database which contains the CS tables (in this case, cs_server).

The following piece of code does just that using dynamic SQL - SQL that writes itself, so to speak.

Bear in mind that when you run this, you must have SELECT access on all the databases on your DB server otherwise you will get "access denied" errors and the whole procedure will fail (so basically, run it as Windows Administrator or database sa).

Without further ado, here's the code:

   1:  CREATE PROCEDURE GetCounterStrikeServerDetails AS 
   2:  BEGIN  
   3:  CREATE TABLE #tmp ( 
   4:  tmpdbname nvarchar(MAX) 
   5:  ) 
   6:  DECLARE @useronline nvarchar(MAX) 
   7:  DECLARE @dbname nvarchar(255) 
   8:  DECLARE @csdb nvarchar(255) 
   9:  DECLARE @sql nvarchar(MAX) 
  10:  DECLARE dbcur CURSOR FOR 
  11:  (SELECT name FROM sys.databases) 
  12:  OPEN dbcur 
  13:  FETCH NEXT FROM dbcur INTO @dbname 
  14:  WHILE @@FETCH_STATUS = 0 
  15:  BEGIN
  17:  SET @sql = 'IF (SELECT COUNT(*) FROM ' + @dbname + '.INFORMATION_SCHEMA.tables WHERE TABLE_NAME = ''cs_servers'') = 1 INSERT INTO #tmp SELECT ''' + @dbname + '''' 
  18:  EXEC(@sql) 
  19:  FETCH NEXT FROM dbcur INTO @dbname 
  20:  END 
  21:  CLOSE dbcur 
  22:  DEALLOCATE dbcur 
  23:  SET @csdb = CONVERT(nvarchar(max), (SELECT tmpdbname FROM #tmp)) 
  24:  DROP TABLE #tmp 
  25:  SET @useronline = 'SELECT name, cur_players, cur_map, maxplayers FROM ' + @csdb + '.dbo.cs_servers ' 
  26:  SET @useronline = @useronline + 'INNER JOIN ' + @csdb + '.dbo.cs_params ON ' + @csdb + '.dbo.cs_params.server_id = ' + @csdb + '' 
  27:  EXEC(@useronline) 
  28:  END 




Let's walk through this code.

Firstly, I declared a temp table (lines 3-5). Reason is because I had trouble with variable scope when trying to get the name of the database, which is on line 16.

Secondly, declare a number of variables to hold the data that I'll be working with (lines 6-9) - @useronline and @sql to hold my dynamically-generated SQL code, @dbname for my cursor to loop through each database, and @csdb for the name of the actual Counter-Strike database when I discover it.

Third, open a cursor, which loops through every database listed in sys.databases (lines 10-14).

Line 16 is the key. For each database, if I find an entry in INFORMATION_SCHEMA.tables which matches the name of the table I'm after (cs_server), I want to insert a row into my temp table with the name of the database.

Lines 20 and 21 are garbage collection for the cursor.
Lines 22 and 23 simply get the name of the CS database and put it into a variable, and dropping the temp table (garbage collection).
Lines 24 and 25 builds a SELECT JOIN statement using the CS database name and known table names for the CS database structure.
Line 26 executes said SELECT JOIN statement, which gives you the server name, max number of users allowed, current map, and number of users currently connected and playing.

If you take out lines 22-26, this code could be adapted for a number of uses - for example, in software asset management scenarios, this could be used to find particular pieces of software installed on a database server. I am running at least 3 DotNetNuke databases and if I know one of them has got a particular piece of software on it, and I want to delve into it, I'd be using this script.

I hope that someone finds it useful!


Posted on Sunday, September 28, 2008 12:07 AM DotNetNuke | Back to top

Comments on this post: Database discovery and dynamic SQL in SQL Server 2005

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © TheInspiredGecko | Powered by: