SANGSAKA NUSANTARA: tips and trik SQL SERVER 2000

Sinopsis singkat: Microsoft SQL Server 2000 telah dikenal sebagai salah satu server database yang paling

banyak digunakan. Salah satu keunggulannya adalah integrasinya dengan sistem operasi

Windows sehingga memudahkan operasinya.

Buku ini berisi sejumlah tips dan trik pada Microsoft SQL Server 2000 untuk tujuan

administrasi database maupun pemrograman Transact-SQL. Materinya ditujukan bagi

Administrator Database dan programmer tingkat lanjut yang telah terbiasa bekerja dengan

database dan menguasai perintah-perintah dasar SQL. Beberapa bab pertama dari buku ini

membahas tips-tips administrasi database. Pada bagian tersebut, pembaca diajak untuk

mengeksplorasi sisi konfigurasi dan optimasi database, pemantauan database dan

pemeliharaan database yang berguna pada pekerjaan administratif sehari-hari dalam SQL

Server. Bagian terakhir mengulas trik pemrograman T-SQL. Di dalamnya diulas cara

berinteraksi dengan SQL Server melalui pembuatan query yang efektif dan efisien. Semua

teknik ini dapat dengan mudah diadopsi dan diterapkan pada aplikasi-aplikasi yang sedang

dikembangkan.

BACKUP

4.1 Tips Backup Database

Berikut ini adalah 12 tips yang perlu diketahui seorang

Administrator Database dalam mem-backup database, terutama

jika database yang ditanganinya berukuran besar.

1. Walaupun server Anda dilengkapi tape drive, usahakan

melakukan backup database pada harddisk lokal dulu, setelah

itu salin atau backup lagi ke dalam tape.

Kerugian yang dirasakan jika Anda langsung mem-backup

database ke dalam tape adalah waktu backup yang

dibutuhkan akan lebih lama karena kecepatan tape drive

umumnya jauh lebih lambat dari pada harddisk. Kelambanan

proses ini juga akan berdampak pada unjuk kerja server

karena untuk waktu yang lama server akan terpengaruh proses

backup ini, apalagi jika saat itu database juga sedang

digunakan banyak pengguna yang melakukan INSERT,

UPDATE, dan DELETE terhadap data-data di dalamnya

sehingga hal tersebut juga akan membuat proses backup

semakin lama.

2. Lakukan backup ke dalam beberapa backup device. Dengan

cara ini, SQL Server akan membuat backup thread secara

terpisah sehingga backup akan dijalankan secara paralel.

3. Backup akan lebih cepat selesai jika dilakukan pada disk

array. Semakin banyak disk dalam array tersebut, semakin

cepat backup diselesaikan.

4. Lakukan backup pada waktu-waktu akses database rendah.

Hindari proses backup di tengah hari kerja yang sibuk.

5. Jika Anda mem-backup sebuah database untuk direstorasi lagi

di server lain, jalankan full backup untuk meminimalkan waktu

restorasi database. Walaupun jalannya full backup lebih lama

daripada differential dan incremental backup, proses

restorasinya merupakan yang tercepat.

6. Jika yang diutamakan adalah lama proses backup, jalankan

incremental backup karena ia yang tercepat dibandingkan

metode yang lainnya. Namun demikian, incremental backup

akan membutuhkan waktu paling lama saat restorasi database.

7. Pilih differential backup dibandingkan incremental backup jika

data dalam database sering berubah. Hal ini disebabkan

differential backup hanya mendeteksi data-data page yang

telah berubah sejak backup database terakhir dilakukan.

Dengan cara ini, waktu yang dibutuhkan server untuk

melakukan rolling forward transaction saat melakukan

recovery transaction log pada incremental backup bisa banyak

dihemat. Pada kasus ini, differential backup akan

meningkatkan proses recovery secara signifikan.

8. Usahakan untuk menempatkan database Anda pada beberapa

file dan filegroup sehingga Anda memiliki kesempatan untuk

mem-backup file/filegroup tertentu saja.

9. Gunakan Windows NT Performance Monitor atau Windows

2000 System Monitor untuk memantau unjuk kerja server

selama proses backup berlangsung. Counter yang bisa Anda

pantau adalah:

�� SQL Server Backup Device: Device Throughput Bytes/sec,

untuk memantau hasil pada backup device (bukan operasi

backup/restore secara keseluruhan).

�� SQL Server Databases: Backup/Restore Throughput/sec,

untuk memantau hasil backup/restore secara keseluruhan.

�� PhysicalDisk: % Disk Time, untuk memonitor aktivitas

baca/tulis.

�� Physical Disk Object: Avg. Disk Queue Length, untuk

memantau antrian permintaan akses ke disk.

10. Untuk mengurangi waktu backup, usahakan untuk membackup

lebih sering (tapi ingat bahwa backup tetap dianjurkan

dilakukan pada waktu akses database-nya rendah). Dengan

differential backup, semakin sering Anda melakukan backup,

semakin cepat prosesnya selesai karena SQL Server hanya

menangkap bagian-bagian data yang berubah.

Tentunya keuntungan melakukan backup yang lebih sering

adalah Anda mendapatkan backup data yang lebih up-to-date

sehingga kalaupun terjadi musibah, Anda bisa mengandalkan

backup tersebut.

11. Tempatkan tape drive pada SCSI bus yang terpisah dari disk

drive atau CD-ROM drive. Kebanyakan tape drive akan bekerja

lebih baik jika ia memiliki SCSI bus tersendiri untuk setiap tape

drive yang digunakan. Keberadaan bus untuk setiap tape drive

akan memaksimalkan unjuk kerja backup dan mencegah

konflik dengan drive lain. Microsoft menyarankan penggunaan

SCSI bus tersendiri untuk setiap tape drive dengan rasio

transfer lebih dari 50% dari kecepatan SCSI bus-nya.

12. Gunakan snapshot backup dari SQL Server 2000 untuk

database yang berukuran sangat besar. Teknologi snapshot

backup dan restore mendukung hardware dan software khusus

yang dibuat oleh vendor pihak ketiga. Keuntungan paling besar

dari snapshot backup adalah waktu operasinya yang dapat

menjadi sangat singkat.

4.2 Backup ke Network Share

Saat Anda hendak mem-backup sebuah database dengan SQL

Enterprise Manager, perhatikan bahwa program tersebut

tampaknya hanya mengizinkan Anda untuk menentukan lokasi

backup device pada drive atau direkrori lokal dari komputer yang

bersangkutan. Sekalipun Anda pernah membuat mapping drive

dengan sebuah network share (misalnya menggunakan perintah

NET USE), drive tersebut tidak akan muncul pada SQL Enterprise

Manager. Dengan kata lain, sepertinya SQL Server tidak

memungkinkan Anda untuk menempatkan hasil backup sebuah

database ke network share.

Kenyataannya tidak begitu. Anda bisa menempatkan hasil backup

sebuah database ke network share. Hal ini memang tidak

didokumentasikan, namun caranya sangat mudah. Saat SQL

Enterprise Manager menanyakan lokasi hasil backup, isilah

network share dan nama file backup-nya pada kolom File name

sesuai aturan Uniform Naming Convention (UNC). Setelah itu klik

tombol OK dan proses backup akan berjalan normal.

Gambar 4.1 Menentukan lokasi hasil backup

Pastikan bahwa network share tersebut dapat diakses secara

penuh (full control) oleh service account SQL Server tersebut.

4.3 Menyusutkan Transaction Log

Jika Anda memiliki sebuah database yang sudah beroperasi dalam

jangka waktu lama, Anda akan menemukan bahwa transaction log

database tersebut membengkak menjadi ukuran yang cukup

besar. Bukan tidak mungkin bahwa ukurannya melebihi ukuran

datanya itu sendiri. Hal ini bisa terjadi karena beberapa sebab:

1. Anda tidak atau jarang sekali mem-backup database tersebut.

Jika Anda tidak melakukan backup, transaction log tidak

pernah dikosongkan sehingga semua catatan aktivitas

database akan terus ditambahkan ke dalam transaction log

dan ukurannya akan terus membengkak, menyita ruang

harddisk komputer. Jika Anda mem-backup data atau

transaction log dari database tersebut, SQL Server secara

otomatis akan mengosongkan transaction log sehingga di

dalamnya akan tersedia cukup ruang kosong untuk mencatat

aktivitas baru tanpa harus menambah ukuran transaction log

tersebut.

2. Transaction log yang terus membengkak juga disebabkan

konfigurasi database yang mengaktifkan opsi autogrow

sehingga saat SQL Server mendeteksi bahwa transaction log

tidak memadai lagi untuk menampung catatan aktivitas

database, SQL Server secara otomatis akan memperbesar

ukuran transaction log tersebut. Walaupun fitur ini

meringankan pekerjaan seorang Administrator Database, hal

itu juga akan mengurangi kontrol Anda terhadap pertumbuhan

database. Apabila Anda jarang memantau ukuran database,

tidak aneh jika suatu saat Anda dikejutkan oleh transaction log

yang berukuran raksasa.

Untuk menghindari ukuran transaction log yang besar sekali,

secara periodik Anda harus melakukan pemeliharaan database.

Ada beberapa hal yang bisa dilakukan, yaitu:

1. Mem-backup data dari database. Pada SQL Server dikenal dua

macam cara untuk mem-backup database, yaitu full backup

dan incremental backup. Selain akan mem-backup data ke

dalam file atau media lain (misalnya tape), kedua cara ini juga

akan mengosongkan transaction log. Untuk mengetahui cara

mem-backup data, silahkan membaca SQL Server Online

Books.

2. Mem-backup transaction log dari database. Sama halnya

dengan data, Anda juga dapat mem-backup transaction log

agar setelah langkah ini dilakukan maka akan tersedia ruang

kosong pada transaction log tersebut. Untuk mengetahui cara

mem-backup transaction log, silahhkan membacanya SQL

Server Online Books.

Apabila Anda hanya ingin mengosongkan transaction log,

sebetulnya Anda tidak perlu mem-backup-nya ke dalam file

atau media, tetapi Anda bisa langsung mengosongkannya.

Sebagai contoh, perintah berikut ini akan menghapus isi

transaction log.

BACKUP LOG northwind WITH NO_LOG

Walaupun menggunakan perintah BACKUP, instruksi di atas

akan mengosongkan transaction log tanpa sungguh-sungguh

mem-backup-nya.

Apabila Anda menjalankan perintah BACKUP, yang dilakukan

pada transaction log hanyalah mengosongkan isinya. Ukuran fisik

transaction log itu sendiri tidak akan berubah. Jika Anda terlanjur

memiliki transaction log yang berukuran besar, Anda tidak bisa

berharap bisa memperkecilnya dengan hanya melakukan

BACKUP. Untuk lebih memahami penjelasan ini, kita akan

membuat sebuah eksperimen dengan database Northwind.

Pada gambar di bawah ini diperlihatkan diagram mengenai ukuran

database Northwind.

Anda bisa mendapatkan gambar ini pada program SQL Server

Enterprise Manager dengan cara:

1. Pada treeview, klik database Northwind.

2. Klik-kanan pada database Northwind, kemudian pilih menu

View | Taskpad.

Pada diagram database tersebut, Anda melihat bahwa ukuran

datanya adalah 3.25 MB (3 MB terisi data dan 0.25 MB kosong),

sedangkan ukuran transaction log-nya adalah 0.99 MB (0.49 MB

terisi data dan 0,5 MB kosong).

Sekarang kita akan membuat simulasi untuk mengisi 10.000 baris

data ke dalam sebuah table. Dengan cara ini, SQL Server akan

mencatat aktivitas database tersebut ke dalam transaction log

sehingga dengan sengaja kita akan membuatnya penuh dan

ukurannya membengkak (sebelumnya, pastikan bahwa opsi

autogrow untuk transaction log telah diaktifkan).

Silahkan eksekusi kode program di bawah ini pada SQL Query

Analyzer. Tunggu beberapa menit sampai program selesai.

Perhatikan bahwa selama waktu tersebut aktivitas SQL Server

akan meningkat drastis.

USE Northwind

DECLARE @I int

IF NOT EXISTS(SELECT * FROM sysobjects WHERE

name=’Dummy_Customer’)

SELECT *

INTO Dummy_Customer

FROM Customers

WHERE 1=0

SET @I=0

BEGIN TRAN

WHILE @I < 10000 BEGIN INSERT Dummy_Customer SELECT TOP 1 * FROM Customers ORDER BY NEWID() DELETE Dummy_Customer SET @I=@I + 1 END IF @@TRANCOUNT > 0

COMMIT TRAN

DROP TABLE Dummy_Customer

Setelah program berakhir, kembalilah ke SQL Server Enterprise

Manager dan refresh tampilan diagram database Northwind tadi.

Sekarang Anda bisa melihat bahwa ukuran transaction log telah

membengkak jauh melampaui ukuran sebelumnya.

Pada gambar di atas tampak bahwa ukuran transaction log

berubah menjadi 16,18 MB dimana ruang yang terisi adalah 9.68

MB. Fantastis bukan? Hanya dalam beberapa menit, Anda telah

membuat ukurannya berlipat ganda. Ini juga bisa terjadi pada

database Anda. Bayangkan seandainya Anda terus-menerus

menggunakan database tanpa pernah mempedulikan transaction

log!

Jika Anda menjalankan lagi program tadi berulang-ulang, ukuran

transaction log akan terus bertambah besar. Agar ukurannnya

tidak semakin membengkak, Anda perlu menghapus isi

transaction log. Pengosongan transaction log dapat dilakukan

dengan menjalankan sebaris perintah berikut ini pada SQL Query

Analyzer.

BACKUP LOG northwind WITH NO_LOG

Setelah perintah tersebut dijalankan, refresh lagi tampilan diagram

database pada SQL Server Enterprise Manager. Sekarang Anda

bisa melihat bahwa sebagian besar isi transaction log telah dihapus

sehingga tersisa 12,2 MB ruang kosong. Pada saat ini, jika Anda

menjalankan program simulasi tadi sekali lagi maka ukuran

transaction log tidak akan bertambah karena di dalamnya ada

ruang kosong yang cukup besar untuk mencatat aktivitas

database. Inilah gunanya Anda mengosongkan transaction log.

Kondisi database setelah pengosongan transaction log

Walaupun transaction log telah dihapus, ukuran fisiknya tetap

belum berubah, yaitu tetap 16,18 MB, baik sebelum maupun

sesudah perintah BACKUP LOG dieksekusi. Jika Anda ingin agar

ukurannya kembali ke ukuran semula (sekitar 1 MB), Anda dapat

menjalankan perintah berikut ini.

DBCC SHRINKFILE ( Northwind_log,1)

Perintah DBCC SHRINKFILE akan menyusutkan ukuran

transaction log ke ukuran yang diinginkan. Parameter yang harus

dimasukkan ke dalam perintah tersebut adalah:

1. Parameter pertama adalah nama transaction log. Nama yang

dimaksud di sini adalah nama logikal, bukan nama file

fisiknya. Untuk mengetahui nama-nama logikal dari file-file

database, Anda dapat menjalankan perintah berikut ini.

sp_helpdb northwind

Nama-nama logical dari file-file database

2. Parameter kedua adalah ukuran yang diinginkan dalam satuan

MB. Anda tidak perlu kuatir untuk menentukan ukurannya.

Jika SQL Server mendeteksi bahwa transaction log tidak

mungkin disusutkan sampai ke ukuran tersebut, SQL Server

akan melaporkannya pada Anda.

Jika perintah tersebut berhasil dieksekusi, Anda akan melihat pada

diagram database bahwa ukuran transaction log telah menyusut ke

ukuran yang diinginkan.

Gambar 4.7 Kondisi database setelah penyusutan transaction log

Dari eksperimen ini, kita dapat menarik beberapa kesimpulan:

1. Anda tidak boleh mengabaikan prosedur pemeliharaan

database. Sebagai seorang Administrator Database, Anda

harus senantiasa memantau database dan pertumbuhannya.

Secara periodik Anda harus melakukan backup database atau

mengosongkan transaction log agar ukurannya tidak

membengkak tidak terkendali.

2. Walaupun pengosongan transaction log mutlak harus

dilakukan, penyusutan ukuran fisik transaction log tidak perlu

dilakukan kecuali Anda memiliki alasan yang khusus.

Penjelasannya adalah karena bagaimana pun Anda tidak bisa

mencegah transaction log untuk bertumbuh. Jika database

Anda sedang beroperasi dan saat itu aktivitasnya sedang tinggi

sehingga SQL Server harus menambah ukuran transaction log,

Anda tidak bisa menahan hal itu terjadi. Jika sesudahnya Anda

menyusutkan lagi transaction log ke ukuran yang kecil tapi

kemudian aktivitas database tinggi lagi maka transaction log

akan membesar lagi ke ukuran tertentu. Jadi Anda tidak perlu

melakukan hal yang sia-sia seperti itu, bukan? Bahkan

sebaliknya, ukuran transaction log yang besar juga bisa

memberikan keuntungan. Anda harus menyadari bahwa setiap

kali SQL Server menambah ukuran transaction log, kinerja

database akan melorot drastis untuk beberapa detik hingga

beberapa menit. Hal ini dikarenakan adanya tambahan

pekerjaan yang dibebankan kepadanya untuk memperbesar

transaction log. Akan tetapi jika transaction log Anda sudah

cukup besar, overhead ini bisa dieliminasi sehingga performa

database relatif stabil.

4.4 Memindahkan File Database

Jika Anda memiliki sebuah database dengan ukuran yang besar

sementara ruang kosong pada disk penyimpanan database itu

akan segera habis, Anda perlu memindahkan file-file tersebut ke

disk lain sebelum mereka betul-betul memenuhi isi disk itu. Untuk

memindahkan file-file database, silahkan ikuti langkah-langkah

berikut ini:

1. Pastikan bahwa tidak satu pengguna pun yang terkoneksi ke

database tersebut.

2. Dari database master, jalankanlah stored procedure sp_helpdb

untuk melihat informasi dimana file-file database-nya

diletakkan. Pada umumnya file-file data akan mempunyai

ekstensi MDF atau NDF, sedangkan file transaction log akan

berekstensi LDF. Contoh:

EXEC sp_helpdb 'pubs'

Jumlah file-file itu akan bervariasi, bergantung pada

bagaimana database itu dulu dibuat.

3. Bila Anda telah siap, jalankanlah stored procedure

sp_detach_db dari database master, diikuti nama database-nya

dengan diapit tanda kutip. Contoh:

EXEC sp_detach_db 'pubs'

4. Setelah menjalankan sp_detach_db, database tersebut akan

„terlepas‰ dari SQL Server dan database itu tidak akan dikenali

lagi. Namun demikian, file-file databasenya (data dan

transaction log) masih tetap ada pada tempat semula.

5. Silahkan pindahkan file-file tersebut secara manual ke disk

lain.

6. Setelah file-file selesai dipindahkan, silahkan kembali ke

database master dan jalankan stored procedure sp_attach_db

diikuti nama database dan semua nama filenya berikut lokasi

lengkapnya. Contoh:

EXEC sp_attach_db @dbname = 'pubs',

@filename1 = 'd:\SQLData\pubs.mdf',

@filename2 = 'd:\SQLData\pubs_log.ldf'

Bila langkah ini berhasil dijalankan, database tersebut akan

kembali terdaftar pada SQL Server dan dapat digunakan

seperti biasa.

Catatan:

Cara lain untuk mengatasi masalah file-file yang memenuhi disk

adalah dengan menambahkan file-file database baru yang

dibuat pada disk lain dan membiarkan file-file yang ada

sebelumnya pada tempat semula. Agar file-file yang semula

tidak semakin membengkak, Anda harus menonaktifkan opsi

autogrow pada masing-masing file.

4.5 Membuat Skrip Objek

Dengan SQL Enterprise Manager, Anda dapat membuat skrip

pembuatan objek-objek database (CREATE). Namun demikian,

hal tersebut juga bisa dilakukan pada SQL Query Analyzer dengan

membuat sebuah program SQL yang cukup pendek. Cara kerja

program ini adalah dengan memanfaatkan fungsi-fungsi di

komponen SQL Distributed Management Objects (SQL-DMO)

yang dipanggil menggunakan stored procedure sp_OACreate,

sp_OASetProperty, dan sp_OAMethod.

Stored procedure sp_OACreate digunakan untuk membuat sebuah

instan objek OLE pada SQL Server.

Sintaks:

sp_OACreate progid, | clsid, objecttoken OUTPUT

[, context ]

Keterangan

progid : String, adalah programmatic identifier (ProgID)

dari objek OLE yang akan dibuat. Untuk SQLDMO,

ProgID-nya adalah 'SQLDMO.SQLServer'.

clsid : String, adalah class identifier (CLSID) dari objek

OLE yang akan dibuat. CLSID adalah sebuah

rangkaian karakter yang menggambarkan ID

class dari objek tersebut. Untuk SQL-DMO,

CLSID-nya adalah:

'{00026BA1-0000-0000-C000-000000000046}'

Untuk menjalankan sp_OACreate, Anda boleh

menggunakan ProgID atau CLSID, tapi

sebaiknya Anda menggunakan ProgID karena

tentunya lebih mudah dibaca.

objecttoken

[OUTPUT]

: Integer, adalah handle dari instan objek yang

dibuat. Handle ini harus dimasukkan ke dalam

sebuah variabel yang dideklarasikan lokal.

Handle ini digunakan untuk mengacu instan

objek tersebut selama penggunaannya.

context : Integer, opsional. Menentukan konteks eksekusi

dari instan yang dibuat. Nilai-nilai yang mungkin:

1 = In-process (.dll) OLE server only

4 = Local (.exe) OLE server only

5 = Both in-process and local OLE server allowed

Defaultnya adalah 5.

Stored procedure ini mengembalikan nilai integer, yaitu 0 jika

berhasil atau nilai lain jika gagal. Untuk menampilkan pesan

kesalahan, Anda dapat menggunakan sp_OAGetErrorInfo.

Stored procedure sp_OASetProperty digunakan untuk menset

property dari sebuah instan objek.

Sintaks:

sp_OASetProperty objecttoken, propertyname,

newvalue [ , index... ]

Keterangan

objecttoken : Integer, adalah handle dari instan objek.

propertyname : String. Nama property.

newvalue : Nilai yang akan diset ke dalam property

tersebut. Tipe datanya harus berkesesuaian

dengan property yang bersangkutan.

index : Integer. Index dari property jika ada.

Stored procedure ini mengembalikan nilai integer, yaitu 0 jika

berhasil atau nilai lain jika gagal.

Stored procedure sp_OAMethod digunakan untuk menjalankan

fungsi dari sebuah instan objek.

Sintaks:

sp_OAMethod objecttoken, methodname

[ , returnvalue OUTPUT ]

[ , [ @parametername = ] parameter [ OUTPUT ]

[ ...n ] ]

Keterangan

objecttoken : Integer, adalah handle dari instan objek.

methodname : String. Nama fungsi.

returnvalue

[OUTPUT]

: Nilai yang dikembalikan oleh fungsi

tersebut. Tipe datanya harus bersesuaian

dengan kembalian dari fungsi yang

bersangkutan.

[@parameternam

e =] parameter

[OUTPUT]

: Nilai parameter yang dimasukkan ke

dalam fungsi tersebut (jika ada). Apabila

sebuah parameter adalah pass-by-value,

Anda bisa langsung memasukkan nilainya

ke dalam stored procedure ini, tetapi jika

parameter tersebut adalah pass-byreference,

Anda harus menggunakan

sebuah variabel lokal diikuti OUTPUT.

Anda dapat mengirim beberapa parameter

dengan masing-masing dipisahkan oleh

koma. Urutan parameter-parameter yang

dikirim harus bersesuaian dengan ukuran

parameter dari fungsi tersebut. Namun

demikian, Anda dapat juga mengirimkan

parameter-parameter dengan urutan yang

tidak sesuai jika Anda menggunakan

@parametername. Perhatikan bahwa

@parametername bukan sebuah variabel

lokal, melainkan nama parameter dari

fungsi tersebut.

Stored procedure ini mengembalikan nilai integer, yaitu 0 jika

berhasil, atau nilai lain jika gagal.

Anda juga dapat menggunakan sp_OAMethod untuk mengambil

nilai sebuah property.

Stored procedure sp_OADestroy digunakan untuk menghapus

sebuah instan. Sintaks:

sp_OADestroy objecttoken

Berikut ini adalah kode program SQL untuk membuat skrip objek.

CREATE PROCEDURE p_object_script @obj_name varchar(50)=NULL AS

/**************************************************

Author : Feri Djuandi

Created : 21 May 2003

Desc. : Generate Object Script

**************************************************/

DECLARE @db_name varchar(50),

@obj_type char(2),

@command varchar(255),

@svr_object int,

@ret int,

@obj_script varchar(5000)

SET NOCOUNT ON

--Get current database name

SELECT @db_name=LEFT(the_db.name , 50)

FROM master..sysprocesses the_spid, master..sysdatabases the_db

WHERE the_spid.spid=@@SPID AND the_spid.dbid=the_db.dbid

EXEC @ret = sp_OACreate 'SQLDMO.SQLServer', @svr_object OUT

IF @ret <> 0

BEGIN

EXEC sp_OAGetErrorInfo @svr_object

RETURN

END

--Win NT login

EXEC @ret = sp_OASetProperty @svr_object, 'LoginSecure', TRUE

/* SQL Server Login

EXEC @ret = sp_OASetProperty @svr_object, 'Login', 'sa'

EXEC @ret = sp_OASetProperty @svr_object, 'password', '******'

SET @command = 'Connect('+RTRIM(@@SERVERNAME) +')'

EXEC @ret = sp_OAMethod @svr_object,@command

SET @obj_type=NULL

SET @obj_type=(SELECT dbobj.xtype FROM sysobjects dbobj

WHERE dbobj.id=OBJECT_ID(@obj_name) )

109

SET @command = (CASE

WHEN @obj_type IS NULL THEN 'Databases("' + @db_name +

'").Script(4)'

WHEN @obj_type='P' THEN 'Databases("' + @db_name +

'").StoredProcedures("'+ @obj_name + '").Script(4)'

WHEN @obj_type='V' THEN 'Databases("' + @db_name +

'").Views("'+ @obj_name + '").Script(4)'

WHEN @obj_type='U' THEN 'Databases("' + @db_name +

'").Tables("'+ @obj_name + '").Script(532750364)'

END)

EXEC @ret = sp_OAMethod @svr_object, @command, @obj_script OUTPUT

EXEC @ret = sp_OADestroy @svr_object

PRINT @obj_script

Program ini bisa digunakan untuk membuat skrip database, table,

view dan stored procedure. Contoh untuk membuat skrip dari table

Order:

EXEC p_object_script 'Orders'

Ouput:

CREATE TABLE [Orders] (

[OrderID] [int] IDENTITY (1, 1) NOT NULL ,

[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL , [EmployeeID] [int] NULL , [OrderDate] [datetime] NULL ,

[RequiredDate] [datetime] NULL ,

[ShippedDate] [datetime] NULL , [ShipVia] [int] NULL ,

[Freight] [money] NULL CONSTRAINT [DF_Orders_Freight] DEFAULT

(0), [ShipName] [nvarchar] (40) COLLATE