Query Across Multiple Databases on Same Server

Query across multiple databases on same server

It's not going to be the cleanest solution ever, but you could define a view on a "Master database" (if your individual databases are not going to stay constant) that includes the data from the individual databases, and allows you to execute queries on a single source.

For example...

CREATE VIEW vCombinedRecords AS
SELECT * FROM DB1.dbo.MyTable
UNION ALL
SELECT * FROM DB2.dbo.MyTable

Which allows you to do...

SELECT * FROM vCombinedRecords WHERE....

When your databases change, you just update the view definition to include the new tables.

Query against two tables in separate databases on the same server

Sounds like you mistyped something. You can query a table in another DB using the following method:

SELECT tn.ID, tn.NAME
FROM [Database Name].[Schema].[TableName] as tn

I purposely added a two word database name because you have to put square brackets around that for it to be recognized. Your Schema will most likely be dbo.

If you show us your query and give us the DB names I can provide a more complete answer.

UPDATE:

Are you sure you are spelling "Center" correctly? I noticed you spelled it "centre" in IIPCentre_UserObject which I think might be right for the UK (?) but you spelled it "center" for QUALITY_CENTER. I would assume it's spelled one way or the other in your environment.

Querying multiple databases on the same server

You can move the check into your dynamic SQL.

Example

DECLARE @Qry NVARCHAR(MAX) = '
    USE ' + QUOTENAME(@DB) + ';

    IF EXISTS (SELECT 1 FROM sys.columns...) 
        BEGIN
            ...
        END
    ELSE
        BEGIN
            ...
        END
';

EXECUTE(@Qry);

USE cannot be combined with a variable, outside of dynamic sql, making the statement USE @DB invalid. This is because @DB is a string (NVARCHAR(128)) and USE expects a database. Unfortunately you cannot directly parametrise databases.

I've added the functon QUOTENAME to help prevent SQL injection attacks.

SQL query to sum up count across multiple databases on same host

You have to use a prepared statement to get at the desired result:

SELECT
  GROUP_CONCAT(
    CONCAT(
      '(SELECT count(id) FROM `',
      name,
      '`.`hosts`)') SEPARATOR ' + ')
FROM
  db1.companies
WHERE
  status = 'active'
INTO @sql;

SET @sql := CONCAT('SELECT ', @sql);

SELECT @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

Output from SELECT @sql:

@sql
-------------------------------------------------------------------------
SELECT (SELECT count(id) FROM `companyA`.`hosts`) + 
       (SELECT count(id) FROM `companyB`.`hosts`) + 
       (SELECT count(id) FROM `companyC`.`hosts`)

So, @sql variable holds the dynamic sql statement that needs to be executed in order to obtain the desired result.

Demo here

SQL - Select from Multiple databases

I'd create a view which combines the select statements. e.g.

CREATE VIEW v_ICITEM
AS
SELECT * FROM CN2DAT.dbo.ICITEM
UNION ALL 
SELECT * FROM AU1DAT.dbo.ICITEM
go;

You could include the source database as a column also:

CREATE VIEW v_ICITEM
AS
SELECT 'CN2DAT' AS Db, * FROM CN2DAT.dbo.ICITEM
UNION ALL 
SELECT 'AU1DAT', * FROM AU1DAT.dbo.ICITEM
go;

How can I reference tables from multiple databases within the same server in a common table expression (CTE)?

There are lots of ways to do this..

You could read the tables in one database into a temp table on the second database and then join to it.. or join both of them on the fly.

but first.. refrain from doing select *.. specify the columns

You could go 

select t1.column1,t2.column2

from UniversalData.dbo.someTable t1

inner join Site01Data.dbo.anotherTable t2
 on t2.ida = t2.idx

and so onn.. it depends on which way you want to specify the join and what sort of join you want to choose..

This assumes that both the data bases are on the same instance.. else you will need linked servers

Use an initial query to merge queries across multiple databases?

To run non-trivial queries across databases, based on an initial query:

  1. Figure out the common key in all databases. In this case it's AccountId (which is a user's Stack-Exchange-wide Id).

  2. Create your initial query to feed that key into a temp table. In this case:

    CREATE TABLE #UsersOfInterest (AccountId INT)
    INSERT INTO  #UsersOfInterest  
        SELECT  u.AccountId
        FROM    Users u
        Where   u.Reputation > 200000
  3. Create Another temp table to hold the final results (see below).

  4. Determine the query, to run on each site, that gets the info you want. EG:

    SELECT  u.AccountId, u.DisplayName, u.Reputation, u.Id
            , numQst = (SELECT COUNT(q.Id)  FROM Posts q  WHERE q.OwnerUserId = u.Id  AND q.PostTypeId = 1)
            , numAns = (SELECT COUNT(q.Id)  FROM Posts q  WHERE q.OwnerUserId = u.Id  AND q.PostTypeId = 2)
    FROM    Users u
    WHERE   u.AccountId = ##seAccntId##

  5. Use a system query to get the appropriate databases. For the Data Explorer (SEDE), a query of this type:

    SELECT      name
    FROM        sys.databases
    WHERE       CASE    WHEN state_desc = 'ONLINE'
                        THEN OBJECT_ID (QUOTENAME (name) + '.[dbo].[PostNotices]', 'U')
                END
                IS NOT NULL

  6. Create a cursor on the above query and use it to step through the databases.

    For each database:

    1. Build a query string that takes the query of step 4 and puts it into the temp table of step 3.
    2. Run the query string using sp_executesql.

  7. When the cursor is done, perform the final query on the temp table from step 3.

Refer to this other answer, for a working template for querying all of the Stack Exchange sites.

Putting it all together, results in the following query, which you can run live on SEDE:

-- MinMasterSiteRep: User's must have this much rep on whichever site this query is run against
-- MinRep: User's must have this much rep on all other sites

CREATE TABLE #UsersOfInterest (
    AccountId       INT NOT NULL
    , Reputation    INT
    , UserId        INT
    , PRIMARY KEY (AccountId)
)
INSERT INTO  #UsersOfInterest
    SELECT  u.AccountId, u.Reputation, u.Id
    FROM    Users u
    Where   u.Reputation > ##MinMasterSiteRep:INT?200000##

CREATE TABLE #AllSiteResults (
      [Master Rep]          INT
      , [Mstr UsrId]        NVARCHAR(777)
      , AccountId           NVARCHAR(777)
      , [Site name]         NVARCHAR(777)
      , [Username on site]  NVARCHAR(777)
      , [Rep]               INT
      , [# Ans]             INT
      , [# Qst]             INT
)

DECLARE @seDbName       AS NVARCHAR(777)
DECLARE @seSiteURL      AS NVARCHAR(777)
DECLARE @sitePrettyName AS NVARCHAR(777)
DECLARE @seSiteQuery    AS NVARCHAR(max)

DECLARE seSites_crsr CURSOR FOR
WITH dbsAndDomainNames AS (
    SELECT      dbL.dbName
                , STRING_AGG (dbL.domainPieces, '.')    AS siteDomain
    FROM (
        SELECT      TOP 50000   -- Never be that many sites and TOP is needed for order by, below
                    name        AS dbName
                    , value     AS domainPieces
                    , row_number ()  OVER (ORDER BY (SELECT 0)) AS [rowN]
        FROM        sys.databases
        CROSS APPLY STRING_SPLIT (name, '.')
        WHERE       CASE    WHEN state_desc = 'ONLINE'
                            THEN OBJECT_ID (QUOTENAME (name) + '.[dbo].[PostNotices]', 'U') -- Pick a table unique to SE data
                    END
                    IS NOT NULL
        ORDER BY    dbName, [rowN] DESC
    ) AS dbL
    GROUP BY    dbL.dbName
)
SELECT      REPLACE (REPLACE (dadn.dbName, 'StackExchange.', ''), '.', ' ' )  AS [Site Name]
            , dadn.dbName
            , CASE  -- See https://meta.stackexchange.com/q/215071
                    WHEN dadn.dbName = 'StackExchange.Mathoverflow.Meta'
                    THEN 'https://meta.mathoverflow.net/'
                    -- Some AVP/Audio/Video/Sound kerfuffle?
                    WHEN dadn.dbName = 'StackExchange.Audio'
                    THEN 'https://video.stackexchange.com/'
                    -- Ditto
                    WHEN dadn.dbName = 'StackExchange.Audio.Meta'
                    THEN 'https://video.meta.stackexchange.com/'
                    -- Normal site
                    ELSE 'https://' + LOWER (siteDomain) + '.com/'
            END AS siteURL
FROM        dbsAndDomainNames dadn
WHERE       (dadn.dbName = 'StackExchange.Meta'  OR  dadn.dbName NOT LIKE '%Meta%')

-- Step through cursor
OPEN    seSites_crsr
FETCH   NEXT FROM seSites_crsr INTO @sitePrettyName, @seDbName, @seSiteURL
WHILE   @@FETCH_STATUS = 0
BEGIN
    SET @seSiteQuery = '
        USE [' + @seDbName + ']

        INSERT INTO #AllSiteResults
            SELECT
                        uoi.Reputation                                                                                  AS [Master Rep]
                        , ''site://u/'' + CAST(uoi.UserId AS NVARCHAR(88)) + ''|'' + CAST(uoi.UserId AS NVARCHAR(88))   AS [Mstr UsrId]
                        , [AccountId] = ''https://stackexchange.com/users/'' + CAST(u.AccountId AS NVARCHAR(88)) + ''?tab=accounts|'' + CAST(u.AccountId AS NVARCHAR(88))
                        , ''' + @sitePrettyName + '''                                                                   AS [Site name]
                        , ''' + @seSiteURL + ''' + ''u/'' + CAST(u.Id AS NVARCHAR(88)) + ''|'' + u.DisplayName          AS [Username on site]
                        , u.Reputation                                                                                  AS [Rep]
                        , (SELECT COUNT(q.Id)  FROM Posts q  WHERE q.OwnerUserId = u.Id  AND q.PostTypeId = 2)          AS [# Ans]
                        , (SELECT COUNT(q.Id)  FROM Posts q  WHERE q.OwnerUserId = u.Id  AND q.PostTypeId = 1)          AS [# Qst]
            FROM        #UsersOfInterest uoi
            INNER JOIN  Users u                ON uoi.AccountId = u.AccountId
            WHERE       u.Reputation > ##MinRep:INT?200##
    '
    EXEC sp_executesql @seSiteQuery

    FETCH NEXT FROM seSites_crsr INTO @sitePrettyName, @seDbName, @seSiteURL
END
CLOSE       seSites_crsr
DEALLOCATE  seSites_crsr

SELECT      *
FROM        #AllSiteResults
ORDER BY    [Master Rep] DESC, AccountId, [Rep] DESC

It gives results like:

Query output

-- where the blue values are hyperlinked.

Note that a user must have 200 rep on a site for it to be "significant". That's also the rep needed for the site to be included in the Stack Exchange flair.


Related Topics

Creating New Database from a Backup of Another Database on the Same Server

SQL Join Where to Place the Where Condition

Truncate All Tables in MySQL Database That Match a Name Pattern

Sqlite Multi-Primary Key on a Table, One of Them Is Auto Increment

How to Select Columns from a Table Which Have Non Null Values

What Column Should the Clustered Index Be Put On

Alter Table Then Update in Single Statement

Access SQL Using Top 5 Returning More Than 5 Results

SQL Injection After Removing All Single-Quotes and Dash-Characters

How to Execute SQL Statements Saved in a Table with T-Sql

How to Use Order by with Union All in SQL

Force Index Use in Oracle

How to Store More Than 255 Char in MySQL Database

Are There Any Free Tools to Generate 'Insert Into' Scripts in Ms SQL Server

How to Convert Date to a Format 'Mm/Dd/Yyyy'

Cross-Table Update in SQLite3

Oracle Pivot Query Gives Columns with Quotes Around the Column Names. What

Multiple Yet Mutually Exclusive Foreign Keys - Is This the Way to Go


Leave a reply



Submit