Database discovery and dynamic SQL in SQL Server 2005By Benjamin Howarth on
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:
- Gets all the databases on the server;
- Queries the INFORMATION_SCHEMA.tables table, which holds the names of all the tables in the database;
- 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:
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) -
@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!