Category Archives: Databases

Monetdb : how to get schema and tables information ?

As other SQL database engines, monetdb allows to query schema and table information through SQL queries over a few system tables, namely : sys.schemas, sys.tables, and sys.columns.

The following query will give columns details for the system table “tables”:

SELECT c.id, c.name, c.type, c.type_digits 
    FROM sys.columns c LEFT JOIN sys.tables t ON c.table_id = t.id 
    WHERE t.name = 'tables';

The resultset :

+------+---------------+----------+-------------+
| id   | name          | type     | type_digits |
+======+===============+==========+=============+
| 5211 | id            | int      |          32 |
| 5212 | name          | varchar  |        1024 |
| 5213 | schema_id     | int      |          32 |
| 5214 | query         | varchar  |        2048 |
| 5215 | type          | smallint |          16 |
| 5216 | system        | boolean  |           1 |
| 5217 | commit_action | smallint |          16 |
| 5218 | access        | smallint |          16 |
| 5219 | temporary     | smallint |          16 |
+------+---------------+----------+-------------+ 

Monetdb : how to convert a string to a timestamp ?

Monetdb is great tool, with a not so great documentation… Converting a string/varchar to a timestamp is easy once you know the proper function to call.

The time conversion functions are listed in the [Monetdb installation folder]\MonetDB5\lib\monetdb5\createdb\13_date.sql file :

create function str_to_date(s string, format string) returns date
	external name mtime."str_to_date";

create function date_to_str(d date, format string) returns string
	external name mtime."date_to_str";

create function str_to_time(s string, format string) returns time
	external name mtime."str_to_time";

create function time_to_str(d time, format string) returns string
	external name mtime."time_to_str";

create function str_to_timestamp(s string, format string) returns timestamp
	external name mtime."str_to_timestamp";

create function timestamp_to_str(d timestamp, format string) returns string
	external name mtime."timestamp_to_str";

The syntax of the format string follows the MySQL one.

And eventually, here is a conversion example of a varchar to a timestamp

select str_to_timestamp('2016-02-04 15:30:29', '%Y-%m-%d %H:%M:%S');

How to import data from MySQL to SQL Server 2014 ?

Importing data from one database engine to another is often a pain. Getting the data out of MySQL is easy, but pushing it to SQL Server may be harder. The easiest way to achieve this is to use “SQL Server Import and Export Wizard” to directly query your MySQL Database, and push the data in your SQL Server.

Trying other techniques often leads to a waste of time. For example, Continue reading

How to execute the same SQL query on each database of a SQL Server instance ?

You want to execute the same SQL query on each database of your SQL Server. For example, because you have one database per customer and want to collect info about your customers’ databases version, size, etc.

The following T-SQL code illustrates how to get the number of tables in each database whose name starts with “RH”, and presents the result in one single recordset. Continue reading