Q&A for spacecraft operators, scientists, engineers, and enthusiasts
-- 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 @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 Count] int NOT NULL -- ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ ) -- These variables are for the SPOOKY HOSTNAME GENERATION CODE DECLARE @spooky_string AS nvarchar(max) DECLARE @spooky_delimiter AS char(1) = '.' DECLARE @spooky_xml AS xml DECLARE @spooky_result AS nvarchar(max) -- Step through cursor OPEN sites FETCH NEXT FROM sites INTO @sitedbname WHILE @@FETCH_STATUS = 0 BEGIN ----------------------------------------------------------------------------- -- BEGIN SPOOKY HOSTNAME GENERATION CODE ------------------------------------ -- adapted from <http://data.stackexchange.com/stackoverflow/query/256747/> - ----------------------------------------------------------------------------- SET @spooky_string = @sitedbname SET @spooky_xml = CAST(('<X>' + REPLACE(@spooky_string, @spooky_delimiter, '</X><X>') + '</X>') AS xml) SET @spooky_result = '' SELECT C.value('.', 'nvarchar(max)') AS [Piece], C.value('for $i in . return count(../*[. << $i]) + 1', 'int') AS [Index] INTO #spooky_pieces FROM @spooky_xml.nodes('X') AS X(C) SELECT @spooky_result = COALESCE(@spooky_result + '.', '') + [Piece] FROM #spooky_pieces ORDER BY [Index] DESC DROP TABLE #spooky_pieces SET @sitehostname = 'http://' + RIGHT(@spooky_result, LEN(@spooky_result)-1) + '.com' SET @ispersitemeta = (CASE WHEN @sitedbname LIKE '%Meta%' AND @sitedbname != 'StackExchange.Meta' THEN 1 ELSE 0 END) ---------------------------------------- -- HERE COME THE SPOOKY SPECIAL CASES -- ---------------------------------------- -- Meta MathOverflow doesn't have a redirect; see <http://meta.stackexchange.com/q/215071/224428> IF @sitedbname = 'StackExchange.Mathoverflow.Meta' SET @sitehostname = 'http://Meta.MathOverflow.net' -- For some reason probably involving the AVP/Audio/Video/Sound hullabaloo, there is -- still a StackExchange.Audio DB that's getting updated. http://audio.stackexchange.com/ -- no longer exists, so we use Video.SE for the hostname instead. IF @sitedbname = 'StackExchange.Audio' SET @sitehostname = 'http://Video.StackExchange.com' ----------------------------------------------------------------------------- -- END SPOOKY HOSTNAME GENERATION CODE -------------------------------------- ----------------------------------------------------------------------------- -- ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ -- CODE YOU WANT TO ADD SHOULD GO HERE -- for example, SET @query = ' USE [' + @sitedbname + '] INSERT INTO #out SELECT ''' + @sitehostname + '|' + @sitedbname + ''' AS [Site], (Select Count(*) From Users) As [User Count] ' EXEC sp_executesql @query -- ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ FETCH NEXT FROM sites INTO @sitedbname END CLOSE sites DEALLOCATE sites -- Reap results (also optional) SELECT * FROM #out