My user ID on all SE sites

0

Please login or register to vote for this query.

(click on this box to dismiss)

Music Fans Meta

Q&A about the site for music historians, critics, and fans

-- Originally based on https://data.stackexchange.com/space/query/355774/cross-site-query-template

-- AccountId: Your global Stack Exchange account ID number, as found in your network profile URL
DECLARE @accountid AS nvarchar(max) = CAST(##AccountId:int## as nvarchar)

-- 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 @fixedsitename AS nvarchar(max)
DECLARE @splitpos AS int
DECLARE @sitehostname AS nvarchar(max)
DECLARE @ispersitemeta AS bit

DECLARE @query AS nvarchar(max)
CREATE TABLE #out (
  Site nvarchar(max) NOT NULL,
  -- ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
  -- COLUMN NAMES WANT TO ADD SHOULD GO HERE   
  [User ID] int NOT NULL
  -- ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑
)

-- Step through cursor
OPEN sites
FETCH NEXT FROM sites INTO @sitedbname
WHILE @@FETCH_STATUS = 0
BEGIN
  -- Fix some inconsistencies in the DB names to make them match the host names better
  SET @fixedsitename = @sitedbname
  SET @fixedsitename = REPLACE(@fixedsitename, 'StackExchange.Mathoverflow', 'Mathoverflow')
  SET @fixedsitename = REPLACE(@fixedsitename, 'StackExchange.Ubuntu', 'AskUbuntu')
  SET @fixedsitename = REPLACE(@fixedsitename, 'StackExchange.Audio', 'StackExchange.Video')
  SET @fixedsitename = REPLACE(@fixedsitename, 'StackOverflow.Br', 'StackOverflow.Pt')

  -- Convert fixed DB name to hostname by moving the domain name to the end
  SET @splitpos = CHARINDEX('.', @fixedsitename, 0)
  SET @sitehostname = (CASE
        WHEN @splitpos = 0 THEN @fixedsitename
        ELSE RIGHT(@fixedsitename, LEN(@fixedsitename) - @splitpos) + '.' + LEFT(@fixedsitename, @splitpos - 1)
      END) + (CASE WHEN @fixedsitename LIKE 'Mathoverflow%' THEN '.net' ELSE '.com' END)

  -- Knowing whether a site is a per-site meta is sometimes useful
  SET @ispersitemeta = (CASE WHEN @sitedbname LIKE '%.Meta' AND @sitedbname != 'StackExchange.Meta' THEN 1 ELSE 0 END)

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

    INSERT INTO #out
    SELECT
    ''https://' + @sitehostname + '/users/'' + CAST(Id AS nvarchar) + ''|' + @sitedbname + ''' AS [Site],
      Id AS [User ID]
    FROM Users WHERE AccountId = ' + @accountid + '
    '
  EXEC sp_executesql @query
  -- ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑

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

-- Reap results (also optional)
SELECT
  *
FROM
  #out

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results