0

Please login or register to vote for this query.

(click on this box to dismiss)

sys.databases

Q&A about the site for gardeners and landscapers

-- 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 @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
  -- (In most cases these are due to site merges or renames and the main sites do redirect, but the metas don't.)
  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, 'StackExchange.Garage', 'StackExchange.Mechanics')
  SET @fixedsitename = REPLACE(@fixedsitename, 'StackExchange.Moderators', 'StackExchange.CommunityBuilding')
  SET @fixedsitename = REPLACE(@fixedsitename, 'StackExchange.Photography', 'StackExchange.Photo')
  SET @fixedsitename = REPLACE(@fixedsitename, 'StackExchange.Programmers', 'StackExchange.SoftwareEngineering')
  SET @fixedsitename = REPLACE(@fixedsitename, 'StackOverflow.Br', 'StackOverflow.Pt')

  -- Convert fixed DB name to hostname by moving the domain name to the end
  SET @sitehostname = (CASE
        WHEN CHARINDEX('.', @fixedsitename, 0) = 0 THEN @fixedsitename
        ELSE RIGHT(@fixedsitename, LEN(@fixedsitename) - CHARINDEX('.', @fixedsitename, 0))
             + '.' + LEFT(@fixedsitename, CHARINDEX('.', @fixedsitename, 0) - 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) + ''|' + @fixedsitename + ''' 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

fork query

edited oct 19 19

created aug 26 15 anonymous