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 [...]

Getting data out of MySQL with SSIS is a snap. Putting data into MySQL has been a different matter. I’ve always done this in the past with a hodgepodge of ODBC, Linked Servers, OPENQUERY and the Script Task. All of these work well but they’re just not as convenient as loading files with the OLED [...]

Things have been rather quiet on this blog lately as I’ve just been finalizing the details for my new role as a MySQL DBA \ Developer. This may seem a little odd, considering TSQL is mentioned in my blog url, but I frequently blog about MySQL and have worked with it previously in a professional [...]

There’s few SQL techniques you seem to keep in the cupboard gathering dust. I don’t think I’ve ever needed to use RIGHT JOIN outside of the classroom. I can recall using FULL OUTER JOIN, just once, to show an employer how not-in-sync their "integrated system" was. Today I broke my professional Non-Equi JOIN cherry! I [...]

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 [...]

I’ve recently been busy documentation various systems at work and came up with these queries to get a list of databases and their sizes for each SQL Server. These queries will show the server name, database names, and their sizes in KB, MB and GB. For SQL Server 2005 & 2008 ?View Code TSQL– SQL [...]

Today I needed to order some data by specific column value and I recalled the really handy FIELD function in MySQL. Here’s a demo of this feature in MySQL ?View Code MYSQL# Test table CREATE TABLE City ( Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, City VARCHAR(50) );   # Insert some test data INSERT [...]

Steve Novoselac posted a good article about using CROSS APPLY with TSQL. This is a really useful technique for transforming data into grouped lists. It made me think of a similar feature in MySQL, a function, called GROUP_CONCAT. Here’s a demonstration of CROSS APPLY with TSQL and GROUP_CONCAT in MySQL to achieve the same thing. [...]

MySQL fulltext indexing can be a useful addition to a website requiring some better searching capabilities. Many blogging platforms based on MySQL, like wordpress, will be using fulltext indexes for their search features. While not as powerful as something like Lucene it certainly is a lot simpler to setup. Fulltext indexes can only be created on [...]