1

I'm debating whether or not to try running through a list of tables and truncating them with a stored procedure. Would it be that easy with MySql and how would I do it?

stevebot
  • 23,275
  • 29
  • 119
  • 181
  • possible duplicate of [How can I truncate all tables from a MySQL Database?](http://stackoverflow.com/questions/4756430/how-can-i-truncate-all-tables-from-a-mysql-database) – OMG Ponies Jan 28 '11 at 00:49
  • @OMG Ponies: I think that's different. He's using the information schema to collect all tables in a database and then truncate them. I only want to truncate select tables, as defined in a list. For me the answer giving in that question would be a horrible fix! – stevebot Jan 28 '11 at 01:01
  • That is the way to get a list of tables -- SHOW TABLES is a synonym, and there're countless similar questions on SO: http://stackoverflow.com/search?q=[mysql]+truncate+tables – OMG Ponies Jan 28 '11 at 01:03
  • Yes, but can you define a list of tables as a variable or take in a list as an argument for the stored procedure? I'm sorry, but none of those answers really contain what I'm looking for. – stevebot Jan 28 '11 at 01:06
  • Now that you've clarified why the answer wouldn't work, if a parameter & it's incorporation is too difficult... – OMG Ponies Jan 28 '11 at 03:16

1 Answers1

1

The main piece of info you need is the list of tables. Most platforms support this:

select table_name from information_schema.tables

However, before you code the sproc, do a select * from information_schema.tables and examine the entries, there may be some you do not expect -- system tables and such, so you may need to craft a filter to get the set you want.

Since I don't do mySQL that much, I can't show you the code, but if you can translate this from MS SQL, and fill in some blanks you can make it work:

declare @table_name varchar(200)
while 1=1 begin
    select top 1 @table_name = table_name
      from information_schema.tables
     where ....possible filter...
    if @table_name is null break

    -- for this line you may need dynamic sql
    truncate table @table_name
end
Ken Downs
  • 4,707
  • 1
  • 22
  • 20
  • That's SQL Server, not MySQL syntax – OMG Ponies Jan 28 '11 at 03:15
  • 1
    @OMG Ponies, hence the line in the answer: "ince I don't do mySQL that much, I can't show you the code but if you can translate this from MS SQL, and fill in some blanks you can make it work:" – Ken Downs Jan 28 '11 at 12:26