How to reuse a cursor, pointing it at a different db each loop?

Question

I would like to modify an existing procedure to run multiple times using a slightly different cursor each time. The procedure--see below--is short and simple and it only needs to be pointed at a max of 3 different db's so it would be easy to just hard code with mostly duplicated SQL. On the other hand I would like to know what the grown-up way to do this.

My guess would be to increment a counter within a loop and set a database name variable to point to a different database when the cursor is declared anew with each iteration. Questions:

1. How would I set a variable to a database name? Would I have to use the dreaded QUOTENAME function?

2. Is there a more elegant way to repeat the procedure 2 or 3 times than this?

USE [dbONE]
GO
DECLARE @TableName	SYSNAME
DECLARE curTables CURSOR FOR
	SELECT		Table_Name
	FROM		INFORMATION_SCHEMA.TABLES
	WHERE		TABLE_TYPE = 'BASE TABLE'
	AND			TABLE_SCHEMA = 'dbo'
OPEN curTables
FETCH NEXT FROM curTables INTO @TableName
WHILE @@FETCH_STATUS = 0 
BEGIN
INSERT INTO SystemAnalytics.dbo.Record_Counts(Record_Count, Table_Name, Data_Source, Database_Name)
EXECUTE ('SELECT COUNT(*) ,''' +  @TableName + ''', ''SourceOne'', ''dbONE'' FROM ' + @TableName)
FETCH NEXT FROM curTables INTO @TableName
END
CLOSE curTables
DEALLOCATE curTables

---

HomeCookN

Answer 1

A simple solution, in line with what you are doing, is to run a cursor in the master database that pulls each database from sys.databases.  You can then feed each database into code above.

I'm not sure what you have against QUOTENAME though.  It's very useful for protecting against errors caused by special characters in the object names, and when used properly in dynamic sql can protect against some forms of sql injection.

Answer 2

Thanks, Mike. Glad to know I'm more or less on the right path.

I don't have anything against QUOTENAME; I'm just not smart enough to figure out how to use it yet I guess. The single QUOTENAME example under than heading in BOL made no sense to me:

SELECT QUOTENAME('abc[]def')
Here is the result set.
[abc[]]def]

---

HomeCookN

Answer 3

Thanks, Mike. Glad to know I'm more or less on the right path.

I don't have anything against QUOTENAME; I'm just not smart enough to figure out how to use it yet I guess. The single QUOTENAME example under than heading in BOL made no sense to me:

SELECT QUOTENAME('abc[]def')
Here is the result set.
[abc[]]def]

---

HomeCookN

All the function does is wrap the string in identifiers, defaulted to square brackets.  The example above just shows what happens when you use the function on a string that contains the identifier - the result being that it escapes the character where necessary.  In this example the second left bracket ([) can remain the same, but the adjacent right bracket (]) needs to be escaped so that it is parsed correctly.  This is done by doubling up the bracket.

In most cases it will simply wrap the text in square brackets.  So imagine for example you have a stored proc that takes a table name as a parameter, and builds and runs dynamic sql using the table, e.g.

-- @table is passed as 'Employee'
set @sql = 'select count(*) from '+@table;

-- runs "select count(*) from Employee"
exec(@sql);

Now say someone passes the following string as the table name - 'sys.tables; drop table dbo.Employee;' - you see what could happen?  It's a simple example but demonstrates the dangers of dynamic sql.  However, let's wrap the table name in the QUOTENAME function:

-- @table is passed as 'Employee'
set @sql = 'select count(*) from '+quotename(@table);

-- runs "select count(*) from [Employee]"
exec(@sql);

If the hacker tries to insert the text above, they'll execute the following query:

select count(*) from [sys.tables; drop table dbo.Employee;]

which will return an error as the table does not exist.

I try to use it as much as possible in these situations, even if I have total control over the dynamic sql.  Always a good habit to get into :-)

Answer 4

Well, that's a very cool explanation, Mike, a zillion times more detailed and informative than the BOL article. Thank you for your time in helping me learn something.

---

HomeCookN