Worked - Cross site

0

Please login or register to vote for this query.

(click on this box to dismiss)

Open Source Meta

Q&A about the site for people organizing, marketing or licensing open source development projects

CREATE TABLE #results (
  db           sysname,
  Id           int,
  Title        nvarchar(250),
  ViewCount    int,
  Score        int,
  CreationDate datetime);

DECLARE @exec nvarchar(500), @sql nvarchar(max), @c cursor;
declare @TagName nvarchar(100) = ##TagName:string##
-- TagName: The tag you're looking for


SELECT @sql = N'INSERT #results
  (db,Id,Title,ViewCount,Score,CreationDate)
  SELECT DB_NAME(), p.Id, p.Title, p.ViewCount, p.Score, p.CreationDate
  FROM Posts AS p 
  WHERE p.Title LIKE ' + cast(@TagName as nvarchar) + ' AND p.PostTypeId = 1 ';

SET @c = cursor FOR SELECT database_name
  FROM dbo.sede_databases WHERE notes IS NULL;

OPEN @c;
FETCH NEXT FROM @c INTO @exec;

WHILE @@FETCH_STATUS <> -1
BEGIN
  SET @exec = QUOTENAME(@exec) + N'.sys.sp_executesql';
  FETCH NEXT FROM @c INTO @exec;
END

SELECT Site        = sd.long_name, 
       Question    = CONCAT(sd.site_url, '/q/', r.Id, '|', r.Title),
       [# views]   = r.ViewCount,
       r.Score,
       [Posted on] = r.CreationDate
  FROM dbo.sede_databases AS sd
  INNER JOIN #results AS r 
     ON r.db = sd.database_name
  ORDER BY r.ViewCount DESC;

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results