A SQL Server collation setting governs the code page to store non Unicode data in SQL Server and the rules to sort and compare non Unicode characters. The SQL Server installation automatically selects the collation depending on the windows collation on which it is being installed. If required here is how to change collation of a database.
Database collation information can be retrieved via T-SQL and GUI as shown below.
The function DATABASEPROPERTYEX can be used to get database collation as shown above. Additionally the collation details can also be obtained from sys.databases.
The collation settings can also be retrieved by right clicking database in SSMS object explorer and checking its properties as shown below.
The SQL Server database can also be changed by changing the Collation property from database property dialog box under the options pane, by selecting a value from the drop down list ( highlighted in green) as shown in above image.
Here is a T-SQL to change database collation.
The T-SQL ALTER DATABASE terminates with error if there are object which depend on database collation.