Preguntas y respuestas sobre el sitio para programadores y profesionales de la informática
-- result table, don't rename and keep the site column CREATE TABLE #Results (Site NVARCHAR(250), Id INT, DisplayName NVARCHAR(40)); DECLARE @sql NVARCHAR(MAX) = ''; -- holds built up sql string -- build one biq union sql, for each db SELECT @sql = @sql + IIF( LEN(@sql) > 1 , 'UNION ALL' , 'INSERT INTO #Results' ) + -- here goes the per site query, fully qualify the database objects N' SELECT ''' + name + ''', Id, DisplayName FROM ' + quotename(name) + '.dbo.Users WHERE AccountId = ' + CAST(##AccountId:int## as nvarchar) + ' ' FROM sys.databases WHERE database_id > 5 AND (name NOT LIKE '%.Meta' OR name = 'StackExchange.Meta'); -- execute it EXEC (@sql); -- Site details, cf. https://meta.stackexchange.com/a/359795/295232 CREATE TABLE #Sites (URL NVARCHAR(100), SiteName NVARCHAR(100), DatabaseName NVARCHAR(50)); -- fetch Markdown for post containing table with site details DECLARE @md NVARCHAR(MAX); SELECT TOP 1 @md = Text FROM [StackExchange.Meta].dbo.PostHistory WHERE PostId = 359796 AND PostHistoryTypeId = 5 ORDER BY CreationDate DESC; -- split Markdown on pipe symbol CREATE TABLE #TableEntries (ID INT IDENTITY(1, 1), Value NVARCHAR(100)); INSERT INTO #TableEntries (Value) SELECT LEFT(TRIM(VALUE), 100) FROM STRING_SPLIT(@md, '|') WHERE LEN(TRIM(REPLACE(REPLACE(VALUE, CHAR(10), ''), CHAR(13), ''))) > 0; -- 'pivot' - we have to discard the introductory text, headers and horizontal lines INSERT INTO #Sites SELECT t0.Value, t1.Value, t2.Value FROM #TableEntries AS t0 INNER JOIN #TableEntries AS t1 ON t0.Id + 1 = t1.Id INNER JOIN #TableEntries AS t2 ON t0.Id + 2 = t2.Id WHERE t0.Id >= 8 -- that's where the real content starts AND t0.Id % 3 = 2; -- Show results SELECT SiteName, URL + IIF(r.Id IS NULL, '/users/join', '/users/' + CAST(r.Id AS NVARCHAR)) + '|' + IIF(r.Id IS NULL, 'Join ' + SiteName, r.DisplayName) FROM #Sites AS s LEFT OUTER JOIN #Results AS r ON r.Site = s.DatabaseName WHERE DatabaseName NOT LIKE '%.Meta' OR DatabaseName = 'StackExchange.Meta' ORDER BY r.Id DESC;