Advertisement
  1. Code
  2. SQL

7 Skrip SQL yang Berguna untuk Pengembang SQL

Scroll to top
Read Time: 8 min

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.

1
SELECT DISTINCT o.name AS Object_Name,o.type_desc
2
FROM sys.sql_modules m
3
INNER JOIN sys.objects o
4
ON m.object_id=o.object_id
5
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.

1
use YourDatabase_1
2
CREATE TABLE #counts
3
(
4
table_name varchar(255),
5
row_count int
6
)
7
8
EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
9
10
use YourDatabase_2
11
CREATE TABLE #counts_2
12
(
13
table_name varchar(255),
14
row_count int
15
)
16
17
EXEC sp_MSForEachTable @command1='INSERT #counts_2 (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
18
19
SELECT a.table_name,
20
a.row_count as [Counts from regular run],
21
b.row_count as [Counts from mod scripts],
22
a.row_count - b.row_count as [difference]
23
FROM #counts a
24
inner join #counts_2 b on a.table_name = b.table_name
25
where a.row_count <> b.row_count
26
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.

1
DECLARE @name VARCHAR(50) -- database name

2
DECLARE @path VARCHAR(256) -- path for backup files

3
DECLARE @fileName VARCHAR(256) -- filename for backup

4
DECLARE @fileDate VARCHAR(20) -- used for file name

5
6
-- specify database backup directory

7
SET @path = 'E:\\Sovit\_BackupFolder\'

8
exec master.dbo.xp_create_subdir @path

9


10
-- specify filename format

11
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

12


13
DECLARE db_cursor CURSOR FOR

14
SELECT name

15
FROM master.dbo.sysdatabases

16
WHERE name IN ('DB_1','DB_2','DB_3',

17
'DB_4','DB_5','DB_6') -- only these databases

18


19
OPEN db_cursor

20
FETCH NEXT FROM db_cursor INTO @name

21


22
WHILE @@FETCH_STATUS = 0

23
BEGIN

24
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

25
BACKUP DATABASE @name TO DISK = @fileName

26


27
FETCH NEXT FROM db_cursor INTO @name  

28
END

29


30
CLOSE db_cursor

31
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.

1
DECLARE @logName as nvarchar(50)
2
DECLARE @databaseID as int
3
4
DECLARE db_cursor CURSOR FOR 
5
SELECT TOP 10 name,database_id -- only 10 but you can choose any number

6
FROM sys.master_Files WHERE physical_name like '%.ldf'
7
and physical_name not like 'C:\%'  -- specify your database paths

8
and name not in ('mastlog') -- any database logs that you would like to exclude

9
ORDER BY size DESC
10
11
OPEN db_cursor 
12
FETCH NEXT FROM db_cursor INTO @logName , @databaseID
13
14
WHILE @@FETCH_STATUS = 0 
15
BEGIN 
16
    DECLARE @databaseName as nvarchar(50)
17
 SET @databaseName =  DB_NAME(@databaseID)
18
19
  DECLARE @tsql nvarchar(300)
20
 SET @tsql='USE ['+@databaseName+'] ALTER DATABASE ['+@databaseName+'] set recovery simple DBCC SHRINKFILE ('+@logName+' , 1)'
21
 EXEC(@tsql)
22
23
    FETCH NEXT FROM db_cursor INTO @logName , @databaseID
24
END 
25
CLOSE db_cursor
26
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.

1
USE master;
2
GO
3
ALTER DATABASE YourDatabaseName
4
SET SINGLE_USER
5
WITH ROLLBACK IMMEDIATE;
6
GO
7
ALTER DATABASE YourDatabaseName
8
SET READ_ONLY;
9
GO
10
ALTER DATABASE YourDatabaseName
11
SET MULTI_USER;
12
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.

1
--Example Usage

2
--declare @test varchar(400)

3
--select @test = [dbo].[FN_SPRINTF] ('I am %s and you are %s', '1,0', ',') --param separator ','

4
--print @test -- result: I am 1 and you are 0

5
--select @test = [dbo].[FN_SPRINTF] ('I am %s and you are %s', '1#0', '#') --param separator ','

6
--print @test -- result: I am 1 and you are 0

7
8
SET ANSI_NULLS ON
9
GO
10
SET QUOTED_IDENTIFIER ON
11
GO
12
-- =============================================

13
-- AUTHOR: <SOVIT POUDEL>

14
-- =============================================

15
CREATE FUNCTION DBO.FN_SPRINTF
16
(
17
@STRING VARCHAR(MAX),
18
@PARAMS VARCHAR(MAX),
19
@PARAM_SEPARATOR CHAR(1) = ','
20
)
21
RETURNS VARCHAR(MAX)
22
AS
23
BEGIN
24
25
DECLARE @P VARCHAR(MAX)
26
DECLARE @PARAM_LEN INT
27
28
SET @PARAMS = @PARAMS + @PARAM_SEPARATOR   
29
SET @PARAM_LEN = LEN(@PARAMS)
30
WHILE NOT @PARAMS = ''
31
BEGIN
32
    SET @P = LEFT(@PARAMS+@PARAM_SEPARATOR, CHARINDEX(@PARAM_SEPARATOR, @PARAMS)-1)       
33
    SET @STRING = STUFF(@STRING, CHARINDEX('%S', @STRING), 2, @P)
34
    SET @PARAMS = SUBSTRING(@PARAMS, LEN(@P)+2, @PARAM_LEN)
35
END
36
RETURN @STRING
37
38
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.

1
SELECT
2
sh.name+'.'+o.name AS ObjectName,
3
s.name as ColumnName
4
,CASE
5
    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+')'
6
  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+')'
7
	WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
8
	ELSE t.name
9
END AS DataType
10
,CASE
11
	 WHEN s.is_nullable=1 THEN 'NULL'
12
	ELSE 'NOT NULL'
13
END AS Nullable       
14
	
15
FROM sys.columns s
16
INNER JOIN sys.types t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
17
INNER JOIN sys.objects o ON s.object_id=o.object_id
18
INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id
19
20
WHERE O.name IN
21
   (select table_name from information_schema.tables) 
22
23
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!

Advertisement
Did you find this post useful?
Want a weekly email summary?
Subscribe below and we’ll send you a weekly email summary of all new Code tutorials. Never miss out on learning about the next big thing.
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.