List of Bank Holidays For England in SQL Format

List of Bank Holidays For England in SQL Format

Get Social!

First off let’s create a table to store the bank holiday values. You may need to adjust this slightly depending on your SQL server technology being used (this was tested on MySQL/ MariaDB Server) but this is standard SQL dialect and should work on any RDBMS that respects the current SQL standards.

The next step is to insert the bank holiday values below. This table is currently for 2012 up to 2019 for England and Wales.

For a more up to date list, please see my github page.

Please submit a PR if you have collated a list yourself, or have any updates to an existing list. The repository currently covers the UK, but I’d be more than happy to accept a PR for other countries! 


View Table Sizes in MySQL/ MariaDB Databases

Get Social!

MySQL and MariaDB present a bunch of queryable objects that give you all sorts of insights into what’s happening with the database. 

The size of data stored in tabels is one such thing that can be easily queried directly in SQL, providing you have SELECT access to the information_schema. 

Create the two below views in a schema of your choice:

See schema_size and table_size for more info.

You can now query each view to get the size, in megabytes, of a whole schema or individual table respectivley. 

  • schema_size – displays the aggregated size of each schema in the database.
  • table_size – lists all tables in the database. You can add a filter to the table_schema column to limit the output.

MySQL/ MariaDB Table Size

Get Social!

Create a MySQL or MariaDB view to show the size of each table in the database:

See database size for more information.


MySQL/ MariaDB Schema Size

Get Social!

Create a MySQL or MariaDB view to show the aggregated size of each schema in the database:

See database size for more information.


Oracle ORA-02287: sequence number not allowed here

Get Social!

I’ve recently hit an issue when trying to include a sequence.nextval in an Oracle database view. the database throws the following error:

I can see why that might not be allowed – each time you select from the view you’ll get a new sequence number making it a bit daft for most circumstances.

I say most because I’ve had a need recently; ETL logic at my current client is held in database views with a boilerplate set of code that wraps around that to perform the table load. That’s fine until you want to fabricate ID’s (SIDs) as part of the load.

Solution

The solution here was to create a utility package to wrap the sequence that could be referenced in the view. See the below example code:

You can now query the view without any pesky ORAs.

See the test script here.


Oracle View Sequence Test

Get Social!

 


Visit our advertisers

Search

Quick Poll

How often do you change the password for the computer(s) you use?

Visit our advertisers