How to Purge the Sharepoint Search Database

If you need to stop and restart the Windows SharePoint Services Search Service in SharePoint 3.0/2007, you will be required to first completely purge the database and all stored procedures manually. Here's a quick way to do it.

When attempting to stop and restart the Windows SharePoint Services Search Service by simply clicking on "stop" and then "start" (seems reasonable), you will be confronted with this error after filling out the settings form:

WSS_Search_SERVERNAME on SERVER contains user-defined schema. Databases must be empty before they can be used. Delete all of the tables, stored procedures and other objects or use a different database.

This happens because SharePoint wants to build the search database from scratch after the service has been stopped. One solution is to just drop the database and re-create it, but that can be a big hassle if you have a separate database server in your SharePoint farm and everything is set up just the way you like it. It's hard to say why SharePoint wants to make such a big deal out of staring the service, but I'm sure there is a reasonable explanation. Anyway, here is a quick script you can run against the database after stopping it. This will delete all of the tables and stored procedures (there are no views). You will need to run it at least three times to take care of all of the table dependencies... and please make sure you run it against the right database or significant anguish could result!)

---

/* Delete tables and procedures from WSS_Search_* database
run this set of commands several times to ensure that all dependences removed
WARNING: wipes out tables and stored procedures! Make sure you run it against the right database!
Knowledge Pup - 2008 */
exec sp_msforeachtable "drop table ? print '? has been dropped.' "
declare @dropproc nvarchar(max)
declare @numprocs int
declare @pos int
declare @procs table (rowcnt int identity (1,1) primary key not null, pname varchar(100))
insert into @procs select name from sysobjects where xtype = 'P'
select @numprocs = count(*) from @procs
select @pos = 1
while @pos <= @numprocs
begin
select @dropproc = 'drop procedure ' + pname from @procs where rowcnt = @pos
execute sp_executesql @dropproc
select @pos = @pos + 1
end

---