I decided to clean mine and some other blogs .Text databases after I've installed SQL-trigger based solution for blocking black listed words.
Maybe someone will find these SQL staments useful. Just change the green table and field names in italic to match your installation. Use with care!
This one will generate INSERT statements for your blacklist so you can copy the list to another database. Copy/paste and run on target database.
SELECT 'INSERT INTO MY_ANTISPAM_TABLE VALUES (''' + SpamWord + ''')' FROM BlackList
This one will generated DELETE statements for all words in black list table. Copy/paste the results and run and trackback and comments entries should dissapear.
SELECT 'DELETE FROM blog_content WHERE Title LIKE ''%' + SpamWord + '%''' FROM BlackList ORDER BY ID
Since .Text stores the feedback counts in the field so the numbers are not recalculated in every query, we have to update that count. This is SELECT the will get you the right numbers and some other info.
SELECT BCP.Title,
BCP.ID,
BCP.ParentID,
BCP.FeedBackCount,
COUNT(BC.ID)
FROM blog_Content BCP LEFT JOIN
blog_Content BC ON BCP.ID = BC.ParentID
WHERE BCP.ParentID = -1
GROUP BY BCP.FeedBackCount,
BCP.ID,
BCP.Title,
BCP.ParentID,
BCP.PostType
Now copy the results to Excel starting in cell A1. Paste this formula in cell G1, copy it down as far as needed. You have generated the UPDATE statements for all your posts. Copy/paste to Query Analyzer or equivalent and run.
="UPDATE blog_Content SET FeedBackCount = " & E1 & " WHERE ID = " & B1
My blog is relatively clean now. Have I missed something?