October 4, 2006 at 2:53 pm
Hello all,
Using SQL Server 2005.
We have unique schemas in our DB for each client. The name of each schema is kept in a table called AppClient.
I would like to combine the data in a table called TrxBuilding for each client schema into a view.
If I knew what schemas existed before runtime I would do something like this:
SELECT whatever FROM schema1.TrxBuilding
UNION
SELECT whatever FROM schema2.TrxBuilding
UNION
.
.
.
The problem is I won't know how many schemas there are until runtime.
Is it possible to create the view using a cursor to loop over the schema names from AppClient?
Any thoughts on how to do this greatly appreciated!
JM
October 4, 2006 at 3:42 pm
I'd actually loop over INFORMATION_SCHEMA.TABLES to find your list, it's going to always be up to date whereas AppClient might not be.
DECLARE @sql NVARCHAR ( 4000 )
SET @sql = ''
SELECT @sql = ' SELECT whatever FROM ' + TABLE_SCHEMA + '.TrxBuilding UNION'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'TrxBuilding'
SET @sql = LEFT(@sql, LEN(@sql) - 5) -- Take out the final UNION.
October 5, 2006 at 2:18 pm
Aaron,
Thanks for your helpful reply!
I wrapped the creation of the view in a stored proc that uses a cursor to loop over INFORMATION_SCHEMA.TABLES for schemas other than dbo that contian TrxBuilding.
I build a string that contains the view creation command and then Exec (string) to create the view.
Wow cusors and dynamic sql in one stored proc. I am having a bad day!
JM
October 5, 2006 at 5:51 pm
I'd take dynamic SQL any day over cursors... 😉
You shouldn't need to use a cursor actually if you use the syntax I had above... except I left something out. Change the SELECT statement to this:
SELECT @sql = @sql + ' SELECT whatever FROM ' + TABLE_SCHEMA + '.TrxBuilding UNION'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'TrxBuilding'
My bad on that one, was working too fast.
October 6, 2006 at 8:27 am
>>I'd take dynamic SQL any day over cursors... 😉 <<
I wouldn't. Do you know that your code is easy target for sql injection ?
Also, I would use "union all" instead of "union" for what you are trying to do
Cheers,
* Noel
October 6, 2006 at 8:35 am
noeld,
I am aware of the sql injection risk with dynamic sql.
I did use UNION ALL, I should have mentioned that.
The UNION ALL led to another question I posted this morning concerning partitioned views in this forum.
Thanks!
JM
October 9, 2006 at 4:34 pm
Noel, I avoid using either for production applications. 😉 On the off-situation where I have had to use dynamic sql I whitelist any text-based input.
And no, my example is not injectable. You'd have to be able to insert data into a system catalog or already have CREATE SCHEMA/CREATE TABLE granted. If you've got access to do that, there's not a lot more that injecting code is going to get you. And if you're giving CREATE SCHEMA to your application users, I'd imagine that's probably the least of your problems.
However my assumption from his example is that this is a one-off situation which he needs to execute against varying client databases. In such a situation it's probably best to avoid the bad habit of looping techniques.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply