Sistem database (sys.) - SQL Server

select * from sys.objects
select * from sys.databases
select * from sys.tables
select * from sys.columns
select * from sys.views
select * from sys.triggers

INFORMATION SCHEMA - SQL SERVER

Mencari Nama Field
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Cek Constraints
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS

List
    INFORMATION_SCHEMA.CHECK_CONSTRAINTS
    INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
    INFORMATION_SCHEMA.COLUMN_PRIVILEGES
    INFORMATION_SCHEMA.COLUMNS
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
    INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
    INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
    INFORMATION_SCHEMA.DOMAINS
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    INFORMATION_SCHEMA.PARAMETERS
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
    INFORMATION_SCHEMA.ROUTINE_COLUMNS
    INFORMATION_SCHEMA.ROUTINES
    INFORMATION_SCHEMA.SCHEMATA
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    INFORMATION_SCHEMA.TABLE_PRIVILEGES
    INFORMATION_SCHEMA.TABLES
    INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
    INFORMATION_SCHEMA.VIEW_TABLE_USAGE
    INFORMATION_SCHEMA.VIEWS

Convert Format Tanggal - SQL Server

Untuk fomat tanggal YYYY-MM-DD
select convert(varchar, cust.tgllhr, 23) tgllhr from nasabah


Untuk lebih lengkap link format tanggalnya klik Di Sini

Store Procedure untuk report informasi hardisk dari suatu server, report by Email - SQL Server

USE [DATABASENYA]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SendEmailHardiskSize]     
as     
begin        
 SET NOCOUNT ON;     
   
     
DECLARE @XML1 NVARCHAR(MAX)   
DECLARE @XML2 NVARCHAR(MAX)   
 
DECLARE @BODY NVARCHAR(MAX)
DECLARE @TableBODY NVARCHAR(MAX)

DECLARE @IP_Addr VARCHAR(25)
DECLARE @intFlag INT
DECLARE @Count INT
DECLARE @Warna INT
SET @intFlag = 1


DECLARE @TABLE1 TABLE (     
    [IP] varchar(20)     
    ,[Host Name] varchar(30)
    ,[Drive] varchar(30)
    ,[Type] varchar(30)
    ,[Label] varchar(30)
    ,[Format] varchar(30)
    ,[Total Size] varchar(30)
    ,[Free Size] varchar(30)
    ,[Free %] varchar(30)
    ,[Use Size] varchar(30)
    ,[Use %] varchar(30)
    ,[BgRow] INT 
    ) 

select @Count=COUNT(IP_Addr) from SERVER_A@DB_SERVER_A.DB_SERVER_A.dbo.HDD_Inf
    where CONVERT(varchar,tgl,112) = CONVERT(varchar,GETDATE(),112)
        and [typeOf] <> 'CDRom'

    DECLARE SENDHDD CURSOR FOR
        select distinct IP_Addr from SERVER_A@DB_SERVER_A.DB_SERVER_A.dbo.HDD_Inf
            where CONVERT(varchar,tgl,112) = CONVERT(varchar,GETDATE(),112)
                and [typeOf] <> 'CDRom'
            order by IP_Addr;
    OPEN SENDHDD
    FETCH NEXT FROM SENDHDD INTO @IP_Addr
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @Warna = (@Count-@intFlag) % 2
       
        INSERT INTO @TABLE1   
            select  IP_Addr IP,
            Hostname [Host Name],
            drive [Drive],
            typeOf [Type],
            labels [Label],
            formats [Format],
            totalSize [Total Size],
            freeSize [Free Size],
            freePercent [Free %],
            useSize [Use Size],
            usePercent [Use %],
            @Warna
            from
                SERVER_A@DB_SERVER_A.DB_SERVER_A.dbo.HDD_Inf
                    WHERE IP_Addr=@IP_Addr
                    AND CONVERT(varchar,tgl,112) = CONVERT(varchar,GETDATE(),112)
                    AND [typeOf] <> 'CDRom'                   
        SET @intFlag = @intFlag + 1
    FETCH NEXT FROM SENDHDD INTO @IP_Addr
    END
    CLOSE SENDHDD
        DEALLOCATE SENDHDD
       
SET @XML1 =
        CAST ((SELECT
                case when [BgRow]=0 then '#90EE90' else '#FFDAB9' end AS [@bgcolor],
                td = [IP],'',
                td =  [Host Name],'',
                td =  [Drive],'',
                td =  [Type],'',
                td =  [Label],'',
                td =  [Format],'',
                td =  [Total Size],'',
                td =  [Free Size],'',
                td =  [Free %],'',
                td =  [USE Size],'',
                td =  [Use %],''
                    FROM (select [IP],[Host Name],[Drive],[Type]
                            ,case when [Label] = '' then '-' else [Label] end [Label]
                            ,case when [Format] = '' then '-' else [Format] end [Format]
                            ,case when [Total Size] = '' then '-' else [Total Size] end [Total Size]
                            ,case when [Free Size] = '' then '-' else [Free Size] end [Free Size]  
                            ,case when [Free %] = '' then '-' else [Free %] end [Free %]
                            ,case when [USE Size] = '' then '-' else [USE Size] end [USE Size]
                            ,case when [Use %] = '' then '-' else [Use %] end [Use %]
                            ,[BgRow]
                            from @TABLE1
                            )t FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX))

---to search where low space
SET @XML2 = CAST(( select  [IP] AS 'td'     
                    ,'',[Host Name] AS 'td'     
                    ,'',[Drive] AS 'td'
                    ,'',[Type] AS 'td'
                    ,'',case when [Label] = '' then '-' else [Label] end AS 'td'
                    ,'',case when [Format] = '' then '-' else [Format] end AS 'td'
                    ,'',case when [Total Size] = '' then '-' else [Total Size] end AS 'td' 
                    ,'',case when [Free Size] = '' then '-' else [Free Size] end AS 'td' 
                    ,'',case when [Free %] = '' then '-' else [Free %] end AS 'td' 
                    ,'',case when [USE Size] = '' then '-' else [USE Size] end AS 'td'
                    ,'',case when [Use %] = '' then '-' else [Use %] end AS 'td'
                                       
from @TABLE1 
where
Label not like '%VM%'
      AND Label not like '%VIRTUAL%'
      and cast(REPLACE([Free %],'%','') as int) <=10
                               
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))     
   
---to create row of table low space
set @TableBODY = '
Data Hardisk yang kurang dari 10%
<br> <table border = 1>      
<tr>     
    <th>Site Id</th>
    <th>Site Name</th> 
    <th>Status</th>
    <th>Type</th>
    <th>Label</th>
    <th>Format</th>
    <th>Total Space</th>
    <th>Free Space</th>
    <th>Free %</th>
    <th>Use Space</th>
    <th>Use %</th>
   
</tr>' +  @XML2 +
'</table><br>'


declare @Urgent varchar(5)
if @TableBODY is not null
begin
    set @Urgent= 'High'
end
else set @Urgent= 'Low'

 if @TableBODY  is null
 set @TableBODY='
 Data Hardisk yang kurang dari 10%
 <br> <table border = 1>      
<tr>     
    <th>Site Id</th>
    <th>Site Name</th> 
    <th>Status</th>
    <th>Type</th>
    <th>Label</th>
    <th>Format</th>
    <th>Total Space</th>
    <th>Free Space</th>
    <th>Free %</th>
    <th>Use Space</th>
    <th>Use %</th>
 </tr>
 <tr>     
    <th>-</th>
    <th>-</th> 
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
 </tr>
 ' +
'</table><br>'      
     
SET @BODY ='<html><body>     
<style>body,th,td {font-family :verdana; font-size:8pt;}     
    </style>  
Berikut ini adalah data HDD Space Report.<br>
Status HDD Storage Space digunakan untuk memonitoring dan memeriksa sisa Hardisk <br>
pada server Toko dan Server HO. Jika Space drive C: atau D: Jumlah Free space nya <br>
kurang dari 10% maka perlu dilakukan pengecekan hardisk agar space-nya cukup.<br>      
Periode : '+     
CONVERT(VARCHAR(11),GETDATE(),106)+     
 '<br>     
<br>
<br>
'
+ @TableBODY +
'  
<br>
Data Hardisk Size Keseluruhan
<br>
<table border = 1>      
<tr>     
    <th>Site Id</th>
    <th>Site Name</th> 
    <th>Status</th>
    <th>Type</th>
    <th>Label</th>
    <th>Format</th>
    <th>Total Space</th>
    <th>Free Space</th>
    <th>Free %</th>
    <th>Use Space</th>
    <th>Use %</th>
   
</tr>'     
SET @BODY = @BODY + @XML1 +'</table>'+     
 '<br><br>     
Email ini dikirimkan oleh System.<br><br>     
Procedure Name : DATABASENYA.[dbo].[SendEmailHardiskSize]     
</body></html>';   
     
--SEND EMAIL     
  EXEC msdb.dbo.sp_send_dbmail     
  @profile_name = 'Report Services',     
  @body = @body,     
  @body_format ='HTML',
  @importance =@Urgent,
  @recipients = 'noname@gmail.com',          
  @subject = 'Report Monitoring Hardisk Size ' ;     
     
end  ;   

GO

Contoh Store Procedure Dengan IF - SQL Server

USE [DATBASESNYA]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetUploadMember]
       (@StartMemberNo Varchar (100)
       ,@EndMemberNo Varchar (100)
       ,@StartDate SmallDateTime
       ,@EndDate SmallDateTime)
AS

BEGIN

-- ini jika ingin ngetes ngejalanin SP nya
--set @StartMemberNo = NULL
--set @EndMemberNo = NULL
--set @StartDate = '2010-05-21'
--set @EndDate = '2011-06-30'

--Filter Jika StartMemberNo blank atau tidak diisi
If @StartMemberNo = ''
    Begin
        Set @StartMemberNo = NULL
    End
--Filter Jika EndMemberNo blank atau tidak diisi
If @EndMemberNo = ''
    Begin
        Set @EndMemberNo = NULL
    End

--Filter Jika semua parameter tidak di isi
If @StartMemberNo IS NULL and @EndMemberNo IS NULL and @StartDate IS NULL and @EndDate IS NULL
    Begin
        Set @StartDate= cast(getdate() as Date)
    End

--Filter jika Startmemberno <> '' dan EndMemberno NULL atau ''
If @StartMemberNo <> '' and @EndMemberNo IS NULL
    Begin
        Set @EndMemberNo=@StartMemberNo
    End

--Filter jika Startmemberno NULL atau '' dan EndMemberno <> ''
If @StartMemberNo IS NULL and @EndMemberNo <> ''
    Begin
        Set @StartMemberNo=@EndMemberNo
    End

--Filter jika StartDate <> '' dan EndDate NULL atau ''
If @StartDate <> '' and @EndDate IS NULL
    Begin
        Set @EndDate=@StartDate
    End

--Filter jika StartDate NULL atau '' dan EndDate <> ''
If @StartDate IS NULL and @EndDate <> ''
    Begin
        Set @StartDate=@EndDate
    End

    Select MSR.[Card Number]
          ,CT.[Name]
          ,CT.[E-Mail] [Email]
          ,CT.[Phone No_] [Mobile Phone]
          ,CT.[Address] + ' '+ CT.[Address 2] [Address]
          ,CT.[City]
          ,MSR.[Ending Date]
    From [Nyamnyung Store, PT$MSR Card Link Setup] MSR With (Nolock)
         INNER JOIN [Nyamnyung Store, PT$Contact] CT With (Nolock) on MSR.[Link No_] = CT.[No_]
    Where [Card Number] >= Coalesce(@StartMemberNo,[Card Number])
          AND [Card Number] <= Coalesce(@EndMemberNo,[Card Number])
          AND [Date Created] >= Coalesce(@StartDate,[Date Created])
          AND [Date Created] <= Coalesce(@EndDate,[Date Created])
    Order By [Card Number]

END
GO

Contoh Store Procedure Dengan Dynamic Query - SQL Server

USE [DATABASENYA]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetDataMember]
     (
      @Status Varchar (max)
      ,@StartJoin datetime
      ,@EndJoin datetime
      )
AS
BEGIN
SET NOCOUNT ON;
declare @Query varchar(max)   
declare @filter varchar(max)

if @Status=''set @filter=null


if @Status <>''
begin   
    if @Status='Not Active'
    begin
        set @filter='And [Ending Date]<getdate()'
    end
    else
    if @Status='Active'
    begin
        set @filter='And [Ending Date]>=getdate()'
    end
   
end
else
begin
    set @filter=''
end
   

set @Query= 
    'SELECT
    [CardNumber],
    [Name],
    [Address],
    [City],
    [Post Code],
    [Phone No_],
    [Date Created],
    [Ending Date],
    [status]
FROM
(
    SELECT
    b.[Card Number] as [CardNumber],
    a.[Name],
    a.[Address],
    a.[City],
    a.[Post Code],
    a.[Phone No_],
    a.[Date Created],
    b.[Ending Date],
    (case
        when b.[Ending Date] >= GETDATE() then ''Active''
        else ''Not Active'' 
    end) as [status]
    FROM [Nyamnyung Store, PT$MSR Card Link Setup] b WITH(NOLOCK)
    left join [Nyamnyung Store, PT$Contact] a WITH(NOLOCK)
        on a.[No_]=b.[Link No_]
    where convert(date,a.[Date Created]) >= '''+ convert(varchar(10),@StartJoin,111)+'''
    and convert (date,a.[Date Created]) <='''+convert(varchar(10),@EndJoin,111)+'''
    '+@filter+'
) X
'
--print(@Query)
Exec(@Query);
END

GO

Backup Database - SQL Server

USE [DATABASENYA]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[backupdbase]
AS
    SET NOCOUNT ON;
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)
DECLARE @FileDay VARCHAR(20) -- used for file name
    -- Insert statements for procedure here
    --BACKUP DATABASE [STOCK_TAKE] TO  DISK = @idcomp WITH NOFORMAT, NOINIT,  NAME = N'STOCK_TAKE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
SET @path = 'D:\Backup'
SELECT @FileDay= DATENAME(DW,GETDATE())
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name <>'tempdb'

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 
BEGIN 

       SET @fileName = @path +'\'+ @FileDay +'\'+ @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName

       FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor 
DEALLOCATE db_cursor
GO

Cek User Proses - SQL Server

select *
from master..sysprocesses
order by cpu desc

--Cek prosesan/cpu tinggi--
select spid, waitresource, cpu, physical_io, memusage, status, hostname, program_name,nt_username, loginame
from master..sysprocesses
order by cpu desc

--Cek user yang diblok user lain--
select spid, blocked, waitresource, program_name, nt_username, loginame
from master..sysprocesses
where spid <> blocked
and blocked > 0

--cek user--
--dbcc inputbuffer(428)
--sp_who2 428

Pembulatan Angka Desimal - SQL Server

select round(5.123456 , 2) from dual ==> 5.12
select round(5.87654 , 2) from dual ==> 5.88

select floor(5.89) from dual ==> 5
select floor(5.12) from dual ==> 5

select ceil(5.89) from dual ==> 6
select ceil(5.12) from dual ==> 6

Menampilkan beberapa karakter tertentu dari belakang - PostgreSQL

Contoh ingin melihat 2 karakter terakhir. jika bukan 2, maka disesuaikan angka -1 dan 2 nya.

select
nama_nasabah,
substring(nama_nasabah,(length(nama_nasabah)-1),2) akhir
from trx_transaksi xx
where nama_nasabah<>''
limit 100

HASILNYA:
nama_nasabah                                       akhir
PT BHAKTI MITRA SELARAS            AS
JUMIRAH                                               AH
REVRA VENI                                         NI
SARNI                                                     NI
RADEN HAJI HAYE TEDJA DJUHAERAH        AH
SUPRIYONO                                          NO

Looping Insert Tanggal Urut atau Bertambah Tiap bulannya - PostgreSQL

Contoh kita ingin update suatu tanggal sesuai dengan contoh tanggal di bawah ini:

--update jwl_deperesiasi
DO $$
    Declare idkrt int;   
        Declare bulanke int;   
        DECLARE idaset int;   
        Declare jwl date;

    cur1 CURSOR for select id, depresiasi_bulan_ke, id_aset_inventaris, jwl_depresiasi from m_kartu_depresiasi where id_aset_inventaris=1530 order by depresiasi_bulan_ke;   
 begin 
OPEN Cur1 ; 
Loop     
    FETCH  next from Cur1 INTO idkrt, bulanke, idaset, jwl;    
    EXIT WHEN NOT FOUND;
    update m_kartu_depresiasi
    set jwl_depresiasi=((select jwl_depresiasi from m_kartu_depresiasi where depresiasi_bulan_ke < bulanke  and id_aset_inventaris=idaset order by depresiasi_bulan_ke desc limit 1) + (interval '1 MONTH'))
    where id_aset_inventaris=idaset and depresiasi_bulan_ke=bulanke and depresiasi_bulan_ke >1;
end loop ;  
CLOSE Cur1; 
END $$;

Import dan Restore database - PostgreSQL

A. Restore /Import versi windows

PostgreSQL SECARA LOCAL
1. C -> Program Files (x86) -> PostgreSQL -> 9.6 -> bin -> psql -U postgres

2. lalu masukkan password


SECARA GLOBAL/SERVER LAIN

1. C -> Program Files (x86) -> PostgreSQL -> 9.6 -> bin -> psql -U postgres -115.75.333.324
2. postgres=# \c bank_aty

(bpr_aty) nama database target

3. bank_aty=# \i C:/backup_data__20170619141626.backup

folder atau file jangan pakai spasi

B. Restore /Import versi LINUX (Ubuntu 16.04)

cara restore pake backup
pg_restore -i -h 10.5.2.30 -p 5432 -U postgres -d data_bank -v "C:/database_kosong.backup"

atau
pg_restore -h 10.5.2.30 -p 5432 -U postgres -d data_bank -v "C:/database_kosong.backup"

jika ada error seperti "FATAL: password authentication failed for user "postgres"
maka di pg_hba.conf ubah seperti ini:
local postgres postgres peer

lalu ALTER USER postgres PASSWORD 'my_postgres_password';

Sumber: https://hariatysimangunsong.blogspot.com/2019/06/cara-import-dan-restore-data-dum.html

Looping Sampai Angka Tertentu - PostgreSQL

DO $$
DECLARE
    x INTEGER;
begin
x :=1;
loop
    insert into zz_test_insert VALUES (x);
    x := x + 1;
    exit when x > 101;
end loop;
end $$;

----
DO $$
DECLARE
    x INTEGER;
begin
x :=1;
FOR x IN 1..100 LOOP
    insert into zz_test_insert VALUES (x);
    x := x + 1;  
end loop;
end $$;

-------------------------------------
DO $$
        DECLARE idkartu INTEGER;
        DECLARE bulan_ke INTEGER;
    Declare harga bigint;    
        Declare jangka int;    
        Declare tglbeli date;
        declare aset record;
 begin
idkartu :=(select COALESCE(max(id),0) idmax from m_kartu_depresiasi) + 1;
 bulan_ke :=1;

loop
  
    select jangka_waktu, id into aset from m_aset_inventaris where id=1592;  
  
    insert into m_kartu_depresiasi
    (id, depresiasi_bulan_ke, id_aset_inventaris,status_depresiasi)    values    (idkartu, bulan_ke, aset.id, false);
  
    idkartu := idkartu + 1;
    bulan_ke := bulan_ke + 1;
    exit when bulan_ke > aset.jangka_waktu;

end loop ;
END $$;