SQL query examples for MSSQL

By admin, February 20, 2018

Some companies have dead set against open source due to possible vulnerabilities, others owing to the fact they have a huge [provider] base already installed or long term contracts. In order to handle that, I’ve selected some Microsoft SQL Server “Selects” and other commands one may find useful have in hand in future.

Disclaimer: Some are not new nor part of cutting-edge practices. They are here only to help in very specific scenarios.

MS SQL SERVER BACKUP RESTORE COMMANDS
 
https://sqlserver-help.com/2013/12/13/did-you-know-you-can-generate-insert-statement-with-data-using-management-studio/
USE [master]
RESTORE DATABASE [database_stage] FROM  DISK = N’D:\BackupBD\folder\database_stage_20170810.bak’ WITH  FILE = 1,  NOUNLOAD,  STATS = 5
GO
BACKUP DATABASE [database_stage] TO  DISK = N’D:\BackupBD\folderi\database_stage_20170810.bak’ WITH NOFORMAT, NOINIT,  NAME = N’database_stage-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
Further references
Dump referece
https://stackoverflow.com/questions/15735722/get-sql-file-from-sql-server-2012-database
Create referece
https://stackoverflow.com/questions/7828060/how-do-i-import-a-sql-data-file-into-sql-server
Via command line (big files)
TO dev/stage
PS C:\Users\humberto> sqlcmd -S mssql-mtl -i C:\dumps-mssql\DATABASE-STAGE-20170915.sql -o C:\
dumps-mssql\OUTPUT.TXT
TO LOCAL
PS D:\dumps-mssql\datafolder-stage> sqlcmd -S LAPTOP-DEV01\SQLEXPRESS  -i .\database-STAGE-20170922.sql -o OUTPUT1.TXT
Reference:
https://stackoverflow.com/questions/26200697/sql-server-login-success-but-the-database-dbname-is-not-accessible-object
“Complex” MSSQL Updates
They are not complex for real. They are just adapted to the targeted engine.

Reference:

https://stackoverflow.com/questions/12225715/update-statement-using-join-and-group-by

UPDATE J
SET J.StatusID = A.statusId
FROM MKT_JobOrder J INNER JOIN (
SELECT J.JobOrderID,
CASE WHEN SUM(DUV.VendorDUQuantity) = SUM(RD.InvoiceQuantity)
THEN 1 ELSE J.StatusID
END As statusId
PLN_DU_Vendor DUV
INNER JOIN ENG_Release R ON R.ReleaseID = DUV.ReleaseID
INNER JOIN ENG_DU_Header H ON H.ReleaseID = R.ReleaseID AND DUV.DUID = H.DUID
INNER JOIN MKT_JobOrder J ON J.JobOrderID = R.JobOrderID
INNER JOIN MKT_CustomerOrder CO ON CO.OrderID = J.OrderID
LEFT JOIN PMT_RFDHeader RH ON RH.JobOrderID = J.JobOrderID
LEFT JOIN PMT_RFDDetail RD ON RD.RFDID = RH.RFDID AND RD.DUID = DUV.DUID
WHERE
CO.OrderID = 100
GROUP BY

Other SQL Server references
https://www.sqlservercentral.com/Forums/Topic1530283-3411-1.aspx
https://www.isunshare.com/sql-server/how-to-enable-sa-account-in-sql-server.html#way1

SQL Server checking the number of tables in a database

SELECT count(*) FROM sys.objects WHERE Type_desc= ‘USER_TABLE’ AND is_ms_shipped <> 1;

Masking Emails SQLServer or MySQL
 
UPDATE  email_addresses SET email_address = CONCAT(LEFT(email_address, CHARINDEX(‘@’, email_address)), ‘example.com‘);
UPDATE  email_addresses SET email_address_caps = CONCAT(LEFT(email_address_caps, CHARINDEX(‘@’, email_address)), ‘EXAMPLE.COM‘);
OR
UPDATE  email_addresses SET email_address = CONCAT(LEFT(email_address, INSTR(email_address, ‘@’)), ‘example.com‘);
UPDATE  email_addresses SET email_address_caps = CONCAT(LEFT(email_address_caps, INSTR(email_address_caps, ‘@’)), ‘EXAMPLE.COM‘);

This is a database migration tool for MS SQL Server

Other interesting tools regarding checking if fields might harm the database performance, even direct migration, etc
Getting top X from all tables of a system
https://stackoverflow.com/questions/11741212/select-the-first-3-rows-of-each-table-in-a-database
USE database_stage;
DECLARE @sql VARCHAR(MAX)=”;
SELECT @sql=@sql+’SELECT TOP 10 * FROM ‘+'[‘+SCHEMA_NAME(schema_id)+’].[‘+name+’]’+’;’
  FROM sys.tables
EXEC(@sql)
Select row count of size of each table with size – complete
SELECT
t.NAME AS TableName,
i.name as indexName,
p.[Rows],
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
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
WHERE
t.NAME NOT LIKE ‘dt%’ AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY
object_name(i.object_id)
Stored procedure to generating inserts
https://github.com/drumsta/sql-generate-insert
Query for discovering customers (and admin) emails in SugarCRM 6 nd 7
— Discovering a customer email
SELECT * FROM database.email_addresses
INNER JOIN database.email_addr_bean_rel ON email_addresses.id = email_addr_bean_rel.email_address_id
INNER JOIN database.users ON users.id  = email_addr_bean_rel.bean_id;

Query to verify size of the databases schemas in MySQL

SELECT table_schema AS ‘Database’,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS ‘Size (MB)’
FROM information_schema.TABLES
GROUP BY table_schema;