469,648 Members | 1,172 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Dynamic SQL - A Quick Tutorial

JamieHowarth0
533 Expert 512MB
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:
Expand|Select|Wrap|Line Numbers
  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
  16.         SET @sql = 'IF (SELECT COUNT(*) FROM ' + @dbname + '.INFORMATION_SCHEMA.tables WHERE TABLE_NAME = ''cs_servers'') = 1 INSERT INTO #tmp SELECT ''' + @dbname + ''''
  17.         EXEC(@sql)
  18.         FETCH NEXT FROM dbcur INTO @dbname
  19.     END
  20.     CLOSE dbcur
  21.     DEALLOCATE dbcur
  22.     SET @csdb = CONVERT(nvarchar(max), (SELECT tmpdbname FROM #tmp))
  23.     DROP TABLE #tmp
  24.     SET @useronline = 'SELECT name, cur_players, cur_map, maxplayers FROM ' + @csdb + '.dbo.cs_servers '
  25.     SET @useronline = @useronline + 'INNER JOIN ' + @csdb + '.dbo.cs_params ON ' + @csdb + '.dbo.cs_params.server_id = ' + @csdb + '.dbo.cs_servers.id'
  26.     EXEC(@useronline)
  27. END
  28.  
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
Sep 25 '08 #1
0 7177

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Liddle Feesh | last post: by
5 posts views Thread by David Thielen | last post: by
15 posts views Thread by Nospam | last post: by
11 posts views Thread by Joey Sabey | last post: by
9 posts views Thread by Tarscher | last post: by
5 posts views Thread by marcroy.olsen | last post: by
5 posts views Thread by bearophileHUGS | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.