Archives: db

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

Compare performance of “Object.values()” vs “_.values()” vs “for…in” to extract values from an object

Create a custom password policy for ASP.NET Core Identity using extension methods

How to rename an Azure Function in Azure Portal