.::: T-SQL Script to Drop All Tables: Foreign-Key Tables First, Then Other Tables, While Excluding One Table (e.g., barang) :::.

 

Berikut beberapa pilihan query SQL Server untuk menampilkan daftar tabel yang memiliki Foreign Key dan diurutkan.

1. Urut berdasarkan nama tabel
SELECT DISTINCT 
    t.name AS TableName
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables t ON fk.parent_object_id = t.object_id
ORDER BY 
    t.name;
 

.::: Backup & restore table SQL Server Using bcp & SQL CMD like MariaDB :::.

 

A. Source (backup data )

1. check table barang 
  

2. Backup data as table using bcp 

bcp teguhth.dbo.barang out "C:\Backup\barang.txt" -c -t, -S localhost -U usertest -P passwordsql

bcp barang out "C:\Backup\barang.txt" -c -t, -S localhost -U usertest -P passwordsql -d teguhth
 

.::: Drop All tables in database using Truncate & drop :::.

correlation with https://teguhth.blogspot.com/2025/10/script-drop-all-database-non-system-in.html

1. Drop table using script
 
 

2. Drop table using script and duration
 

3. script drop_table_indb.sh

[root@teguhth tbldrop]# pwd
/data/drop/tbldrop
[root@teguhth tbldrop]# cat drop_table_indb.sh
#!/bin/bash
# ===========================================================
# Script Name  : drop_all_tbl_confirm.sh
# Tujuan       : Menghapus semua tables dari 1 Database MariaDB dengan konfirmasi
# OS Diuji     : CentOS 9
# Author       : Teguh Triharto
# ===========================================================

# === Konfigurasi koneksi ===
USER="admin"
PASS="admin"
HOST="localhost"
DBX="secretdb"
PORT="3306"

.::: Simulation API PATCH Curl Using Phyton & MariaDB MySQL with HTTP Status Code 500, 400, 200, 404 :::.

 

continue from https://teguhth.blogspot.com/2025/11/simulation-api-post-using-phyton.html

1. script get sample

[root@teguhth api]# cat patch_api_buy.py
from flask import Flask, request, jsonify
import mysql.connector

app = Flask(__name__)

def get_db():
    return mysql.connector.connect(
        host="localhost",
        user="admin",
        password="admin",
        database="hris"
    )

.::: Simulation API PUT Curl Using Phyton & MariaDB MySQL with HTTP Status Code 500, 400, 200, 404 :::.

 

continue from https://teguhth.blogspot.com/2025/11/simulation-api-post-using-phyton.html

1. script get sample

[root@teguhth api]# cat /data/api/put_api_buy.py
from flask import Flask, request, jsonify
import mysql.connector

app = Flask(__name__)

def get_db():
    return mysql.connector.connect(
        host="localhost",
        user="admin",
        password="admin",
        database="hris"
    )

.::: Simulation API GET Curl Using Phyton & MariaDB MySQL with HTTP Status Code 500, 400, 200, 404 :::.

 

continue from https://teguhth.blogspot.com/2025/11/simulation-api-post-using-phyton.html

1. script get sample

[root@teguhth api]# cat /data/api/get_api_buy.py
from flask import Flask, request, jsonify
import mysql.connector

app = Flask(__name__)

def get_db():
    return mysql.connector.connect(
        host="localhost",
        user="admin",
        password="admin",
        database="hris"
    )

@app.route('/get-buy-period', methods=['GET'])
def get_buy_period():
    # Ambil data dari query string
    year = request.args.get("year")
    month = request.args.get("month")
 

.::: Simulation API POST Using Phyton & MariaDB MySQL with HTTP Status Code 500, 422, 200, 405 :::.

 

1. Install phyton

sudo dnf install python3 python3-pip -y
pip3 install flask mysql-connector-python

2. create database sample & insert data  

CREATE DATABASE hris;
USE hris;

CREATE TABLE buy_period (
    id INT AUTO_INCREMENT PRIMARY KEY,
    period_year INT NOT NULL,
    period_month INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);
 

.::: Testing Script Lucee using Driver MongoDB Database (NoSQL) :::.

  

1. run mongo.cfm
 

2. run mongo_find as console
 

3. run mongo_table as table 

 

.::: Testing Script Lucee using Driver PostgreSQL, EDB, Oracle, MSSQL SQL Server Database :::.

 

1. run mssql

http://10.10.10.90:8888/belimssql.cfm
 

2. run edb

http://10.10.10.90:8888/beliedb.cfm
 

3. run oracle

http://10.10.10.90:8888/belioracle.cfm
 

.::: Install Lucee 6 In Almalinux 9 Centos 9 :::.

 

1. Install httpd & enable 

dnf install httpd -y

systemctl enable httpd
systemctl start httpd

2. download lucee wget https://cdn.lucee.org/lucee-6.2.3.35-linux-x64-installer.run

3. install lucee 

/opt/lucee-6.2.3.35-linux-x64-installer.run

[root@teguhth nitip2]# /opt/lucee-6.2.3.35-linux-x64-installer.run
----------------------------------------------------------------------------
Welcome to the Lucee Installer.

Tomcat Version: 11.0.13
Bundled Java: 21.0.9+10-LTS

----------------------------------------------------------------------------
Please read the following License Agreement. You must accept the terms of this
agreement before continuing with the installation.
 

.::: Testing Script Lucee using Driver MariaDB & MySQL to access MariaDB Database :::.

 

1. create database & access

CREATE DATABASE testdb;
CREATE USER 'luceeuser'@'%' IDENTIFIED BY 'Password123!';
GRANT ALL PRIVILEGES ON testdb.* TO 'luceeuser'@'%';
FLUSH PRIVILEGES;

CREATE TABLE IF NOT EXISTS test_table (
            id INT AUTO_INCREMENT PRIMARY KEY,
            pesan VARCHAR(255)
        );

INSERT INTO test_table (pesan) VALUES ('Halo dari Lucee di CentOS 9!');

SELECT * FROM test_table ORDER BY id DESC LIMIT 5;
 

.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in Oracle Database :::.

1. create table 

CREATE TABLE datefilter (
    datex DATE NOT NULL
);

CREATE TABLE datefilter (
    datex TIMESTAMP NOT NULL
);

2. create date sample 3 years

INSERT INTO datefilter (datex)
WITH cte AS (
    SELECT TRUNC(ADD_MONTHS(SYSDATE, 1 - LEVEL), 'MM') AS dt
    FROM dual
    CONNECT BY LEVEL <= 36
)
SELECT dt + INTERVAL '1' SECOND
FROM cte;

INSERT INTO datefilter (datex)
WITH cte AS (
    SELECT TRUNC(ADD_MONTHS(SYSDATE, 1 - LEVEL), 'MM') AS dt
    FROM dual
    CONNECT BY LEVEL <= 36
)
SELECT dt + INTERVAL '15' DAY + INTERVAL '1' SECOND
FROM cte;

.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in PostgreSQL EDB :::.

 

1. create table 

CREATE TABLE datefilter (
    datex TIMESTAMP NOT NULL
);

2. create date sample 3 years

WITH RECURSIVE cte AS (
    -- Mulai dari bulan ini
    SELECT date_trunc('month', now())::date AS dt,
           1 AS lvl

    UNION ALL
    
    -- Mundur 36 bulan
    SELECT (dt - INTERVAL '1 month')::date,
           lvl + 1
    FROM cte
    WHERE lvl < 36
)
INSERT INTO datefilter (datex)
SELECT dt + INTERVAL '1 millisecond'             -- tanggal 1
FROM cte
UNION ALL
SELECT dt + INTERVAL '14 days' + INTERVAL '1 millisecond'   -- tanggal 15
FROM cte;

.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in MarinaDB MariaDB :::.

 

1. create table 

CREATE TABLE datefilter (
    datex DATETIME NOT NULL
);

2. create date sample 3 years
INSERT INTO datefilter (datex)
WITH RECURSIVE cte AS (
    -- Mulai dari bulan ini (tanggal 1)
    SELECT
        DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AS dt,
        1 AS lvl

    UNION ALL

    -- Mundur 36 bulan
    SELECT
        DATE_SUB(dt, INTERVAL 1 MONTH),
        lvl + 1
    FROM cte
    WHERE lvl < 36
)
SELECT dt + INTERVAL 1000 MICROSECOND         -- tanggal 1
FROM cte

UNION ALL

SELECT (dt + INTERVAL 14 DAY) + INTERVAL 1000 MICROSECOND   -- tanggal 15
FROM cte;
 

.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in SQL Server MSSQL :::.

 

1. create table 

CREATE TABLE datefilter (
    datex DATETIME NOT NULL
);

2. create date sample 3 years

;WITH cte AS (
    -- Mulai dari bulan ini
    SELECT 
        CAST(DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS DATETIME) AS dt,
        1 AS lvl
    UNION ALL
    -- Mundur 36 bulan (3 tahun)
    SELECT 
        DATEADD(MONTH, -1, dt),
        lvl + 1
    FROM cte
    WHERE lvl < 36
)
INSERT INTO datefilter (datex)
SELECT DATEADD(MILLISECOND, 1, dt)              -- Tanggal 1
FROM cte
UNION ALL
SELECT DATEADD(MILLISECOND, 1, DATEADD(DAY, 14, dt))   -- Tanggal 15
FROM cte
OPTION (MAXRECURSION 0);

.::: Script Monitoring GTID Mirroring using Shell Script Colour :::.

 

1. Monitoring GTID no colour
 

 

.::: Install MariaDB 10.6, Maxscale, Keepalived in Suselinux SLES 15 :::.

 

1. enable repo 

teguhth01suse:~ # cat /etc/zypp/repos.d/mariadb.repo
[mariadb]
enabled=1
autorefresh=1
baseurl=https://yum.mariadb.org/10.6/sles/15/x86_64
gpgcheck=1
teguhth01suse:~ #

2. Prepare packages
rpm -qa | grep -i libmaria

zypper remove libmariadb3-3.1.22-150600.16.3.x86_64

rpm -qa | grep -i libmaria

rpm --import https://downloads.mariadb.com/MariaDB/MariaDB-Server-GPG-KEY
rpm --import https://downloads.mariadb.com/MariaDB/RPM-GPG-KEY-MariaDB

wget https://ftp.lysator.liu.se/pub/opensuse/update/leap/15.6/sle/x86_64/socat-1.8.0.0-150600.20.6.1.x86_64.rpm
zypper install socat-1.8.0.0-150600.20.6.1.x86_64.rpm

wget https://ftp.lysator.liu.se/pub/opensuse/update/leap/15.6/sle/x86_64/libasan8-14.3.0+git11799-150000.1.11.1.x86_64.rpm
zypper install libasan8-14.3.0+git11799-150000.1.11.1.x86_64.rpm

.::: Install MariaDB 10.6 in Debian 11 :::.

 

1. add update debian 

root@debian02:~# cat /etc/apt/sources.list
# deb cdrom:[Debian GNU/Linux 11.6.0 _Bullseye_ - Official amd64 DVD Binary-1 20221217-10:40]/ bullseye contrib main

#deb cdrom:[Debian GNU/Linux 11.6.0 _Bullseye_ - Official amd64 DVD Binary-1 20221217-10:40]/ bullseye contrib main

#deb http://security.debian.org/debian-security bullseye-security main contrib
#deb-src http://security.debian.org/debian-security bullseye-security main contrib

# bullseye-updates, to get updates before a point release is made;
# see https://www.debian.org/doc/manuals/debian-reference/ch02.en.html#_updates_and_backports
# A network mirror was not selected during install.  The following entries
# are provided as examples, but you should amend them as appropriate
# for your mirror of choice.
#
# deb http://deb.debian.org/debian/ bullseye-updates main contrib
# deb-src http://deb.debian.org/debian/ bullseye-updates main contrib

deb http://deb.debian.org/debian bullseye main contrib non-free
deb-src http://deb.debian.org/debian bullseye main contrib non-free

deb http://security.debian.org/debian-security bullseye-security main contrib non-free
deb-src http://security.debian.org/debian-security bullseye-security main contrib non-free

deb http://deb.debian.org/debian bullseye-updates main contrib non-free
deb-src http://deb.debian.org/debian bullseye-updates main contrib non-free
root@debian02:~#

.::: Script Drop All Database non-system in MariaDB MySQL in Centos 9 :::.

 correlation with https://teguhth.blogspot.com/2025/12/drop-all-tables-in-database-using.html

1. run script drop database non-system 

sh drop_database_nosystem_all.sh
 

2. run script drop database non-system with duration

sh drop_database_nosystem_all_duration.sh 

.::: Backup Database MariaDB with encripted -aes-256-cbc dan restore mariadb database :::.

1. backup enkripsi

mysqldump -uroot -pxxx teguhth | openssl enc -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc
mysqldump -uroot -pxxx teguhth | openssl enc -pass pass:abcde -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc
mysqldump -uroot -pxxx teguhth | openssl enc -pass pass:abcde -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc | gzip -c  > teguhthenc.gz.sql.enc
 
 

2. convert to sql from enkripsi file to sql
openssl enc -d -aes-256-cbc -md sha1 -in teguhthenc.sql.enc -out teguhth.sql -pass pass:abcde
 

.::: Script restore database MariaDB using record to table include duration Restore :::.

 

A. simple record

1. create table restore_log

CREATE TABLE IF NOT EXISTS restore_log (
    db_name VARCHAR(100),
    restore_time DATETIME,
    backup_file VARCHAR(255),
    restored_by VARCHAR(100)
);

2. create script 

.::: Script Backup Full & Incremental All Databases Using Record to Table include start, finish & duration backup in PostgreSQL EDB :::.

  

https://teguhth.blogspot.com/2025/02/script-backup-full-all-database-using.html
https://teguhth.blogspot.com/2025/09/script-backup-full-incremental-all.html

1. create database & table

CREATE TABLE backuplogduration (
    host VARCHAR(255) NOT NULL,
    backuptime TIMESTAMP NOT NULL,
    start TIMESTAMP NOT NULL,
    finish TIMESTAMP NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);

.::: Create CDB & Sample Database in Oracle 19C :::.

 

1. Login sebagai SYS atau SYSTEM

Masuk ke CDB lalu kasih privilege ke HRADMIN:

-- Masuk ke CDB sebagai SYS
sqlplus sys@//localhost:1521/TGHDB as sysdba

-- Pindah ke PDB TGHDBHR
ALTER SESSION SET CONTAINER=TGHDBHR;

-- Beri hak penuh

CREATE USER hradmin IDENTIFIED BY hrpass;
GRANT CONNECT, RESOURCE TO hradmin;
ALTER USER hradmin QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE TO hradmin;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO hradmin;

-- Kalau mau full DBA di PDB
GRANT DBA TO hradmin;

sqlplus hradmin/hrpass@//localhost:1521/TGHDBHR
 

.::: Create New Container or add Container CDB in Oracle 19C :::.

 

1. Cek dulu container yang ada

sqlplus sys/oracle as sysdba;

SHOW CON_NAME;
SHOW PDBS;
 

2. Buat PDB baru dari PDB$SEED (template bawaan)

Misalnya mau buat container baru bernama TGHDBHR:

CREATE PLUGGABLE DATABASE TGHDBHR
  ADMIN USER hradmin IDENTIFIED BY hrpass
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/TGH/pdbseed/',
                     '/u01/app/oracle/oradata/TGH/TGHDBHR/');

.::: First time login Oracle using CDB (Container Database / Multitenant) :::.

 

1. login as sysdba 
sqlplus sys/oracle as sysdba;

oracle@teguhth ~]$ sqlplus sys/oracle as sysdba;

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 24 14:12:31 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
 

.::: Script Backup Full & Incremental All Database Using Record to Table in PostgreSQL EDB :::.

correlation with https://teguhth.blogspot.com/2025/02/script-backup-full-all-database-using.html
correlation with https://teguhth.blogspot.com/2025/01/script-backup-full-all-database-using.html

1. create table 

-- Table: public.backuplogs

CREATE TABLE backuplogs (
    host VARCHAR(255) NOT NULL,
    date TIMESTAMP NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    backup VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);

.::: Script to Restore Backup Full & all Transaction (Restore Incremental) in one Command for EnterpriseDB PostgreSQL :::.

 

correlation https://teguhth.blogspot.com/2024/02/script-to-restore-backup-full-all.html
correlation https://teguhth.blogspot.com/2025/09/script-daily-backup-full-incremental.html

1. check before restore
 
2. check directory backup & create dblist,txt
[postgres@teguhth dwh]$ pwd
/var/lib/pgsql/16/manual/dwh
[postgres@teguhth dwh]$
[postgres@teguhth dwh]$ cat dblist.txt
FULL_dwh_20250913_000001.sql.gz
INC_dwh_20250913_010002.sql.gz
INC_dwh_20250913_020002.sql.gz
INC_dwh_20250913_030002.sql.gz
INC_dwh_20250913_040002.sql.gz
INC_dwh_20250913_050002.sql.gz
INC_dwh_20250913_060002.sql.gz
INC_dwh_20250913_070001.sql.gz
INC_dwh_20250913_080002.sql.gz
INC_dwh_20250913_090002.sql.gz
INC_dwh_20250913_100001.sql.gz
INC_dwh_20250913_110002.sql.gz
INC_dwh_20250913_120002.sql.gz
INC_dwh_20250913_130001.sql.gz
INC_dwh_20250913_140002.sql.gz
INC_dwh_20250913_150002.sql.gz
INC_dwh_20250913_160001.sql.gz
INC_dwh_20250913_170002.sql.gz
INC_dwh_20250913_180002.sql.gz
INC_dwh_20250913_190001.sql.gz
INC_dwh_20250913_200002.sql.gz
INC_dwh_20250913_210002.sql.gz
INC_dwh_20250913_220002.sql.gz
INC_dwh_20250913_230001.sql.gz
[postgres@teguhth dwh]$
 

.::: Script Daily Backup FULL & Incremental PostgreSQL EDB Using pgdump & pgaudit :::.

 

correlation with https://teguhth.blogspot.com/2025/09/how-to-create-backup-incremental-in.html

1. Backup All FULL
 

 

.::: How to Create Backup Incremental in PostgreSQL EDB as Logical Backup (like SQL Server or MariaDB with mysqlbinlog) using pgaudit pgauditlogtofile :::.

 

A. Activate pgaudit 
correlation https://teguhth.blogspot.com/2024/02/enable-pgaudit-pgauditlogtofile-in.html

B. convert to table 

correlation https://teguhth.blogspot.com/2024/04/convert-pgaudit-pgauditlogtofile-log.html

C. Testing sample CRUD Activity (sample database 'dwh')

1. create table pembelian;

create table barang(
KODE_BARANG char(6) not null ,
NAMA_BARANG varchar(25),
SATUAN_BARANG varchar(20),
STOK_BARANG decimal(4),
primary key (KODE_BARANG));
  

.::: Install MariaDB GTID Mirroring Monitoring using php :::.

 

install php https://infotechys.com/install-php-8-3-on-rhel-9-centos-9/

1. create bash monitoring 

[root@teguhth03 web]# cat /data/web/server31.sh
#!/bin/bash

# Variabel koneksi
USER="admin"
PASS="xxxxx"
HOST="10.10.10.31"
PORT="3306"

# Test koneksi ke MySQL server
mysql --skip-ssl -u$USER -p$PASS -h$HOST -P$PORT -e "SELECT 1;" >/dev/null 2>&1

 if [ $? -ne 0 ]; then
    echo "{ \"connections\": \"\", \"role\": \"Down\", \"gtid\": \"\",\"hostname\": \"$hostnamex\", \"host\": \"$HOST\", \"port\": \"$PORT\",\"dbv\": \"$versionx\",\"osv\": \"$osx\" }"
    exit 0
fi
 

.::: Install Microsoft SQL Server 2022 in Centos 9 / Redhat 9 / Almalinux 9 :::.

 

1. disable selinux

2. enable repo 

curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/$(rpm -E %{rhel})/mssql-server-2022.repo

curl https://packages.microsoft.com/config/rhel/8/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo

curl https://packages.microsoft.com/config/rhel/9/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo

[root@teguhth data]# curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/$(rpm -E %{rhel})/mssql-server-2022.repo
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   444  100   444    0     0    199      0  0:00:02  0:00:02 --:--:--   199
[root@teguhth data]#
[root@teguhth data]#
[root@teguhth data]# cat /etc/yum.repos.d/mssql-server.repo
[packages-microsoft-com-mssql-server-2022]
name=Microsoft SQL Server 2022
baseurl=https://packages.microsoft.com/rhel/9/mssql-server-2022/
enabled=1
gpgcheck=1
repo_gpgcheck=1
# If installing the bare repo file consider installing the gpg key once and then updating
# this to point to the on-disk location. For example:
# gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-Microsoft
gpgkey=https://packages.microsoft.com/keys/microsoft.asc
sslverify=1
[root@teguhth data]#

.::: Install MariaDB 10.6 in Ubuntu 22 Ubuntu 24 :::.

  

1. Tambahkan GPG Key mariadb

sudo apt install -y curl software-properties-common gnupg
curl -fsSL https://mariadb.org/mariadb_release_signing_key.asc | sudo gpg --dearmor -o /usr/share/keyrings/mariadb-keyring.gpg

2. Tambahkan Repository mariadb 10.6

deb [arch=amd64,arm64 signed-by=/usr/share/keyrings/mariadb-keyring.gpg] http://mirror.mariadb.org/repo/10.6/ubuntu noble main
 

.::: Script Backup & Restore MariaDB using Confirm before Action Execute :::.

 

A. Action 

1. test restore not found