db – Paris Polyzos' blog

A time may come when you will need to alter your database to use a different collation. You can do this by using the following snippet:

USE master;
GO
ALTER DATABASE [DatabaseName]
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

--Verify the collation setting.
SELECT name, collation_name
FROM sys.databases
WHERE name = N'[DatabaseName]';
GO

USE master;

ALTER DATABASE [DatabaseName]

COLLATE SQL_Latin1_General_CP1_CI_AS ;

--Verify the collation setting.

SELECT name, collation_name

FROM sys.databases

WHERE name = N'[DatabaseName]';

When you execute the above snippet you may come across the following error: This error occurs when your database is in Multi-User mode or when there are objects referring to your database. To overcome this error, you…

To get a rough view of how many rows, total, used and unused space each table has, in a sql server database you can run the following query:

USE {Database_Name};
GO 

SELECT
  t.Name                                       AS TableName,
  s.Name                                       AS SchemaName,
  p.Rows                                       AS RowCounts,
  SUM(a.total_pages) * 8                       AS TotalSpaceKB,
  SUM(a.used_pages) * 8                        AS UsedSpaceKB,
  (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
  sys.tables t
  INNER JOIN sys.indexes i ON t.object_id = i.object_id
  INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
  INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
  LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
  t.Name NOT LIKE 'dt%'
  AND t.is_ms_shipped = 0
  AND i.object_id > 255
GROUP BY
  t.Name, s.Name, p.Rows
ORDER BY
  t.Name;
GO

USE {Database_Name};

SELECT

t.Name AS TableName,

s.Name AS SchemaName,

p.Rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM

sys.tables t

INNER JOIN sys.indexes i ON t.object_id = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.Name NOT LIKE 'dt%'

AND t.is_ms_shipped = 0

AND i.object_id > 255

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

t.Name;

Another way is using the stored procedure sp_spaceused which displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in…

db

Change SQL Server Database Collation

Get size of tables in SQL Server

How to rename an Azure Function in Azure Portal

Publish an Angular web app to Azure using GitHub, AzureDevOps & Azure Storage Account

JavaScript Console Tips & Tricks