ALTER DATABASE name SET single_user WITH ROLLBACK immediate;
DROP DATABASE name;
DROP DATABASE name;
Tag Archives: SQL
Enable SQL logging in Play 2.5+
Just a note to self on how to get (in my case) IntelliJ to spit out all SQL statements happening during unit tests.
- Add the following to application.conf
db.default.logSql=true
- Add the following to logback.xml
<logger name="org.jdbcdslog.ConnectionLogger" level="OFF" />
<logger name="org.jdbcdslog.StatementLogger" level="INFO" />
<logger name="org.jdbcdslog.ResultSetLogger" level="OFF" />
Source: StackOverflow
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:
[mysqld]
general-log=1
general-log-file=queries.log
log-output=file
general-log=1
general-log-file=queries.log
log-output=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;
RECONFIGURE
GO
sp_configure 'Agent XPs', 1;
RECONFIGURE
GO
DECLARE @daysleft INT
DECLARE @instancename sysname
SELECT @instancename = CONVERT(sysname, SERVERPROPERTY('InstanceName'))
EXEC @daysleft = xp_qv '2715127595', @instancename
SELECT
DATEADD(dd, @daysleft, CONVERT(DATE, getdate())) 'Expiration date',
@daysleft 'Days left'
GO
RECONFIGURE
GO
sp_configure 'Agent XPs', 1;
RECONFIGURE
GO
DECLARE @daysleft INT
DECLARE @instancename sysname
SELECT @instancename = CONVERT(sysname, SERVERPROPERTY('InstanceName'))
EXEC @daysleft = xp_qv '2715127595', @instancename
SELECT
DATEADD(dd, @daysleft, CONVERT(DATE, getdate())) 'Expiration date',
@daysleft 'Days left'
GO
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…
SELECT UC.OWNER
, UC.CONSTRAINT_NAME
, UCC1.TABLE_NAME||'.'||UCC1.COLUMN_NAME "CONSTRAINT_SOURCE"
, 'REFERENCES'
, UCC2.TABLE_NAME||'.'||UCC2.COLUMN_NAME "REFERENCES_COLUMN"
FROM USER_CONSTRAINTS uc
, USER_CONS_COLUMNS ucc1
, USER_CONS_COLUMNS ucc2
WHERE UC.CONSTRAINT_NAME = UCC1.CONSTRAINT_NAME
AND UC.R_CONSTRAINT_NAME = UCC2.CONSTRAINT_NAME
AND UCC1.POSITION = UCC2.POSITION -- Correction for multiple column primary keys.
AND UC.CONSTRAINT_TYPE = 'R'
ORDER BY UCC1.TABLE_NAME
, UC.CONSTRAINT_NAME;
, UC.CONSTRAINT_NAME
, UCC1.TABLE_NAME||'.'||UCC1.COLUMN_NAME "CONSTRAINT_SOURCE"
, 'REFERENCES'
, UCC2.TABLE_NAME||'.'||UCC2.COLUMN_NAME "REFERENCES_COLUMN"
FROM USER_CONSTRAINTS uc
, USER_CONS_COLUMNS ucc1
, USER_CONS_COLUMNS ucc2
WHERE UC.CONSTRAINT_NAME = UCC1.CONSTRAINT_NAME
AND UC.R_CONSTRAINT_NAME = UCC2.CONSTRAINT_NAME
AND UCC1.POSITION = UCC2.POSITION -- Correction for multiple column primary keys.
AND UC.CONSTRAINT_TYPE = 'R'
ORDER BY UCC1.TABLE_NAME
, UC.CONSTRAINT_NAME;
If you’re just looking for one particular constraint you can of course also add and UC.CONSTRAINT_NAME = 'SOME NAME' 🙂
MySQL tables for continent names, country names and their ISO-3166 codes
Here is a MySQL table containing continent names, country names and their ISO-3166 codes.
Needed one a while ago, but the ones I found were either kind of lacking or kind of old. So I made one myself by converting a datafile on Wikipedia into the format I wanted. Used some regular expressions and manual corrections. Later I also went through newsletters with Updates on ISO 3166. Hopefully I got it all right, and hopefully it can save you and others some time as well.
I have also subscribed to their updates and try to follow up when they change anything.
If you find any mistakes or updates I’ve missed, please let me know 🙂
Continue reading MySQL tables for continent names, country names and their ISO-3166 codes
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.