GHOST Tags

0

Please login or register to vote for this query.

(click on this box to dismiss)

Server Fault

Q&A for system and network administrators

SELECT
  q.Id AS [Post Link],
  Tags.TagName AS 'Ghost Tag',
  ph.Text AS 'Actual Tag',
  q.LastEditDate AS 'Last modified'
from
  Posts q
    INNER JOIN PostTags ON q.Id = PostTags.PostId
    INNER JOIN Tags ON PostTags.TagId = Tags.id
    INNER JOIN (
      SELECT PostId, MAX(id) AS MaxId
      FROM PostHistory
      WHERE PostHistoryTypeId IN (3, 6, 9)
      GROUP BY PostId
    ) AS phx ON phx.PostId = q.Id
    INNER JOIN PostHistory AS ph ON phx.MaxId = ph.Id
WHERE
  -- Check if tag doesn't included in revision history
  CHARINDEX('<' + Tags.TagName + '>', ph.Text) = 0 AND
  -- Single tag handling
  Tags.TagName <> ph.Text AND
  -- Check question only
  q.PostTypeId = 1
ORDER BY
  q.LastActivityDate

Enter Parameters

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