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