Create a view that provides a list of databases

I've built something you will hopefully find useful, as it will present a list of databases (and a bunch of additional information) that you can use for cross-database queries.

Before I explain further, though, I'm going to start with some caveats:

Caveats

1. Due to the principle of least disruption™, as of April 8th, 2024, these views mentioned below are only exposed in 3D Printing - just happened to alphabetically be the first non-busy Data Explorer site (maybe this is a clever ploy to change that!). The rest of the sites will have them enabled on our next SEDE refresh.
   status-completed

2. The views reference data that is pulled on demand from each database as it becomes available on Sunday, and then cached. It's a heavy query so, immediately after a site comes online, the information might take a couple of minutes to be refreshed in the views.

3. Site names that contain ampersands come back as the "fixed" name that doesn't break SEDE. For example, Anime & Amiga is presented as Anime and Amiga. For background, see this question.

4. SEDE simply doesn't have access to some of the information folks have requested in the past, like the launch date (which you can probably approximate from MIN(Posts.CreationDate)), whether a site is in beta, and even the data dump filename. The latter is just {LOWER(site_url without https://).7z}, but is complicated by the fact that Stack Overflow now pushes 8 separate files to archive.org.

5. I'll document them here but you won't find these views (or their definitions) in Data Explorer. If you know why, or think you know why, please don't guess out loud - I won't comment. Let's just all agree to call it magic. 🎩

dbo.sede_databases

This view has the following columns:

Looping query alternatives

One of the main things that prompted me to prompt you to submit a feature request is because it was painful to watch people craft queries that rely on parsing markdown from a meta question - something arguably quite fragile - simply to be able to query each database using information I knew we already had.

CREATE TABLE #results (
  db           sysname,
  Id           int,
  Title        nvarchar(250),
  ViewCount    int,
  Score        int,
  CreationDate datetime);

DECLARE @exec nvarchar(500), @sql nvarchar(max), @c cursor;

SELECT @sql = N'INSERT #results
  (db,Id,Title,ViewCount,Score,CreationDate)
  SELECT DB_NAME(), p.Id, p.Title, p.ViewCount, p.Score, p.CreationDate
  FROM Posts AS p INNER JOIN Users AS u
    ON p.OwnerUserId = u.Id
    WHERE p.PostTypeId = 1 AND u.AccountId = @AccountId;';

SET @c = cursor FOR SELECT database_name
  FROM dbo.sede_databases WHERE notes IS NULL;

OPEN @c;
FETCH NEXT FROM @c INTO @exec;

WHILE @@FETCH_STATUS <> -1
BEGIN
  SET @exec = QUOTENAME(@exec) + N'.sys.sp_executesql';
  EXEC @exec @sql, N'@AccountId int', ##AccountId:int##;
  FETCH NEXT FROM @c INTO @exec;
END

SELECT Site        = sd.long_name, 
       Question    = CONCAT(sd.site_url, '/q/', r.Id, '|', r.Title),
       [# views]   = r.ViewCount,
       r.Score,
       [Posted on] = r.CreationDate
  FROM dbo.sede_databases AS sd
  INNER JOIN #results AS r 
     ON r.db = sd.database_name
  ORDER BY r.ViewCount DESC;

• Query permalink

Aggregate query alternatives

This view also lets you simplify some other queries I've seen out there to obtain aggregates about the state of various databases and how long they took. Some of that information is available directly from sys.databases, but an aggregate variation that comes to mind is:

• Status of the SEDE Refresh on Sunday 03:00 UTC

The first query there...

...returns information about the oldest and newest databases, any databases currently refreshing, and the average duration. (And a couple of other rows I got too lazy to grok, but I think they're covered in the next query?) We can roughly replicate most of this query against the new views like this:

;WITH dbs AS
(
  SELECT database_id, 
         database_name,
         made_available,
         initialized,
         ma_days_ago = DATEDIFF(DAY, made_available, GETDATE()),
         init_days_ago = DATEDIFF(DAY, initialized, GETDATE()),
         notes
  FROM dbo.sede_databases
)
SELECT 1, category = 'oldest', * FROM
(
  SELECT TOP (1) db = RTRIM(database_id), 
             Description = database_name, 
             create_date = made_available, 
             [days ago] = ma_days_ago
    FROM dbs WHERE notes IS NULL
    ORDER BY made_available
) AS o
UNION ALL SELECT 2, 'newest', * FROM 
(
  SELECT TOP (1) db = RTRIM(database_id), 
             database_name, 
             made_available, 
             ma_days_ago
    FROM dbs WHERE notes IS NULL
    ORDER BY made_available DESC
) AS n
UNION ALL SELECT 3, 'restoring', * FROM
(
  SELECT db = RTRIM(database_id), 
         database_name, 
         initialized, 
         init_days_ago
    FROM dbs WHERE notes LIKE N'...%since%UTC%'
) AS r
UNION ALL SELECT 6, 'duration (min)', * FROM
(
  SELECT c  = CONCAT('count ', COUNT(*)), 
         av = CONCAT('avg ', CONVERT(decimal(12,2), 
              AVG(1.0*DATEDIFF(MINUTE, initialized, made_available)))),
         x  = null, y  = null FROM dbs) AS av;

• Query permalink

The second query there...

...shows the earliest and latest database, how many days ago that was, whether there are any databases still restoring, and then how many databases there are and how long the whole process took. This query can work against the new view like this (arguably, it's not much simpler):

SELECT [earliest date (UTC)] = FirstDB,
       [latest date (UTC)]   = LastDB,
       [days ago]            = DATEDIFF(DAY, LastDB, getdate()),
       restoring             = COALESCE(restoring, N'None'),
       [count]               = DBCount,
       minutes               = DATEDIFF(MINUTE, FirstDB, LastDB)
FROM 
(
  SELECT FirstDB   = MIN(made_available),
         LastDB    = MAX(made_available),
         DBCount   = COUNT(*),
         restoring = (
           SELECT STRING_AGG(database_name, ', ')
             FROM dbo.sede_databases
             WHERE notes LIKE N'...%since%UTC%'
         )
    FROM dbo.sede_databases
    WHERE notes IS NULL
) AS details;

The only difference is the new variation shows a comma-separated list of any databases still restoring, which will be helpful since the process is now parallel. Also, the made_available timestamp is up to a few seconds later because it includes time spent on further database operations not captured by sys.databases.

• Query permalink

And the third query there...

...is also captured above.

Generating markdown

There's a question over here about getting URLs and site names for a cross-site SEDE query:

• How can I get URLs and site names for a cross-site SEDE query?

One of the answers there has a big chunk of markdown presenting URL, Site name ("long name"), and Database name for each database, ordered by "long name" (so that, e.g., Ask Ubuntu and Ask Ubuntu Meta appear together).

I've created a view called dbo.sede_databases_markdown that will generate this markdown for you, with the following columns:

My example query looks like this (but I also added some cache-busting):

SELECT content 
  FROM dbo.sede_databases_markdown
  ORDER BY rn;

If you don't want the markdown headers, you can say

SELECT content 
  FROM dbo.sede_databases_markdown
  AND rn > 0
  ORDER BY rn;

To make it easy to copy and paste the results (which will have some extra things you'll need to manually remove, like the content result header, a long line of -----, and 300+ row(s) returned), use the "Text-only results" option:

But wait, there's more!

If you didn't just want to know about databases, I can also tell you a little about the tables in each site!

dbo.sede_tables

Some additional information here about each table, including the ability to see which tables are the biggest (without running expensive COUNT(*) queries) and which took the longest to complete (though that information will become less interesting as the process changes).

An example query that shows how long each table took for Stack Overflow (keeping in mind, this weekend is a bad reference point that involved me literally falling asleep; next weekend's should look a lot better):

SELECT table_name, 
       latest_date, 
       row_count,
       initialized,
       made_available,
       processing_time
  FROM dbo.sede_tables
 WHERE database_name = N'StackOverflow';

• Query permalink

There's still more!

Well, this was mostly to help me.

dbo.sede_sites

This is just a slimmer version of dbo.sede_databases, without all the additional computation and materialization. It just has four columns:

Most importantly, this can be used to join the database you're in to get a couple of extra properties, with a very simple index seek against a pretty narrow row:

SELECT s.site_name, s.site_url, s.long_name
  FROM dbo.sede_sites AS s
  WHERE database_name = DB_NAME();

And yet more!

A common request has been to look at a specific account (say, your own) across multiple sites, without having to loop and scan entire Users tables to find the matching AccountId. So, I offer...

dbo.sede_users

This is meant to provide some of the information you'll spot in the network profile / accounts tab; namely, reputation, question count, and answer count.^* For example, here are my top 5 sites by reputation:

SELECT  TOP (5)
        ss.site_name,
        profile = CONCAT(ss.site_url, '/u/', su.user_id),
        su.reputation,
        su.question_count,
        su.answer_count
   FROM dbo.sede_users AS su
  INNER JOIN dbo.sede_sites AS ss
     ON su.site_id = ss.site_id
  WHERE su.account_id = ##AccountId:int##
  ORDER BY su.reputation DESC;

Results:

^{* Note that this view is refreshed once daily, around 00:00 UTC.}

Column list (mostly self-explanatory):

To keep things simple, sorry, I did not include badge counts, so it is not a full representation. You could replicate a partial row from this page for a single site using this query:

  SELECT su.user_id, 
         su.reputation,
         su.question_count,
         su.answer_count
    FROM dbo.sede_users AS su
   INNER JOIN dbo.sede_sites AS ss
      ON su.site_id = ss.site_id
   WHERE su.account_id = ##AccountId:int##
     AND ss.database_name = DB_NAME();

Results:

Or for all sites, you can use this query:

  SELECT ss.long_name,
         profile = CONCAT(ss.site_url, '/u/', su.user_id),
         su.reputation,
         su.question_count,
         su.answer_count
    FROM dbo.sede_users AS su
   INNER JOIN dbo.sede_sites AS ss
      ON su.site_id = ss.site_id
   WHERE su.account_id = ##AccountId:int##;

Results:

But to get the badges, it's a little more complex (and again, this is for a single site only):

;WITH src AS 
(
  SELECT su.user_id, 
         su.reputation,
         su.question_count,
         su.answer_count
    FROM dbo.sede_users AS su
   INNER JOIN dbo.sede_sites AS ss
      ON su.site_id = ss.site_id
   WHERE su.account_id = ##AccountId:int##
     AND ss.database_name = DB_NAME()
)
SELECT reputation,
       question_count, 
       answer_count,
       [bronze badges] = [1],
       [silver badges] = [2],
       [gold badges]   = [3]
  FROM src
 OUTER APPLY
       (
         SELECT class, BadgeCount = COUNT(*)
           FROM dbo.Badges AS b
          WHERE b.UserId = src.user_id
          GROUP BY class
       ) AS b
 PIVOT
 (
   MAX(BadgeCount) 
   FOR [class] IN ([1],[2],[3])
 ) AS p;

Results:

There isn't a direct way to combine getting global information across all sites with local information within each site, without creating your own #temp table and looping. At least not yet.

Have fun

These views are provided as is and without any warranty or support. I will not be taking bug reports at this time, because that wiped me out, I didn't really get approval to work on this and, also, I don't want to have to update any of the above markup or queries. 🥱