LWZgFp5S

· 7 years ago · Sep 17, 2018, 01:34 AM
1SELECT * FROM sys.databases
2	
3SELECT TOP 1 *
4    FROM [StackOverflow]..[Users] u1
5    INNER JOIN [ServerFault]..[Users] u2 ON u1.AccountId = u2.AccountId
6	
7-- Create cursor for list of sites
8DECLARE sites CURSOR FOR
9  SELECT name
10  FROM sys.databases
11  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange')
12-- And declare some variables
13DECLARE @sitedbname AS nvarchar(max)
14DECLARE @sitehostname AS nvarchar(max)
15DECLARE @ispersitemeta AS bit
16
17DECLARE @query AS nvarchar(max)
18CREATE TABLE #out (
19  Site nvarchar(max) NOT NULL,
20  -- â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“
21  -- COLUMN NAMES YOU WANT TO ADD SHOULD GO HERE   
22  [User Count] int NOT NULL
23  -- â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘
24)
25-- These variables are for the SPOOKY HOSTNAME GENERATION CODE
26DECLARE @spooky_string AS nvarchar(max)
27DECLARE @spooky_delimiter AS char(1) = '.'
28DECLARE @spooky_xml AS xml
29DECLARE @spooky_result AS nvarchar(max)
30
31-- Step through cursor
32OPEN sites
33FETCH NEXT FROM sites INTO @sitedbname
34WHILE @@FETCH_STATUS = 0
35BEGIN
36  -----------------------------------------------------------------------------
37  -- BEGIN SPOOKY HOSTNAME GENERATION CODE ------------------------------------
38  -- adapted from <http://data.stackexchange.com/stackoverflow/query/256747/> -
39  -----------------------------------------------------------------------------
40  SET @spooky_string = @sitedbname
41  SET @spooky_xml = CAST(('<X>' + REPLACE(@spooky_string, @spooky_delimiter, '</X><X>') + '</X>') AS xml)
42  SET @spooky_result = ''
43
44  SELECT
45    C.value('.', 'nvarchar(max)') AS [Piece],
46    C.value('for $i in . return count(../*[. << $i]) + 1', 'int') AS [Index]
47  INTO #spooky_pieces
48  FROM @spooky_xml.nodes('X') AS X(C)
49
50  SELECT @spooky_result = COALESCE(@spooky_result + '.', '') + [Piece]
51  FROM #spooky_pieces
52  ORDER BY [Index] DESC
53
54  DROP TABLE #spooky_pieces
55
56  SET @sitehostname = 'http://' + RIGHT(@spooky_result, LEN(@spooky_result)-1) + '.com'
57  SET @ispersitemeta = (CASE WHEN @sitedbname LIKE '%Meta%' AND @sitedbname != 'StackExchange.Meta' THEN 1 ELSE 0 END)
58
59  ----------------------------------------
60  -- HERE COME THE SPOOKY SPECIAL CASES --
61  ----------------------------------------
62  -- Meta MathOverflow doesn't have a redirect; see <http://meta.stackexchange.com/q/215071/224428>
63  IF @sitedbname = 'StackExchange.Mathoverflow.Meta' SET @sitehostname = 'http://Meta.MathOverflow.net'
64  -- For some reason probably involving the AVP/Audio/Video/Sound hullabaloo, there is 
65  -- still a StackExchange.Audio DB that's getting updated. http://audio.stackexchange.com/
66  -- no longer exists, so we use Video.SE for the hostname instead.
67  IF @sitedbname = 'StackExchange.Audio' SET @sitehostname = 'http://Video.StackExchange.com'
68  -----------------------------------------------------------------------------
69  -- END SPOOKY HOSTNAME GENERATION CODE --------------------------------------
70  -----------------------------------------------------------------------------
71
72  -- â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“â†“
73  -- CODE YOU WANT TO ADD SHOULD GO HERE
74  -- for example,
75  SET @query = '
76    USE [' + @sitedbname + ']
77
78    INSERT INTO #out
79    SELECT
80      ''' + @sitehostname + '|' + @sitedbname + ''' AS [Site],
81      (Select Count(*) From Users) As [User Count]
82    '
83  EXEC sp_executesql @query
84  -- â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘â†‘
85
86  FETCH NEXT FROM sites INTO @sitedbname
87END
88CLOSE sites
89DEALLOCATE sites
90
91-- Reap results (also optional)
92SELECT
93  *
94FROM
95  #out
96	
97/*-- INSTRUCTIONS:
98    1)  Set the columns of #AllSiteResults to what you need in the final query.
99    2)  Set the @seSiteQuery text (inside the WHILE loop) to the query that will run on each site to build
100        the #AllSiteResults table.
101    3)  Comment out the `WHERE       (dadn.dbName = 'StackExchange.Meta'...` line if site metas are desired.
102    4)  Adjust the final query if post processing is desired (optional).
103*/
104DECLARE @seDbName       AS NVARCHAR (max)
105DECLARE @seSiteURL      AS NVARCHAR (max)
106DECLARE @sitePrettyName AS NVARCHAR (max)
107DECLARE @seSiteQuery    AS NVARCHAR (max)
108
109CREATE TABLE #AllSiteResults (
110      -- PUT THE COLUMNS YOU WILL USE, HERE
111      -- vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
112      [Site]            NVARCHAR(max)
113      , [User Count]    INT
114      -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
115)
116
117DECLARE seSites_crsr CURSOR FOR
118WITH dbsAndDomainNames AS (
119    SELECT      dbL.dbName
120                , STRING_AGG (dbL.domainPieces, '.')    AS siteDomain
121    FROM (
122        SELECT      TOP 50000   -- Never be that many sites and TOP is needed for order by, below
123                    name        AS dbName
124                    , value     AS domainPieces
125                    , row_number ()  OVER (ORDER BY (SELECT 0)) AS [rowN]
126        FROM        sys.databases
127        CROSS APPLY STRING_SPLIT (name, '.')
128        WHERE       CASE    WHEN state_desc = 'ONLINE'
129                            THEN OBJECT_ID (QUOTENAME (name) + '.[dbo].[PostNotices]', 'U') -- Pick a table unique to SE data
130                    END
131                    IS NOT NULL
132        ORDER BY    dbName, [rowN] DESC
133    ) AS dbL
134    GROUP BY    dbL.dbName
135)
136SELECT      REPLACE (REPLACE (dadn.dbName, 'StackExchange.', ''), '.', ' ' )  AS [Site Name]
137            , dadn.dbName
138            , CASE  -- See https://meta.stackexchange.com/q/215071
139                    WHEN dadn.dbName = 'StackExchange.Mathoverflow.Meta'
140                    THEN 'https://meta.mathoverflow.net/'
141                    -- Some AVP/Audio/Video/Sound kerfuffle?
142                    WHEN dadn.dbName = 'StackExchange.Audio'
143                    THEN 'https://video.stackexchange.com/'
144                    -- Ditto
145                    WHEN dadn.dbName = 'StackExchange.Audio.Meta'
146                    THEN 'https://video.meta.stackexchange.com/'
147                    -- Normal site
148                    ELSE 'https://' + LOWER (siteDomain) + '.com/'
149            END AS siteURL
150FROM        dbsAndDomainNames dadn
151WHERE       (dadn.dbName = 'StackExchange.Meta'  OR  dadn.dbName NOT LIKE '%Meta%')
152
153-- Step through cursor
154OPEN    seSites_crsr
155FETCH   NEXT FROM seSites_crsr INTO @sitePrettyName, @seDbName, @seSiteURL
156WHILE   @@FETCH_STATUS = 0
157BEGIN
158    -- QUERY THAT YOU WANT TO RUN ON EACH SITE, GOES HERE
159    -- For example:
160    -- vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
161    SET @seSiteQuery = '
162        USE [' + @seDbName + ']
163
164        INSERT INTO #AllSiteResults
165        SELECT
166            ''' + @seSiteURL + '|' + @sitePrettyName + '''  AS [Site],  -- Creates a link
167            (SELECT Count(*) FROM Users)                    AS [User Count]
168    '
169    -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
170    EXEC sp_executesql @seSiteQuery
171
172    FETCH NEXT FROM seSites_crsr INTO @sitePrettyName, @seDbName, @seSiteURL
173END
174CLOSE       seSites_crsr
175DEALLOCATE  seSites_crsr
176
177-- ADJUST THIS QUERY IF ANY POST PROCESSING IS DESIRED.
178SELECT      *
179FROM        #AllSiteResults
180ORDER BY    [User Count] DESC, [Site]