March 24, 2016 at 10:46 am
Hello --
We have twenty databases utilized by an application, and I want to be able to automatically query all the databases. Currently I need to interactively go to each database, and run the query. The query in question is the following:
select * from RS_Patient_6 where PatientID_6_660 = ‘<patient id>’
What syntax do I need in order to have the query go all of the databases?
Thanks.
March 24, 2016 at 10:53 am
select * from RS_Patient_6
where MyDatabase.MySchema.PatientID_6_660 = ‘<patient id>’
John
March 24, 2016 at 11:00 am
Hello --
Thank-you for your reply. At the risk of looking like an idiot, the syntax that I used was the following:
select * from RS_Patient_6
where localhost\RAYCLINICAL.PatientID_6_660 = '<patient id>'
The output that I got was the following:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '\'.
Where is my mistake?
March 24, 2016 at 1:10 pm
kaplan71 (3/24/2016)
Hello --Thank-you for your reply. At the risk of looking like an idiot, the syntax that I used was the following:
select * from RS_Patient_6
where localhost\RAYCLINICAL.PatientID_6_660 = '<patient id>'
The output that I got was the following:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '\'.
Where is my mistake?
You don't specify the name of the server in the query. You can specify a linked server, but that's a pointer to an instance, not a physical server. If they're all on the same instance, you can use the 3-part naming convention like John showed - database.schema.table.
BTW, you want to do this in the FROM clause. So, using 1400 as an example PatientID:
SELECT *
FROM databasename.schema_name.RS_Patient_6
WHERE PatientID_6_660 = 1400;
March 24, 2016 at 1:35 pm
Hello --
I am still not making any progress on this issue. The syntax that I entered is the following:
SELECT *
FROM databasename.schema_name.RS_Patient_6
WHERE PatientID_6_660 = 577735
The output I am getting is the as follows:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'databasename.schema_name.RS_Patient_6'.
March 24, 2016 at 1:44 pm
kaplan71 (3/24/2016)
Hello --I am still not making any progress on this issue. The syntax that I entered is the following:
SELECT *
FROM databasename.schema_name.RS_Patient_6
WHERE PatientID_6_660 = 577735
The output I am getting is the as follows:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'databasename.schema_name.RS_Patient_6'.
In the example query:
1. You have to replace databasename with the name of your database.
2. You have to replace schema_name with the name of your schema.
3. If RS_Patient_6 is not the right table name, you have to replace it with the name of your table.
For example, if your database is named DB1 and you're RS_Patient_6 table is in the default schema of dbo, you would use the following:
SELECT *
FROM DB1.dbo.RS_Patient_6
WHERE PatientID_6_660 = 577735;
March 24, 2016 at 1:51 pm
Hello --
I used the following syntax to complete the query:
SELECT *
FROM RayStationPatientDB_Breast_4014.dbo.RS_Patient_6
WHERE PatientID_6_660 = 577735;
The output was as follows:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'phantom' to data type int.
The other problem that I see is that this is going through only one database. I want to be able to do a simultaneous query on all of the databases.
March 24, 2016 at 1:57 pm
kaplan71 (3/24/2016)
Hello --I used the following syntax to complete the query:
SELECT *
FROM RayStationPatientDB_Breast_4014.dbo.RS_Patient_6
WHERE PatientID_6_660 = 577735;
The output was as follows:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'phantom' to data type int.
The other problem that I see is that this is going through only one database. I want to be able to do a simultaneous query on all of the databases.
Okay, this is good. You're getting somewhere. The column PatiendID_6_660 is an nvarchar column, so you'll need to make sure the value in your WHERE clause predicate is the same type.
Try this:
SELECT *
FROM RayStationPatientDB_Breast_4014.dbo.RS_Patient_6
WHERE PatientID_6_660 = N'577735';
Does this work for you?
March 24, 2016 at 2:05 pm
Actually I made a mistake in my syntax. I had not enclosed the id number in quotes.
Here is the correct syntax:
SELECT *
FROM RayStationPatientDB_Breast_4014.dbo.RS_Patient_6
WHERE PatientID_6_660 = '577735';
The output was empty, which means the patient was not in that particular database. The question now is the following: Can the above syntax be modified to read as follows:
SELECT *
FROM RayStationPatientDB_Breast_4014.dbo.RS_Patient_6
FROM RayStationPatientDB_CNS_4014.dbo.RS_Patient_6
FROM RayStationPatientDB_GI_4014.dbo.RS_Patient_6
...
WHERE PatientID_6_660 = '577735';
To allow the query to go through all the databases?
March 24, 2016 at 2:27 pm
kaplan71 (3/24/2016)
Actually I made a mistake in my syntax. I had not enclosed the id number in quotes.Here is the correct syntax:
SELECT *
FROM RayStationPatientDB_Breast_4014.dbo.RS_Patient_6
WHERE PatientID_6_660 = '577735';
The output was empty, which means the patient was not in that particular database. The question now is the following: Can the above syntax be modified to read as follows:
SELECT *
FROM RayStationPatientDB_Breast_4014.dbo.RS_Patient_6
FROM RayStationPatientDB_CNS_4014.dbo.RS_Patient_6
FROM RayStationPatientDB_GI_4014.dbo.RS_Patient_6
...
WHERE PatientID_6_660 = '577735';
To allow the query to go through all the databases?
The first thing to determine is if you really want to query all databases. My guess is that you don't want to query master, model and msdb. You might have others you don't want to query either. Can you produce a list of databases that you do need to query?
I'm thinking of something along these lines:
SELECT name
FROM sys.databases
WHERE name LIKE 'RayStationPatientDB%'
ORDER BY name;
Also, do you want to have a single list or a bunch of individual queries as your result set?
If you want a single result set, are all the dbo.RS_Patient_6 tables identical?
I'm talking you through this step by step so you understand how to do it next time and you can support it when it gets done. I'll warn you ahead of time that this is going to involve a loop.
March 24, 2016 at 2:36 pm
Hello --
The databases in question are the following:
RayStationPatientDB_Breast_4014
RayStationPatientDB_CNS_4014
RayStationPatientDB_Emerson_4014
RayStationPatientDB_GI_4014
RayStationPatientDB_GU_4014
RayStationPatientDB_GYN_4014
RayStationPatientDB_HN_4014
RayStationPatientDB_Lymphoma_4014
RayStationPatientDB_NWH_4014
RayStationPatientDB_Other_4014
RayStationPatientDB_Pediatric_4014
RayStationPatientDB_Physics_Research_4014
RayStationPatientDB_Sarcoma_4014
RayStationPatientDB_Skullbase_4014
RayStationPatientDB_SRS_4014
RayStationPatientDB_Thoracic_4014
More than likely it will be a single query at a given time, and all the dbo.RS_Patient_6 tables are identical.
March 24, 2016 at 4:58 pm
Okay, I see two possible solutions.
There is the option to create a view in a centralized database that does a SELECT against the dbo.RS_Patient_6 table in each one of these databases. The results would be UNION ALLed together. This could also be done in an inline table-valued function. If you ever add another database, the view or ITVF would need to be updated to include the new one. This would be pretty simple to create, but I don't poor performance I anticipate would make it a viable option.
The option I'm guessing you'll like better is a stored procedure to query each dbo.RS_Patient_6 table in each one of these databases. I've written it to produce a single result set and include the name of the database where the patient was found, but this could be updated to produce independent result sets for each query. If it was me, I would want a single result set, so this is the way I wrote it.
I don't have the structure for your dbo.RS_Patient_6 tables, so I couldn't test this. You're going to have to update the structure of the #results temp table and the columns for your INSERT and SELECT statement that gets executed in each database. You can use the PRINT statement instead of the EXECUTE statement to debug your statements. This should get you most of the way there.
if object_id('dbo.FindPatient', 'p') is not null drop procedure dbo.FindPatient;
go
CREATE PROCEDURE dbo.FindPatient(@PatientID Nvarchar(64))
AS
DECLARE @dbs Nvarchar(64),
@strSQL Nvarchar(255);
DECLARE crDBs CURSOR FAST_FORWARD READ_ONLY FOR
SELECT x.name
FROM (VALUES('RayStationPatientDB_Breast_4014'),
('RayStationPatientDB_CNS_4014'),
('RayStationPatientDB_Emerson_4014'),
('RayStationPatientDB_GI_4014'),
('RayStationPatientDB_GU_4014'),
('RayStationPatientDB_GYN_4014'),
('RayStationPatientDB_HN_4014'),
('RayStationPatientDB_Lymphoma_4014'),
('RayStationPatientDB_NWH_4014'),
('RayStationPatientDB_Other_4014'),
('RayStationPatientDB_Pediatric_4014'),
('RayStationPatientDB_Physics_Research_4014'),
('RayStationPatientDB_Sarcoma_4014'),
('RayStationPatientDB_Skullbase_4014'),
('RayStationPatientDB_SRS_4014'),
('RayStationPatientDB_Thoracic_4014')
) x (name);
BEGIN
IF OBJECT_ID('temp.dbo.#results', 'u') IS NOT NULL DROP TABLE #results;
CREATE TABLE #results (
DBS Nvarchar(255),
PatientID_6_660 Nvarchar(64)
--TODO: finish your table structure to include the columns you need to query
);
OPEN crDBs;
FETCH NEXT FROM crDBs INTO @dbs;
WHILE @@FETCH_STATUS = 0
BEGIN;
--TODO: update the columns you need to query
SET @strSQL = 'INSERT INTO #results(DBS, PatientID_6_660) ' +
'SELECT ''' + @dbs + ''', PatientID_6_660 ' +
'FROM ' + QUOTENAME(@DBS) + '.dbo.RS_Patient_6 ' +
'WHERE PatientID_6_660 = ''' + @PatientID + ''';';
--PRINT @strSQL;
EXECUTE (@strSQL);
FETCH NEXT FROM crDBs INTO @dbs;
END;
CLOSE crDBs;
DEALLOCATE crDBs;
--select the return result set
SELECT *
FROM #results;
DROP TABLE #results;
END;
go
/* testing:
execute dbo.FindPatient '12345';
*/
I hope this helps.
March 25, 2016 at 6:33 am
Hello --
I have the list of columns within the table that will be queried, and I can insert them into the script at the two TODO locations. Is this the correct syntax to use:
,[item 1],[item 2],[item 3]
Thanks.
March 25, 2016 at 7:04 am
Probably the best approach here is to use UNION ALL. Then the query would be the same query, repeated, over and over again, for each of your databases.
My question is, are you looking at this as a one-off, "I need to do something right now", or is it going to be a "I'll be using this as my common query pattern across all the databases all the time"? If it's the latter, you're going to have tons of issues querying your system and you may need to rethink your approach entirely to the database layouts and design.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 25, 2016 at 12:01 pm
I understand you want to run an identical quiery across multiple databases on the same instance. By leveraging sp_MSForEachDB you can execute a batch of t-sql across all databases. What the t-sql does is first set context to database (? is parameter for database name), then checks for existence of 'RS_Patient_6' table, and if exists then it will execute the query.
exec sp_MSForEachDB
'
USE ?;
if exists
(select table_name
from information_schema.tables
where table_name = ''RS_Patient_6'')
begin;
select * from RS_Patient_6
where PatientID_6_660 = ''<patient id>''
end;
';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply