2

By default my table names are using the current site to which I am connected on. Thus a query SELECT TOP 100 * FROM Users will list 100 users.

If I wish to select from a different database, in some cases I can use query like

SELECT TOP 100 * FROM superuser.dbo.Users;

As I can see, this short name is possible only for sites with a different name as *.stackexchange.com. (The "trinity", for example and some other.)

How can I query, for example, from tables in the Physics SE?

I've got the list of the database names with a SELECT * FROM sysdatabases, but I couldn't go further. Any tries to name them as StackExchange.dbo.devops.users, StackExchange.devops.dbo.users and similar were all unsuccessful.

6

The database names contain literal periods, so you have to escape them, e.g.:

SELECT TOP 100
  *
FROM
  [StackExchange.Devops].dbo.Users

For example (contrived), this lists the top users on DevOps who also have SO/SU accounts and provides a total rep for the three sites:

SELECT TOP 50
  CONCAT('https://stackexchange.com/users/', so.AccountId) Network_Profile,
  dv.DisplayName DevOps_UserName,
  so.Reputation SO_Rep,
  su.Reputation SU_Rep,
  dv.Reputation DevOps_Rep,
  so.Reputation + su.Reputation + dv.Reputation Total
FROM
  StackOverflow.dbo.Users so
  INNER JOIN SuperUser.dbo.Users su ON su.AccountId = so.AccountId
  -- must escape db name:
  INNER JOIN [StackExchange.Devops].dbo.Users dv ON dv.AccountId = so.AccountId
ORDER BY
  DevOps_Rep DESC

It doesn't matter which site you have selected in SEDE when you run that.


Alternatively, you can use the USE statement, and all subsequent queries will be on that database. Same escaping rules apply. For example, this displays the number of up and downvotes on the currently selected site (which is Mechanics), Worldbuilding, and DevOps:

DECLARE @Example TABLE (SiteName nvarchar(32), UpVotes int, DownVotes int)

-- This first one uses the currently selected site:
INSERT INTO @Example SELECT DB_NAME(),
  COUNT(CASE WHEN VoteTypeId = 2 THEN 1 ELSE NULL END),
  COUNT(CASE WHEN VoteTypeId = 3 THEN 1 ELSE NULL END)
  FROM Votes WHERE VoteTypeId IN(2, 3);

USE [StackExchange.Worldbuilding];
INSERT INTO @Example SELECT DB_NAME(),
  COUNT(CASE WHEN VoteTypeId = 2 THEN 1 ELSE NULL END),
  COUNT(CASE WHEN VoteTypeId = 3 THEN 1 ELSE NULL END)
  FROM Votes WHERE VoteTypeId IN(2, 3);

USE [StackExchange.DevOps];
INSERT INTO @Example SELECT DB_NAME(),
  COUNT(CASE WHEN VoteTypeId = 2 THEN 1 ELSE NULL END),
  COUNT(CASE WHEN VoteTypeId = 3 THEN 1 ELSE NULL END)
  FROM Votes WHERE VoteTypeId IN(2, 3);

SELECT * FROM @Example

That could save you typing in certain cases.

You must log in to answer this question.

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