473,597 Members | 2,761 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Dynamic SQL - A Quick Tutorial

533 Recognized Expert Contributor
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_SCH EMA.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
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_SCH EMA.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.

Sep 25 '08 #1
0 7628

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

Similar topics

by: murrayatuptowngallery | last post by:
Hello: I previously posted a question about how to do populate an html table dynamically with results from JavaScript Math and basic math. Dr Clue responded and this started the learning curve. I think the terminology for what I want to do is pass variables to the <td> 'workspace' (half this statement might make sense). The problem I am having is being overwhelmed with understanding the context of each new statement, syntax etc.
by: Liddle Feesh | last post by:
Hi, I need a quick tutorial to help me upgrade a simple client-server application currently using ADO to VB.NET (and ADO.NET). Does anyone have a favourite link? --
by: David Thielen | last post by:
Hi; Almost all of the Quick Starts show the code in the .aspx file inside a <script> instead of in a seperate .aspx.cs file. My instinct is that the code should be in a seperate file to keep the view and the view logic distinct. Is there a strong argument for combining these in one file? (It may just feel wrong to me because of years of J2EE/JSP programming where you always keep them seperate.)
by: Nospam | last post by:
Is there a tutorial on dynamic SID generation?
by: Joey Sabey | last post by:
How would you go about making an array thats size must increase as new data is inputted to the program? As in, the size will never be known before input stops. Can you easily increase the size of a dynamically created array? Thanks, ~Joey~
by: Tarscher | last post by:
hi all, I have this seemingly simple problem. I have lost a lot of time on it though. When a user selects a value from a dropdownlist (static control) a dynamic control is generated. I have to create the dynamic controls in the OnInit stage of the lifecycle. Since data from static controls is not yet available in the OnInit stage I can't know what dynamic control I have to create.
by: MiG | last post by:
Hello all, I want to make a dynamic 4 drop down menus with AJAX and PHP/mySQL, like this: Category->dynamic subCategory County->dynamic cityCounty Is there any good tutorial that can teach me how to do this? I managed
by: marcroy.olsen | last post by:
Hi list and python gurus :-) I'm playing with some mod_python and web development. And in me code I need to do som dynamic imports. Right now I just do a: exec 'import '+some_modulename But it seems to easy, is there a "dark side" to doing it this way? (memory use,processing ,etc)
by: bearophileHUGS | last post by:
I often use Python to write small programs, in the range of 50-500 lines of code. For example to process some bioinformatics data, perform some data munging, to apply a randomized optimization algorithm to solve a certain messy problem, and many different things. For that I often use several general modules that I have written, like implementation of certain data structures, and small general "utility" functions/classes, plus of course...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.