Working Log: SQL Server

Showing posts with label SQL Server. Show all posts

Tuesday, September 22, 2015

Searching Column Name From Table in Database

select table_name from information_schema.columns group by table_name

select table_name,column_name from information_schema.columns
where column_name like '%MGS_Groupcode%'

Saturday, May 23, 2015

How to get all user accounts SQL Server

Get the list of all SQL User Accounts SQL Server:

SELECT name AS Login_Name, type_desc AS Account_Type
FROM sys.server_principals
WHERE TYPE IN ('U', 'S', 'G')
and name not like '%##%'
ORDER BY name, type_desc

Get the list of all SQL User Accounts Database only

SELECT name
FROM sys.server_principals
WHERE TYPE = 'S'
and name not like '%##%'

Get the list of all Windows User Accounts only
SELECT name
FROM sys.server_principals
WHERE TYPE = 'U'

Get the list of all Windows Group User Accounts only
SELECT name
FROM sys.server_principals
WHERE TYPE = 'G'

Monday, September 15, 2014

Delete from data select in SQL Server

Delete from data select

example :

delete tm_access_det from tm_access_det
inner join tm_access_hdt a on AJD_HeaderID=AJH_ID
inner join tbmst_section on ajh_sectioncode=sec_sectioncode
inner join tbmst_employee on em_emplcode=AJD_Emplcode
where AJD_Emplcode=20285
and SEC_DEPARTMENT='QC'

update from data select in sql server

update From data select

example :

update tm_access_det set AJD_Emplcode=20033
from tm_access_det
inner join tm_access_hdr a on AJD_HeaderID=AJH_ID
inner join tbmst_section on ajh_sectioncode=sec_sectioncode
inner join tbmst_employee on em_emplcode=AJD_Emplcode
where AJD_Emplcode=10043

Tuesday, May 27, 2014

Contoh Stored Procedures SQL Server

Berikut Ini Contoh Stored Procedures Sederhana .

USE [ACCOUNTINGDB]

/****** Object: StoredProcedure [dbo].[spvw_SummFreight_ShippingCharge] Script Date: 05/28/2014 08:18:34 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER OFF

-- Create store procedure

-- By Kusdi Mei-2014

CREATE PROCEDURE [dbo].[spvw_SummFreight_ShippingCharge]

(

@month int,

@year int

)

--deklarasi variable

declare

@FRD_FRDCode int,

@FI_CurrencyCode int,

@air_import numeric(18,2),

@air_export numeric(18,2),

@sea_import numeric(18,2),

@sea_export numeric(18,2)

--set @month=5

--set @year=2014

begin

create table #Temp_FreightInvoice(

no_urut int,

forwadercode int,

CurrencyCode int,

air_import numeric(18,2) null,

air_export numeric(18,2) null,

sea_import numeric(18,2) null,

sea_export numeric(18,2) null)

--select * from #Temp_FreightInvoice

--insert from select

insert into #Temp_FreightInvoice

(forwadercode,CurrencyCode)

select FRD_FRDCode,FI_CurrencyCode

from accountingdb..TBTI_FreightInvoice

inner join accountingdb..vw_mst_currency on FI_CurrencyCode = cast(CRC_CurrencyCode as int)

inner join PPSDB..TB_MST_Forwarders on FRD_FRDCode = FI_ForwarderCode

where FI_flag <> 25 AND FI_Source = 22 and FI_Flag = 2 and month(FI_PaymentDate)=@month and year(FI_PaymentDate)=@year

group by FRD_FRDCode,FI_CurrencyCode

--buat deklarasi cursor

declare FreightInvoice cursor fast_forward for

select FRD_FRDCode,FI_CurrencyCode

from accountingdb..TBTI_FreightInvoice

inner join accountingdb..vw_mst_currency on FI_CurrencyCode = cast(CRC_CurrencyCode as int)

inner join PPSDB..TB_MST_Forwarders on FRD_FRDCode = FI_ForwarderCode

where FI_flag <> 25 AND FI_Source = 22 and FI_Flag = 2 and month(FI_PaymentDate)=@month and year(FI_PaymentDate)=@year

group by FRD_FRDCode,FI_CurrencyCode

Open FreightInvoice

Fetch next from FreightInvoice into @FRD_FRDCode,@FI_CurrencyCode

while @@Fetch_status = 0

begin

--summ air - import

select @air_import= sum(FI_Amount)

from accountingdb..TBTI_FreightInvoice

where month(FI_PaymentDate)=@month and year(FI_PaymentDate)=@year

and FI_ShippingMode='Air - Import' and FI_ForwarderCode=@FRD_FRDCode

and FI_CurrencyCode=@FI_CurrencyCode

--summ Air - Export

select @air_export= sum(FI_Amount)

from TBTI_FreightInvoice

where month(FI_PaymentDate)=@month and year(FI_PaymentDate)=@year

and FI_ShippingMode='Air - Export' and FI_ForwarderCode=@FRD_FRDCode

and FI_CurrencyCode=@FI_CurrencyCode

--summ Sea - Import

select @sea_import= sum(FI_Amount)

from TBTI_FreightInvoice

where month(FI_PaymentDate)=@month and year(FI_PaymentDate)=@year

and FI_ShippingMode='Sea - Import' and FI_ForwarderCode=@FRD_FRDCode

and FI_CurrencyCode=@FI_CurrencyCode

--summ Sea - Import

select @sea_export= sum(FI_Amount)

from TBTI_FreightInvoice

where month(FI_PaymentDate)=@month and year(FI_PaymentDate)=@year

and FI_ShippingMode='Sea - Export' and FI_ForwarderCode=@FRD_FRDCode

and FI_CurrencyCode=@FI_CurrencyCode

--update temp tabel

update #Temp_FreightInvoice

set

air_import=@air_import,

air_export=@air_export,

sea_import=@sea_import,

sea_export=@sea_export

Where forwadercode =@FRD_FRDCode and CurrencyCode =@FI_CurrencyCode

Fetch next from FreightInvoice into @FRD_FRDCode,@FI_CurrencyCode

end

Close FreightInvoice

Deallocate FreightInvoice

end

--select data temporary

select FRD_FRDName, CRC_CurrencyName, air_import, air_export ,sea_import , sea_export

from #Temp_FreightInvoice

inner join accountingdb..vw_mst_currency on CurrencyCode = cast(CRC_CurrencyCode as int)

inner join PPSDB..TB_MST_Forwarders on forwadercode=FRD_FRDCode

--drop temporary table

Drop table #Temp_FreightInvoice

Tuesday, February 4, 2014

Temporary Database SQL Server Membengkak

Saya sempet tidak percaya, tiba tiba database sql server tidak bisa menyimpan dengan Errornya Disk Full, setelah saya check ternyata benar,

Saya coba lihat beberapa database yang ada di server semua sizenya normal.
tambah aneh pikir saya!

Ternyata ... biang keroknya adalah TEMPDB alias Temporary Databasenya nya membengkak.

Bagaimana solusinya :

1. LAkukan Shrink Database dan Shrink File:

shrink database :
Klik kanan pada database -> Tasks ->Shrink -> Database

shrink file:
Klik kanan pada database -> Tasks ->Shrink -> File
catatan ketika shring file : typenya adalah Log nya bukan filenya.

2. Tambahkan Hardisk pada Server anda , kemudian buat menjadi beberapa drive.

3. Pindahkan Bebarapa Database yang menyatu dengan Directory Root karena temporary database defaultnya ke root directory.
kecuali ana pindahkan path TEMPDB nya. Tapi saya belum pernah coba sih.

4. Search and Drop tabel temporary yang ada pada View and Storeprocedure.

5. Restart service database/server database Secara Berkala agar tempdb nya kembalo kosong.

demikian tips dari saya , semoga bermanfaat.

Thursday, January 23, 2014

Membuat Temporary Table Pada SQL Server

Cara pertama anda bisa menggunakan function create table biasa.

create table #temp_access (id int, emplcode int, level int, status varchar(50))

kemudian coba chek hasilnya

select * from #temp_access

Cara kedua , dengan menggunakan select - insert : ngambil data dari tabel lain.

select AJH_ID, AJD_Emplcode , AJD_LevelCheckID, AJD_Status ,AJD_Username, AJD_LastUpdate , ajd_checkdetail
into #temp_access
from tm_accessdt
inner join tm_accesshd a on AJD_HeaderID=AJH_ID where AJD_Emplcode=20260

Monday, July 22, 2013

Menghitung Perbedaan Waktu Antara Dua Tanggal

Menghitung Perbedaan Waktu antara Dua Tanggal

Rumus :

DATEDIFF(datepart,startdate,enddate)

Untuk datepart anda bisa rubah

year
quarter
month
dayofyear
day
week
weekday
hour
minute
second
millisecond
microsecond
nanosecond

contoh :

SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS jml_hari

hasilnya = 61 Hari

SELECT DATEDIFF(hour,'2008-06-05','2008-08-05') AS jml_jam

hasilnya = 1464 Jam

Tuesday, July 2, 2013

Insert select pada SQL Server

Contoh :

select AJH_ID, '20249' AJD_Emplcode , AJD_LevelCheckID, AJD_Status ,'kusdi' AJD_Username, getdate() AJD_LastUpdate , ajd_checkdetail
from tm_accessjeisdt
inner join tm_accessjeishd a on AJD_HeaderID=AJH_ID
inner join tbmst_section on ajh_sectioncode=sec_sectioncode
inner join tbmst_employee on em_emplcode=AJD_Emplcode
where AJD_Emplcode=20472

kemudian tambahkan didepan select secript insert

Menjadi :

Insert into tm_accessjeisdt (AJD_HeaderID, AJD_Emplcode, AJD_LevelCheckID, AJD_Status , AJD_Username, AJD_LastUpdate , ajd_checkdetail)
select AJH_ID, '20249' AJD_Emplcode , AJD_LevelCheckID, AJD_Status ,'kusdi' AJD_Username, getdate() AJD_LastUpdate , ajd_checkdetail
from tm_accessjeisdt
inner join tm_accessjeishd a on AJD_HeaderID=AJH_ID
inner join tbmst_section on ajh_sectioncode=sec_sectioncode
inner join tbmst_employee on em_emplcode=AJD_Emplcode
where AJD_Emplcode=20472

Tuesday, June 11, 2013

Cara Reset Auto Increment Pada SQL Server

Berikut ini adalah cara reset auto increment pada SQL server.
fucntion yang digunakan

DBCC CHECKIDENT(nama_table, 24)

Contoh :

kita punya data 10 record dengan id max adalah 10.
kemudian dilakukan delete data maka id maxnya jadi 9.
dan apabila kita insert data maka auto increment pada id nya adalah 11.

untuk mereset auto increment agar idnya bisa kembali ke 10 maka anada bisa gunakan script ini :

DBCC CHECKIDENT(T_employee, RESEED,9)

Remark:
t_employee = table yang di reset
9 = nilai max auto increment. dan apabila ada insert data maka id nya 9 + 1 = 10

Cara Membuat Looping Pada SQL Server

Berikut ini adalah contoh perulangan dengan menggunakan fuction while

1) contoh looping WHILE

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
GO

Hasilnya:
1
2
3
4
5

2) contoh Looping WHILE dengan tambahan BREAK

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
IF @intFlag = 4
BREAK;
END
GO

Hasilnya :
1
2
3

3) contoh looping WHILE dengan CONTINUE dan BREAK

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
CONTINUE;
IF @intFlag = 4 -- This will never executed
BREAK;
END
GO

hasilnya:
1
2
3
4
5