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
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
Create referece
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:
“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
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,
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
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_addr
ess_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;
Related
