SQL Database Table Sizes

May 25, 2017 / joe

The following SQL command lists each table in a database and shows total row counts and disk spaced used.

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

SQL Encrypted Fields

May 25, 2017 / joe

This document shows step-by-step instructions for setting up encrypted fields in a SQL database table.

/* Create table and insert data in the table */
USE EncryptTest
GO
CREATE TABLE PasswordTable (SEQ INT, AdminPassword VARCHAR(50))
GO
INSERT INTO PasswordTable (SEQ, AdminPassword)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO

/* Check the content of the PasswordTable */
USE EncryptTest
GO
SELECT *
FROM PasswordTable
GO

/* Create Database Master Key */
USE EncryptTest
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'Password-1234'
GO

/* Create Encryption Certificate */
USE EncryptTest
GO
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'Password-1234'
GO

/* Create Symmetric Key */
USE EncryptTest
GO
CREATE SYMMETRIC KEY PasswordTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert
GO

/* Encrypt Data using Key and Certificate
Add Columns which will hold the encrypted data in binary */
USE EncryptTest
GO
ALTER TABLE PasswordTable
ADD EncryptAdminPassword VARBINARY(256)
GO

/* Update binary column with encrypted data created by certificate & key */
USE EncryptTest
GO
OPEN SYMMETRIC KEY PasswordTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
UPDATE PasswordTable
SET EncryptAdminPassword = ENCRYPTBYKEY(KEY_GUID('PasswordTableKey'),AdminPassword)
GO

/* DROP original column which was encrypted to protect the data */
USE EncryptTest
GO
ALTER TABLE PasswordTable
DROP COLUMN AdminPassword
GO

/* Check the content of the PasswordTable */
USE EncryptTest
GO
SELECT *
FROM PasswordTable
GO

/* Decrypt the data of the AdminPassword */
USE EncryptTest
GO
OPEN SYMMETRIC KEY PasswordTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT SEQ, CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptAdminPassword)) AS DecryptAdminPassword, EncryptAdminPassword, RegStamp, DateStamp
FROM PasswordTable
GO

/* This is an example of adding a record with an encrypted field */
OPEN SYMMETRIC KEY PasswordTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
INSERT INTO PasswordTable
(EncryptAdminPassword, RegStamp, DateStamp)
VALUES(ENCRYPTBYKEY(KEY_GUID('PasswordTableKey'),'DogBreath91'),'test 2014.01','2014-01-15')
GO

The syntax for truncating a table in SQLite differs from that of SQL or mySQL. Truncation means that all records are deleted but the table structure is left in place. In addition, the record counter is reset to zero.

To truncate a table in SQLite, the following two commands are used:

Delete from TableName;
DELETE FROM SQLITE_SEQUENCE WHERE name='TableName';

SQL Summing daily totals

May 18, 2017 / joe

This example illustrates how to sum values collected daily into monthly categories.

SELECT DISTINCT
CONVERT(varchar(4), YEAR(Date)) + '-' + RIGHT('00' + CAST(DATEPART(mm, Date) AS varchar(2)), 2) AS PERIOD,
  SUM(Steps) AS TotalSteps,
  SUM(Floors) AS TotalFloors,
  SUM(Distance_mi) AS TotalMiles,
  SUM(Activity) AS ActiveMinutes,
 SUM(Calories) AS TotalCalories
 FROM  dbo.FitBit
 GROUP BY CONVERT(varchar(4), YEAR(Date)) + '-' + RIGHT('00' + CAST(DATEPART(mm, Date) AS varchar(2)), 2)

SQL Finding primary keys and missing primary keys

March 20, 2017 / joe

Tables with Primary Keys

SELECT c.name, b.name, a.name
 FROM sys.key_constraints a
 INNER JOIN sys.tables b ON a.parent_object_id = b.OBJECT_ID
 INNER JOIN sys.schemas c ON a.schema_id = c.schema_id
 WHERE a.type = 'PK'

Tables Without Primary Keys

SELECT c.name, b.name
 FROM sys.tables b
 INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
 WHERE b.type = 'U'
 AND NOT EXISTS
 (SELECT a.name
 FROM sys.key_constraints a
 WHERE a.parent_object_id = b.OBJECT_ID
 AND a.schema_id = c.schema_id
 AND a.type = 'PK' )

Meta