7 Skrip SQL yang Berguna untuk Pengembang SQL

Indonesian (Bahasa Indonesia) translation by Ari Ana (you can also view the original English article)

Banyak hal yang kita lakukan bergantung pada pengetahuan yang kita miliki. Jika kita sadar apa yang bisa dilakukan, baru kemudian kita bisa membuat keputusan yang lebih cerdas dan lebih efektif. Itulah mengapa selalu baik untuk memiliki tip dan trik cepat yang berguna di saku Anda. Prinsip ini berlaku di mana-mana, termasuk untuk pengembang MS-SQL.

Melalui artikel ini saya ingin berbagi beberapa skrip SQL yang telah terbukti sangat berguna untuk pekerjaan sehari-hari saya sebagai pengembang SQL. Saya akan menyajikan skenario singkat tentang di mana masing-masing skrip ini dapat digunakan bersama dengan skrip di bawah ini.

Catatan: Sebelum memetik manfaat dari skrip ini, sangat disarankan agar semua skrip yang disediakan dijalankan dalam lingkungan pengujian terlebih dahulu sebelum menjalankannya pada database waktu nyata untuk memastikan keamanan.

1. Mencari Teks di dalam Semua Prosedur SQL

Bisakah kita membayangkan hidup tanpa Control-F di dunia saat ini? Atau hidup tanpa mesin pencari! Mengerikan, bukan? Sekarang bayangkan Anda memiliki 20-30 prosedur sql dalam database Anda dan Anda perlu menemukan prosedur yang mengandung kata tertentu.

Jelas satu cara untuk melakukannya adalah dengan membuka setiap prosedur satu per satu dan melakukan Control-F di dalam prosedurnya. Tapi ini manual, repetitif, dan membosankan. Jadi, inilah skrip cepat yang memungkinkan Anda untuk mencapai ini.

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE m.definition Like '%search_text%'

2. Membandingkan Jumlah Baris dalam Tabel Dari Dua Database yang Berbeda dengan Skema yang Sama

Jika Anda memiliki database yang besar dan sumber data untuk database Anda adalah beberapa proses ETL (extract, transform, load) yang berjalan setiap hari, skrip berikutnya ini untuk Anda.

Katakanlah Anda memiliki skrip yang berjalan setiap hari untuk mengekstrak data ke dalam database Anda dan proses ini memakan waktu sekitar lima jam setiap hari. Ketika Anda mulai melihat lebih dalam proses ini, Anda menemukan beberapa area di mana Anda dapat mengoptimalkan skrip untuk menyelesaikan tugas dalam waktu kurang dari empat jam.

Anda ingin mencoba pengoptimalan ini, tetapi karena Anda sudah memiliki implementasi saat ini di server produksi, hal yang logis untuk dilakukan adalah mencoba proses yang dioptimalkan dalam database terpisah, yang akan Anda gandakan dengan menggunakan database yang ada.

Sekarang, setelah siap, Anda akan menjalankan kedua proses ETL dan membandingkan data yang diekstrak. Jika Anda memiliki database dengan banyak tabel, perbandingan ini dapat memakan waktu cukup lama. Jadi, inilah skrip cepat yang memfasilitasi proses ini.

use YourDatabase_1
CREATE TABLE #counts
(
table_name varchar(255),
row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

use YourDatabase_2
CREATE TABLE #counts_2
(
table_name varchar(255),
row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts_2 (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

SELECT a.table_name,
a.row_count as [Counts from regular run],
b.row_count as [Counts from mod scripts],
a.row_count - b.row_count as [difference]
FROM #counts a
inner join #counts_2 b on a.table_name = b.table_name
where a.row_count <> b.row_count
ORDER BY a.table_name, a.row_count DESC

3. Mencadangkan Banyak Databases Sekaligus

Dalam setiap perusahaan IT, hal pertama yang harus dilakukan oleh programmer baru (atau pengembang sql) sebelum menulis permintaan SQL pertamanya adalah membeli asuransi versi kerja dari database produksi, yaitu membuat cadangan.

Tindakan tunggal untuk membuat cadangan dan bekerja dengan versi cadangan memberi Anda kebebasan untuk melakukan dan mempraktekkan jenis transformasi data apa pun, karena memastikan bahwa bahkan jika Anda meledakkan data klien perusahaan, hal itu dapat dipulihkan. Bahkan, bukan hanya karyawan baru tetapi bahkan para veteran dari perusahaan IT yang sama tidak pernah melakukan transformasi data tanpa membuat cadangan.

Meskipun membuat cadangan database di SQL Server bukanlah tugas yang sulit, itu
pasti memakan waktu, terutama ketika Anda perlu membuat cadangan banyak database sekaligus. Jadi skrip selanjutnya cukup berguna untuk tujuan ini.

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'E:\\Sovit\_BackupFolder\'
exec master.dbo.xp_create_subdir @path

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('DB_1','DB_2','DB_3',
'DB_4','DB_5','DB_6') -- only these databases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name  
END

CLOSE db_cursor
DEALLOCATE db_cursor

4. Menyusutkan Banyak Log Database Sekaligus

Setiap database SQL Server memiliki log transaksi yang mencatat semua transaksi dan modifikasi database yang dibuat oleh setiap transaksi. Log transaksi adalah komponen penting dari database dan, jika ada kegagalan sistem, log transaksi mungkin diperlukan untuk membawa database Anda kembali ke keadaan yang konsisten.

Karena jumlah transaksi mulai meningkat, ketersediaan ruang mulai menjadi perhatian utama. Untungnya, SQL Server memungkinkan Anda untuk merebut kembali kelebihan ruang dengan mengurangi ukuran log transaksi.

Meskipun Anda dapat menyusutkan file log secara manual, satu per satu menggunakan UI yang disediakan, siapa yang punya waktu untuk melakukan ini secara manual? Skrip berikut ini dapat digunakan untuk menyusutkan banyak file log database dengan cepat.

DECLARE @logName as nvarchar(50)
DECLARE @databaseID as int

DECLARE db_cursor CURSOR FOR 
SELECT TOP 10 name,database_id -- only 10 but you can choose any number
FROM sys.master_Files WHERE physical_name like '%.ldf'
and physical_name not like 'C:\%'  -- specify your database paths
and name not in ('mastlog') -- any database logs that you would like to exclude
ORDER BY size DESC

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @logName , @databaseID

WHILE @@FETCH_STATUS = 0 
BEGIN 
    DECLARE @databaseName as nvarchar(50)
 SET @databaseName =  DB_NAME(@databaseID)

  DECLARE @tsql nvarchar(300)
 SET @tsql='USE ['+@databaseName+'] ALTER DATABASE ['+@databaseName+'] set recovery simple DBCC SHRINKFILE ('+@logName+' , 1)'
 EXEC(@tsql)

    FETCH NEXT FROM db_cursor INTO @logName , @databaseID
END 
CLOSE db_cursor
DEALLOCATE db_cursor

5. Membatasi Koneksi ke Database dengan Menetapkan Mode Single-User

Mode single-user menentukan bahwa hanya satu pengguna pada satu waktu dapat mengakses database dan umumnya digunakan untuk tindakan pemeliharaan. Pada dasarnya, jika pengguna lain terhubung ke database pada saat Anda mengatur database ke mode single-user, koneksi mereka ke database akan ditutup tanpa peringatan.

Ini sangat berguna dalam skenario di mana Anda perlu mengembalikan database Anda ke versi dari titik waktu tertentu atau Anda perlu mencegah kemungkinan perubahan oleh proses lain yang mengakses database.

USE master;
GO
ALTER DATABASE YourDatabaseName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE YourDatabaseName
SET READ_ONLY;
GO
ALTER DATABASE YourDatabaseName
SET MULTI_USER;
GO

6. Fungsi String dalam SQL untuk Menghasilkan Teks Dinamis

Banyak bahasa pemrograman memungkinkan Anda untuk memasukkan nilai-nilai di dalam teks string, yang sangat berguna ketika menghasilkan teks string yang dinamis. Karena SQL tidak menyediakan fungsi seperti itu secara default, ini adalah solusi cepat untuk itu. Dengan menggunakan fungsi di bawah ini, sejumlah teks dapat secara dinamis disisipkan di dalam teks string.

--Example Usage
--declare @test varchar(400)
--select @test = [dbo].[FN_SPRINTF] ('I am %s and you are %s', '1,0', ',') --param separator ','
--print @test -- result: I am 1 and you are 0
--select @test = [dbo].[FN_SPRINTF] ('I am %s and you are %s', '1#0', '#') --param separator ','
--print @test -- result: I am 1 and you are 0

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR: <SOVIT POUDEL>
-- =============================================
CREATE FUNCTION DBO.FN_SPRINTF
(
@STRING VARCHAR(MAX),
@PARAMS VARCHAR(MAX),
@PARAM_SEPARATOR CHAR(1) = ','
)
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @P VARCHAR(MAX)
DECLARE @PARAM_LEN INT

SET @PARAMS = @PARAMS + @PARAM_SEPARATOR   
SET @PARAM_LEN = LEN(@PARAMS)
WHILE NOT @PARAMS = ''
BEGIN
    SET @P = LEFT(@PARAMS+@PARAM_SEPARATOR, CHARINDEX(@PARAM_SEPARATOR, @PARAMS)-1)       
    SET @STRING = STUFF(@STRING, CHARINDEX('%S', @STRING), 2, @P)
    SET @PARAMS = SUBSTRING(@PARAMS, LEN(@P)+2, @PARAM_LEN)
END
RETURN @STRING

END

7. Mencetak Definisi Kolom Tabel

Ketika membandingkan beberapa database yang memiliki skema serupa, kita harus melihat detail kolom tabel. Definisi kolom (tipe data, nullables?) sama pentingnya dengan nama kolom itu sendiri.

Sekarang untuk database yang memiliki banyak tabel dan tabel yang memiliki banyak kolom, diperlukan beberapa waktu untuk membandingkan setiap kolom secara manual dengan kolom dari tabel lain dari database lain. Skrip selanjutnya dapat digunakan untuk mengotomatiskan proses ini karena mencetak definisi semua tabel untuk database tertentu.

SELECT
sh.name+'.'+o.name AS ObjectName,
s.name as ColumnName
,CASE
    WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
  WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
	WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
	ELSE t.name
END AS DataType
,CASE
	 WHEN s.is_nullable=1 THEN 'NULL'
	ELSE 'NOT NULL'
END AS Nullable       
	
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
INNER JOIN sys.objects o ON s.object_id=o.object_id
INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id

WHERE O.name IN
   (select table_name from information_schema.tables) 

ORDER BY sh.name+'.'+o.name,s.column_id

Kesimpulan

Dalam artikel ini, kita melihat tujuh skrip yang berguna yang dapat mengurangi banyak pekerjaan manual dan melelahkan serta meningkatkan efisiensi keseluruhan untuk pengembang SQL. Kita juga melihat skenario yang berbeda di mana skrip ini dapat diimplementasikan.

Jika Anda mencari lebih banyak skrip SQL untuk dipelajari (atau digunakan), jangan ragu untuk melihat apa yang bisa kita dapatkan di CodeCanyon.

Setelah Anda mulai memahami skrip ini, tentu Anda akan mulai mengidentifikasi banyak skenario lain di mana skrip ini dapat digunakan secara efektif.

Semoga berhasil!