Bulk table drop

[tmf]

CAn someone tell me how to script a bulk table drop within a database?

I have to delete some 20 odd tables in a database and I don't want to have to do it manually with individual mouse clicks.

Thanks

 

[philhege]

Sure, you can script it, but you have to get the table names into the script somehow.

One option is to have an admin table that holds the table names. Then,


declare @tablename sysname
declare @sql varchar(128)
select tablename, 0 as processed into #droptables
while (select count(*) from #droptables where processed = 0) > 0


BEGIN
select top 1 @tablename from #droptables where processed = 0
set @sql = 'DROP TABLE '+@tablename
exec (@sql)
update #droptables set processed = 1 where tablename = @tablename
END

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[tmf]

Phil,

Thank you for your advice. I managed to remove all the table and I am now getting a different error. Basically what is happening is that SharePoint wanted me to clear out the search table before enabling the search function. Now SP is throwing an error:

The specified database has an incorrect collation. Rebuild the database with the Latin1_General_CI_AS_KS_WS collation or create a new database.

Do you know how I can set these parameters?

Thank you again for your help.

Regards,
Tim

 

[Catadmin]

The ALTER DATABASE command can be used to change a single Database's collation.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"