SQL Server Collations Find current collation and all supported collations

Have you ever needed to check your database or SQL instance to find out its collation? Have you ever wanted to check whether a collation is supported by Windows or SQL Server?

Well I am going to show you how to find out the information quickly and easily.

The below query will show the instance level collation.
SELECT SERVERPROPERTY(‘collation’)

1

The query below will show the collation setting at the database level

 

SELECT DATABASEPROPERTYEX(‘AdventureWorks2012’, ‘Collation’) SQLCollation

2

Sometimes its important to know the collations which the OS supports and the following query will help list all the collations which are supported by the OS.

 

SELECT * FROM sys.fn_helpcollations() WHERE name NOT LIKE ‘SQL%’;

3

When doing migrations it is very important to ensure that the Collation setting is maintained and to know whether it is supported by the SQL instance. The below query will help you easily and quickly ascertain that information.

 

SELECT * FROM sys.fn_helpcollations() WHERE name LIKE ‘SQL%’;

4

 

Hopefully that covers all your collation gathering requirements but if you do have another technique which I have missed then please leave them in the comments below.

 

List active process running using PowerShell

Have you ever wanted to quickly find the processes running using Powershell? Wanted an alternate to Task Manager? Well I am going to show you how.

Whenever I get a call from someone about a performance issue on any of our SQL Servers I always usually have a quick glance of Task Manager so that I can help identify what maybe causing the problem. However, the problem can be that on a server which is low on resources it may take a long time to open or the opening of task manager may add to the problems or it may not open at all.

This has led me to frequently opening PowerShell and using it to get the information which I need. The script is quite simple and is below.

Get-Process

1

I have found the advantage of using the script is that it gives me a snapshot whilst in task manager it is always a moving target and if I run it several times I can just scroll up and down to find a pattern which would help diagnose the problem.

Get the Operating System Properties via PowerShell

Have you ever wanted to get all the operating system properties of a computer? Well I am going to show you a short PowerShell script which can get loads of information about the operating system.

One of my colleagues was googling around for information about how to find the windows directory, number of users, last boot up time and many other properties of various computers. I told him that I had a short script which had that information and probably a whole lot more information which he might find useful.

$hostname = “parvinder-pc”

Get-WmiObject -class win32_operatingsystem -computer $hostname | select-object *

1