What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 149 Stack Exchange communities.

I think it would be useful if we could join across databases in the data-explorer.

For example, I'd like to query up a list of questions that I had a hand in migrating away from SO, and see what percentage of them were subsequently closed or re-migrated.

I realise I could download the various data dumps and do this on my own machine, but it would be nice if it were allowed on the online version.

Is this possible/practical?

share|improve this question

2 Answers 2

up vote 17 down vote accepted

You can already do this if you want to do a bit of light hacking in your queries. And, of course, you'll have to roll your own logic to do what you're specifically asking for.

SELECT * FROM sys.databases

(All SEDE activity is IP-logged, so please don't go poking into the system databases.)

When you switch sites in SEDE, all the web app does is direct the query to the appropriate database. Since all the databases are accessible through any connection, you have access to all the data at any time -- you just have to 3-part-name the database objects.

SELECT TOP 1 *
    FROM [StackOverflow]..[Users] u1
    INNER JOIN [ServerFault]..[Users] u2 ON u1.AccountId = u2.AccountId
share|improve this answer
    
Awesome, that's all I need! –  Blorgbeard Mar 17 '11 at 17:09
    
@Mark: The point of this question was to demonstrate that cross-database queries are possible, not necessarily that the example query I provided did anything meaningful (IIRC, I just typed it from memory to make the point). However, for the sake of correctness, I have edited the answer. Thanks. –  Jon Seigel Aug 30 '12 at 12:58
    
Tim Stone has pointed out that AccountId is now available to join with. –  Mark Hurd Feb 16 '14 at 16:45
    
@Mark: Thanks; updated. –  Jon Seigel Feb 16 '14 at 16:56

Automatic cross-site queries have been made possible by senshin in this query (mind you, large queries will timeout) (current version https://data.stackexchange.com/space/query/355774/cross-site-query-template by Mark Hurd):

-- Create cursor for list of sites
DECLARE sites CURSOR FOR
  SELECT name
  FROM sys.databases
  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange')
-- And declare some variables
DECLARE @sitedbname AS nvarchar(max)
DECLARE @sitehostname AS nvarchar(max)
DECLARE @ispersitemeta AS bit

DECLARE @query AS nvarchar(max)
CREATE TABLE #out (
  Site nvarchar(max) NOT NULL,
  -- ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
  -- COLUMN NAMES YOU WANT TO ADD SHOULD GO HERE   
  [User Count] int NOT NULL
  -- ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑
)
-- These variables are for the SPOOKY HOSTNAME GENERATION CODE
DECLARE @spooky_string AS nvarchar(max)
DECLARE @spooky_delimiter AS char(1) = '.'
DECLARE @spooky_xml AS xml
DECLARE @spooky_result AS nvarchar(max)

-- Step through cursor
OPEN sites
FETCH NEXT FROM sites INTO @sitedbname
WHILE @@FETCH_STATUS = 0
BEGIN
  -----------------------------------------------------------------------------
  -- BEGIN SPOOKY HOSTNAME GENERATION CODE ------------------------------------
  -- adapted from <http://data.stackexchange.com/stackoverflow/query/256747/> -
  -----------------------------------------------------------------------------
  SET @spooky_string = @sitedbname
  SET @spooky_xml = CAST(('<X>' + REPLACE(@spooky_string, @spooky_delimiter, '</X><X>') + '</X>') AS xml)
  SET @spooky_result = ''

  SELECT
    C.value('.', 'nvarchar(max)') AS [Piece],
    C.value('for $i in . return count(../*[. << $i]) + 1', 'int') AS [Index]
  INTO #spooky_pieces
  FROM @spooky_xml.nodes('X') AS X(C)

  SELECT @spooky_result = COALESCE(@spooky_result + '.', '') + [Piece]
  FROM #spooky_pieces
  ORDER BY [Index] DESC

  DROP TABLE #spooky_pieces

  SET @sitehostname = 'http://' + RIGHT(@spooky_result, LEN(@spooky_result)-1) + '.com'
  SET @ispersitemeta = (CASE WHEN @sitedbname LIKE '%Meta%' AND @sitedbname != 'StackExchange.Meta' THEN 1 ELSE 0 END)

  ----------------------------------------
  -- HERE COME THE SPOOKY SPECIAL CASES --
  ----------------------------------------
  -- Meta MathOverflow doesn't have a redirect; see <http://meta.stackexchange.com/q/215071/224428>
  IF @sitedbname = 'StackExchange.Mathoverflow.Meta' SET @sitehostname = 'http://Meta.MathOverflow.net'
  -- For some reason probably involving the AVP/Audio/Video/Sound hullabaloo, there is 
  -- still a StackExchange.Audio DB that's getting updated. http://audio.stackexchange.com/
  -- no longer exists, so we use Video.SE for the hostname instead.
  IF @sitedbname = 'StackExchange.Audio' SET @sitehostname = 'http://Video.StackExchange.com'
  -----------------------------------------------------------------------------
  -- END SPOOKY HOSTNAME GENERATION CODE --------------------------------------
  -----------------------------------------------------------------------------

  -- ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
  -- CODE YOU WANT TO ADD SHOULD GO HERE
  -- for example,
  SET @query = '
    USE [' + @sitedbname + ']

    INSERT INTO #out
    SELECT
      ''' + @sitehostname + '|' + @sitedbname + ''' AS [Site],
      (Select Count(*) From Users) As [User Count]
    '
  EXEC sp_executesql @query
  -- ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑

  FETCH NEXT FROM sites INTO @sitedbname
END
CLOSE sites
DEALLOCATE sites

-- Reap results (also optional)
SELECT
  *
FROM
  #out
share|improve this answer
1  
Based upon the SQL posted here, not the query linked, I have "simplified" the query and highlighted the other place that needs changing when selecting some columns per site, on SEDE. –  Mark Hurd 17 hours ago

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .