It’s reasonably easy to run multiple instances of MySQL with the mysqld_multi bash script. This can be really useful in development environments where you need to give several developers their own instance. To install multiple Microsoft SQL Server instances we have to get the install DVD and go through a laborious series of wizards. MySQL [...]

I ran across this error today whilst upgrading to an instance of MySQL 5.4. Unknown table engine ‘InnoDB’‏ I executed the following command at the MySQL client to see the available storage engines. ?View Code MYSQLSHOW ENGINES; This only listed the following table types; MRG_MYISAM MyISAM BLACKHOLE CSV MEMORY FEDERATED ARCHIVE Sure enough, No Innodb [...]

I’ve recently needed to script out the create sql for various MySQL database objects. No Powershell or SMO to help with this so I’ve quickly rolled a PHP script to get this done. This will script out all tables, views, triggers, stored procedures and functions from the specified database. One file per object in your [...]

Now I’m back working with MySQL on Linux I’m starting to learn Bash scripting to automate various tasks. Here’s a very simple script demonstrating how to interact with MySQL from Bash. Just set the localhost, user and pwd (password) variables to something appropriate for the MySQL server you want to query. The script will use [...]

Sometimes it’s useful to get a quick overview of what objects are referencing a particular table, view or function. This may arise when we think we may need to drop an object but want to double-check if anything in the database is still referencing it. Here’s a quick solution in the form of a Powershell [...]

Here’s neat little Powershell script you can use to audit your SQL Server databases. The script is dependant on the SQL Browser service, to discover instances, so you will need to make sure this is running. This will allow you to audit all SQL Server instances on the localhost with details of your databases and [...]

I’m a big fan of Redgate SQL Compare but it’s been good to see the arrival of a few free alternatives. Life previous to these tools really does seem like the stone age now eliminating those "oh $h**, I forgot about that!" moments. I’d always go for Redgate every time but, if you don’t have [...]

Following on from yesterdays blog post, a MySQL clone of sp_MsForEachTable, here’s an attempt at a clone of sp_spaceused. There’s a few issues to be aware of involving the storage engine in use. For example the row count is accurate for MyISAM while with InnoDb it seems to be just an estimate. This estimate can [...]

Many SQL Server DBAs and Developers get a  lot of use out of the undocumented sp_MsForEachTable system stored procedure. Here’s an attempt at creating a functional version for MySQL. The procedure makes use of prepared statements, which do have some limitations, so some uses may not translate across. This is far from production ready so [...]

Here’s a Powershell script that can be used to manage index fragmentation in SQL Server databases. The strategy I’ve used in the script is based on a recommendation from Pinal Dave (blog | twitter) in his article  Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script. Just set the $sqlserver and $database variables [...]