SQL Query - Selecting all tables

[Dynix]

Hey,

Trying to convert ~70 tables to the character set latin1 and wondered if there was a quick way of doing this. SQL query I've got is:

ALTER TABLE tablename CONVERT TO CHARACTER SET latin1;

Sure, it works, but by that I would have to do each table 1-by-1.

Is there a quick "select all" I can put in replace for the tablename? I guessed * would work but it doesn't

Cheers.

 

[Buttons]

Look at the procedure sp_msforeachtable

Probably something like...

exec sp_MSForeachtable 'ALTER TABLE ? CONVERT TO CHARACTER SET latin1','?'

..off the top of my head. The ? marks are a deliberate wildcard so don't change 'em.

There is a procedure sp_MSForeachdb which is my favourite. Sad I know.

If you wanna do it yourself, cursors are your friend.

 

[Dynix]

Hey,

All done. Didn't realise there was an ALTER DATABASE command

Cheers anyways

 

[Buttons]

Be careful with alter database, not sure if that just sets the collation of all future tables.

To be honest I didn't realise alter table actually worked either but rarely deviate from Latin1 general anyway.

What am I talking about, not been a DBA for 12 months, should stop pretending!!