Query from multiple databases - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query from multiple databases

Author

Topic

happyneil
Starting Member

1 Post

Posted - 2011-05-01 : 06:15:54

Hi there,

I have 5 databases in Microsoft SQL Server Management Studio. Each has a name such as V1_database??? (e.g. V1_database_york, V1_database_vegas etc). How would I go about querying these databases?

At the moment I am only querying out of one database... Here is part of an include file...


<?php
/*database connection */
$serverName = ".\SQLEXPRESS";
$connectionOptions = array("Database"=>"V1_database_york",
         "UID"=>"username",
         "PWD" => "password",
		 "ReturnDatesAsStrings" => true,
		 "ConnectionPooling" => true);
		 
 /* Connect using Windows Authentication */
$conn = sqlsrv_connect($serverName, $connectionOptions);
?>

How do I change this include file to include any database starting with V1_database???? Any advice would be greatly appreciated!

Cheers,

Neil

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-01 : 07:57:48

Assuming the user has proper permissions, if all the databases are located on the same server, you can query other databases with the same connection using three-part naming convention in your queries. For example, with the connection string you have in your example which connects to V1_database_york, you can query V1_database_vegas like this:

select top 10 casino_name from V1_database_vegas.dbo.Casinos

You could even query databases on other servers if they are linked using four-part naming convention of server_name .[database_name].[schema_name].object_name. This page has a description of the four-part naming convention: http://msdn.microsoft.com/en-us/library/ms177563.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources