In this article I present several ways to check your PostgreSQL version.
Tag: how to
How to Return a List of Available Collations in PostgreSQL
In PostgreSQL, we can use the pg_collation
catalog to get a list of the available collations. Another way to do it is to use theĀ \dOS
command.
Using the first method, we can run the following statement to return a list of available collations in PostgreSQL:
SELECT * FROM pg_collation;
These collations areĀ mappings from an SQL name to operating system locale categories.
How to Return a List of Available Character Sets in MySQL
You can run the following statement to return a list of available character sets in MySQL:
SHOW CHARACTER SET;
This returns a list displaying the character set name, a description, its default collation, and its maximum length.
Continue reading
How to Find the Collations Supported by the Server in MySQL
Running the following statement lists all collations supported by the server in MySQL:
SHOW COLLATION;
However, the resulting list is quite long, and if you have a collation in mind, you can always filter the list with either the LIKE
clause or the WHERE
clause.
How to Find the Database Collations Supported by your SQL Server Instance
You can run the following query to return all the collations that are supported in your instance of SQL Server:
SELECT name, description FROM sys.fn_helpcollations();
Note that this returns quite a long list. Running that statement on SQL Server 2017 returns 3955 collations.
How to Change the Collation of a SQL Server Database using T-SQL
This is a quick article to demonstrate how to use Transact-SQL to change the collation of a database in SQL Server.
Here’s the basic code:
USE master; GO ALTER DATABASE Solutions COLLATE French_CI_AS ; GO
This first changes to the master database, then we use the ALTER DATABASE
statement to change the collation of the Solutions database to French_CI_AS collation.
Continue reading
How to Backup a SQL Server Database using T-SQL
You can use the T-SQL BACKUP DATABASE
statement to back up any SQL Server database.
This article shows you how to backup a database to disk. This creates a .bak file which can be used later to restore the database if required.
Continue reading
How to Rename a SQL Server Database using T-SQL
While you can certainly rename a SQL Server database in the SSMS GUI by right-clicking on the database name and selecting Rename, sometimes you might prefer (or need) to do it using Transact-SQL.
The most basic way to rename a database using T-SQL is like this:
-- Change to the master database USE master; -- Change the database name ALTER DATABASE Films Modify Name = Movies; GO
The only problem with this basic script is that it doesn’t change the name of the data files and log files. In most cases you’ll probably want to change the names of these files to match the new name. In that case you can take the following script and replace the database name with your own (as well as its file names and paths):
Continue reading
How to Specify the Location of Data Files and Log Files when Creating a Database in SQL Server
The simplest way to create a database in SQL Server is to use CREATE DATABASE my_database
without specifying anything else. When you do this, data files and log files are created in the default location (see how to find the default location).
However, sometimes you might want the data files and log files to reside in a different location. If that’s the case, use the following code example to explicitly state your own location for the database’s data files and log files.
USE master; GO CREATE DATABASE Solutions ON ( NAME = Solutions_dat, FILENAME = 'D:\mssql\data\Solutionsdat.mdf', SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB ) LOG ON ( NAME = Solutions_log, FILENAME = 'D:\mssql\data\Solutionslog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ); GO
That example uses Windows path conventions (starts with a drive letter and uses a backslash).
How to Find the Default File Location for Data Files and Log Files in SQL Server
Any time you create a database in SQL Server, two files are created. One is the data file, and the other is the transaction log file.
The location of these files will depend on whether or not you explicitly specify a location for these files when you create the database. If not, they will be created in the default location.
You can find the default location with the following code:
SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS 'Data Files', SERVERPROPERTY('InstanceDefaultLogPath') AS 'Log Files'