November 5, 2008 at 2:01 pm
The SQL Server host holds several databases for an application. Some of them are defined the same as the PRODUCTION database: there is at least DEVELOPMENT and TRAINING. Some might be added later on.
There are also other databases which have nothing to do with the application.
In each of the application's databases, there is a table, say [font="Courier New"]License[/font], with a column [font="Courier New"]License_No[/font] which must have a UNIQUE value which CANNOT be the same as the values used for the same column in the other application databases.
[font="Courier New"]CREATE TABLE License
. (
. pkID int NOT NULL PRIMARY KEY IDENTITY,
. License_No varchar(10) NULL
. )[/font]
When a value is entered in column [font="Courier New"]License_No[/font], the application must verify that this value is not used in other database.
How can this be done other than using a WHILE statement to loop through all databases, check for the existence of the [font="Courier New"]License[/font] table and select [font="Courier New"]License_No[/font] ?
For each application database, the License table has only ONE record. The application must be able to find out on the fly the list of databases since the names could change or new databases could be added.
Any ideas ?
November 6, 2008 at 10:02 am
You explicitly said, "without a loop". Given this constraint I don't think that it's possible. Why can't you use a loop?
If a loop was possible I would do the following:
create procedure proc_findLicenseInAllDB @license varchar(250) AS
-- Create a temp table to hold db names
-- loop over db names
-- for each build dynamic SQL to see if the table exists in the catalog
-- If it exists build dynamic SQL to query the existence of the license
-- handle if the license ID is found
November 6, 2008 at 10:07 am
After sleeping over it, I am now considering creating a database that will only hold the table. That database can have a name known in advance and that will not change.
Since I would have to query other databases (as in my original post), I might as well just query ONE database that the other databases can also query.
This should be simpler.
Regards
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply