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
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
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.
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.
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.
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:
| column name |
data type |
description |
| database_id |
int |
database_id from sys.databases. |
| database_name |
nvarchar(128) |
name from sys.databases. |
| site_name |
nvarchar(64) |
Name from Sites, e.g. Ubuntu or Webapps Meta. |
| tiny_name |
nvarchar(50) |
TinyName from Sites, which is also the API site parameter, e.g. askubuntu or meta.webapps. |
| long_name |
nvarchar(64) |
LongName from Sites, e.g. Ask Ubuntu or Web Applications Meta. |
| site_type |
nvarchar(9) |
For meta sites other than this one, meta_site. For this site and all main sites, main_site. |
| site_url |
nvarchar(2048) |
Clickable link to the site, e.g. https://askubuntu.com or https://webapps.meta.stackexchange.com. |
| sede_url |
nvarchar(2048) |
Clickable link to the site-specific Data Explorer. Note that a link to meta effectively lands you at its parent. |
| api_site_parameter |
nvarchar(50) |
The api site parameter, e.g. askubuntu or meta.webapps. |
| initialized |
datetime |
When we started populating that database. |
| made_available |
datetime |
When the database came online and was ready for queries. |
| processing_time |
time(3) |
The duration between initialized and made_available, in hh:mm:ss.fff format. |
| questions |
int |
Total number of questions in this site at the time of the current refresh. |
| answers |
int |
Total number of answers in this site at the time of the current refresh. |
| latest_post |
datetime |
The timestamp of the last post captured in this refresh. |
| notes |
varchar(46) |
This will be non-NULL when a database is in transition (Database is not ready.) or when the database is currently being refreshed (... refresh in progress since {hh:mm:ss} UTC ...). For the former, this should be rare and very brief. For the latter, the existing database is still available, it's just the old copy. If you want to get just online databases with the most recent week, use WHERE notes IS NOT NULL. If you want to wait until all databases are online, just check WHERE NOT EXISTS (SELECT 1 FROM dbo.sede_databases WHERE notes IS NOT NULL. |
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;
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:
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;
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.
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:
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:
| column name |
data type |
description |
| rn |
int |
row number, for sorting by "long name" |
| content |
nvarchar(max) |
markdown with headers |
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).
| column name |
data type |
description |
| database_id |
int |
database_id from sys.databases. |
| database_name |
nvarchar(128) |
name from sys.databases. |
| table_name |
nvarchar(128) |
name from sys.tables. |
| latest_date |
datetime |
The latest timestamp (e.g. CreationDate) for tables that have such a column. |
| row_count |
bigint |
Number of rows in the table, according to sys.partitions. Note: For Posts, this is PostsWithDeleted; if you want non-deleted total number of Posts, use answers + questions from dbo.sede_databases. |
| initialized |
datetime |
Timestamp of initial table creation. |
| made_available |
datetime |
Timestamp of final operation against table. |
| processing_time |
time(3) |
Duration of initialized -> made_available, in {hh:mm:ss.fff} format. |
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';
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:
| column name |
data type |
description |
| site_name |
nvarchar(64) |
Name from Sites, e.g. Ubuntu or Webapps Meta. |
| site_url |
nvarchar(2048) |
Clickable link to the site, e.g. https://askubuntu.com or https://webapps.meta.stackexchange.com. |
| database_name |
nvarchar(128) |
name from sys.databases. |
| long_name |
nvarchar(64) |
LongName from Sites, e.g. Ask Ubuntu or Web Applications Meta. |
| site_id |
int |
Id from Sites. |
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):
| column name |
data type |
description |
| account_id |
int |
Id from Accounts. |
| site_id |
int |
Id from Sites (and site_id from dbo.sede_sites). |
| user_id |
int |
Id from Users. (Sorry for the keyword collision, another vicious debate I had with myself about keeping the established convention or letting IntelliSense be angry. Lazy won.) |
| reputation |
int |
Reputation from the network profile accounts tab. |
| question_count |
int |
Questions from the network profile accounts tab. |
| answer_count |
int |
Answers from the network profile accounts tab. |
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. đ„±
