Tag Archives: SQL

Enabling query logging on MariaDB/MySQL

When developing it can sometimes be very useful to see exactly what queries are sent to the database.

Found out there’s something called a General Query Log, and enabling it was really easy. Just add the following to your my.{ini,cnf} file:


Restart the server and you should now find the log in your data directory (unless no queries done yet).

If you don’t know where your data directory is, just run this query:

SHOW VARIABLES WHERE variable_name = 'datadir'

SQL Server evaluation license expiration date

Running the following SQL should give you how many days left until your evaluation license runs out. Also tried adding a calculation of when that date is. Either way, beware of off-by-one errors…

sp_configure 'show advanced options', 1;

sp_configure 'Agent XPs', 1;

DECLARE @daysleft INT
DECLARE @instancename sysname
SELECT @instancename = CONVERT(sysname, SERVERPROPERTY('InstanceName'))
EXEC @daysleft = xp_qv '2715127595', @instancename
    DATEADD(dd, @daysleft, CONVERT(DATE, getdate())) 'Expiration date',
    @daysleft 'Days left'

Source: When will my SQL Server Evaluation Edition expire?

Finding all constraints and their affected columns in an Oracle database

Found an SQL script to list all constraints in an Oracle database together with affected columns. Putting it here in case I need it again… Took a while to run, but sure beats having to look through all the table definitions to find what exactly is preventing me from deleting a row…

  AND UCC1.POSITION = UCC2.POSITION -- Correction for multiple column primary keys.

If you’re just looking for one particular constraint you can of course also add and UC.CONSTRAINT_NAME = 'SOME NAME' 🙂

SQL for listing all WordPress tags

When writing a post I sometimes find it difficult to choose what I should tag it with. I try reuse tags I already have to prevent a total mess, and sometimes I just don’t really remember what tags I have used so far. When writing a post in WordPress you can get a list of the most used ones, but once in a while I write a post on subject I haven’t written a lot about. So, instead of going to the Post Tags page and look through all the pages of tags, I decided to just connect to my blog database and run a query.

Continue reading SQL for listing all WordPress tags

How to search for a table or column in a big Oracle database

I needed to find columns containing a certain string. That is, the name of the column should contain that string. Actually not too difficult to do 🙂

SELECT owner, TABLE_NAME, column_name
FROM all_tab_columns
WHERE column_name LIKE '%FOO_ID%';

That gives you a nice list of all columns containing FOO_ID and what tables you find them in. Fantastic! Now, back to work…

How to delete WordPress post revisions

Having revisions on my posts is nice I suppose. But I was starting to get a bit annoyed with the increasingly long list of revisions in each post. And they really did get long. Mainly because I am in a bit of a testing and experimenting phase which means that I have done a lot of adjustments to almost every single post so far because I don’t have 100% control on how I want things or how things will end up looking and so on 😛 There’s also of course all the obligatory spelling errors I only discover after I have clicked Publish 😛

Anyways, I found a nice little MySQL snippet to clear out all of them in a French comment to a blog post. Thought I could share it here. That way I won’t lose it either 🙂

Continue reading How to delete WordPress post revisions