Deleted tags

0

Please login or register to vote for this query.

(click on this box to dismiss)

Music Fans

Q&A for music historians, critics, and fans

create table #DeletedTags(TagName nvarchar(25),
                          PostHistoryId int,
                          PostId int)
declare cur cursor for select Id, PostId, Text from PostHistory
where PostHistoryTypeId = 3 or PostHistoryTypeId = 6
declare @id int
declare @postid int
declare @text nvarchar(135)
declare @i tinyint
declare @tag nvarchar(25)

open cur;
fetch next from cur into @id, @postid, @text
while @@FETCH_STATUS = 0 begin
  while len(@text) > 0 begin
    set @i = charindex('>', @text)
    set @tag = substring(@text, 2, @i-2)
    set @text = right(@text, len(@text)-@i)
    if not (exists (select id from Tags where TagName = @tag)) begin
      insert into #DeletedTags values (@tag, @id, @postid)
    end
  end
  fetch next from cur into @id, @postid, @text
end
close cur;

select
  dt.TagName,
  dt.PostId as [Post Link],
  ph.CreationDate as [Date],
  ph.UserId as [User Link],
  ph.Comment as [Comment]
from #DeletedTags dt
left join PostHistory ph on dt.PostHistoryId = ph.id
order by dt.PostHistoryId

Enter Parameters

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