Dynamic SQL - A Quick Tutorial

[JamieHowarth0]

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 initial catalog. 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.

Without further ado, here's the code:

CREATE PROCEDURE GetCounterStrikeServerDetails AS
BEGIN
    CREATE TABLE #tmp (
        tmpdbname nvarchar(MAX)
    )
    DECLARE @useronline nvarchar(MAX)
    DECLARE @dbname nvarchar(255)
    DECLARE @csdb nvarchar(255) 
    DECLARE @sql nvarchar(MAX)
    DECLARE dbcur CURSOR FOR
    (SELECT name FROM sys.databases)
    OPEN dbcur
    FETCH NEXT FROM dbcur INTO @dbname
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'IF (SELECT COUNT(*) FROM ' + @dbname + '.INFORMATION_SCHEMA.tables WHERE TABLE_NAME = ''cs_servers'') = 1 INSERT INTO #tmp SELECT ''' + @dbname + ''''
        EXEC(@sql)
        FETCH NEXT FROM dbcur INTO @dbname
    END
    CLOSE dbcur
    DEALLOCATE dbcur
    SET @csdb = CONVERT(nvarchar(max), (SELECT tmpdbname FROM #tmp))
    DROP TABLE #tmp
    SET @useronline = 'SELECT name, cur_players, cur_map, maxplayers FROM ' + @csdb + '.dbo.cs_servers '
    SET @useronline = @useronline + 'INNER JOIN ' + @csdb + '.dbo.cs_params ON ' + @csdb + '.dbo.cs_params.server_id = ' + @csdb + '.dbo.cs_servers.id'
    EXEC(@useronline)
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 24-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.

medicineworker