For web parts and page templates you can use
select CONVERT(XML,PageTemplateWebParts), * from cms_pageTemplate where PageTemplateWebParts like '%searchstring%'
I was checking that way to see if an old macro method has been removed from our site.
PageTemplateWebParts - is the XML that you see on Web parts Tab in page template properties.
To see which pages are using this template you can do
select * from View_CMS_Tree_Joined where NodeTemplateID = YourTemplateID
P.S. People can hardcode HTML also in ContentBefore and ContentAfter - so you have to check those properties as well, but they are also in the same PageTemplateWebParts XML.
Anyway It all depends what web parts are in your template, for ex: if this is an editable text, then you have to dig even more deeper because the editable content is not in the PageTemplateWebParts XML.