SQL Server Management Studio – Agunahwan's Blog

Query untuk melihat structure table

Setelah melewati 3 hari yang melelahkan, ingin kembali menulis kembali. Yuph, 2 hari akhir pekan merenovasi rumah di bogor, dan sehari ke rumah sakit seharian untuk imunisasi anak. Tak habis bahan untuk saya ulas, kali ini saya ingin membagikan query untuk melihat bagaimana struktur sebuah tabel maupun tabel-tabel dalam database.

Langsung aja ke inti utama yang akan saya sharing. Jadi, ceritanya saya ingin membuat dokumentasi berupa Data Dictionary untuk semua database yang dipakai. Task ini saya berikan kepada developer saya saat ia sedang tidak terlalu berat task. Setiap point merupakan sebuah penjelasan untuk semua tabel yang ada dalam setiap database dimana di dalamnya terdapat penjelasan mana yang merupakan tabel PK, FK, apakah nullable, apa tipe data dan berapa lengthnya, dan apakah ia unik. Yuph, kira2 semacam itu, dan teman-teman dapat menggunakan query ini untuk mendapatkan data lainnya dari tabel-tabel tersebut tentunya.

Pada contoh kali ini, saya menggunakan database pada contoh-contoh saya sebelumnya yakni database Akademik. Untuk query database, teman-teman dapat mendownload query database pada contoh kali ini pada link berikut :

https://drive.google.com/file/d/1KYiT2H_uPhfzMDJ8ZZclxBy685uXUFs1/view?usp=sharing

Atau teman-teman dapat me-restore-nya dengan menggunakan file bak pada link berikut :

https://drive.google.com/file/d/1dm7TsHlJqpG1YaQHBnq7r2g0lAJCrLLd/view?usp=sharing

Saya asumsikan teman-teman sudah memiliki databasenya. Teman-teman akan melihat secara singkat struktur key seperti berikut :

Table Structure

Ada dua kasus yang akan saya gunakan saat ini :

Saya ingin melihat semua struktur pada tabel Siswa, maka query yang dapat digunakan sebagai berikut :

WITH foreign_data
AS (
    SELECT DISTINCT f.parent_object_id
        ,fc.parent_column_id
        ,OBJECT_NAME(f.parent_object_id) TableName
        ,COL_NAME(fc.parent_object_id, fc.parent_column_id) ColName
        ,OBJECT_NAME(f.referenced_object_id) ReferencedTableName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
    INNER JOIN sys.tables ta ON ta.OBJECT_ID = fc.referenced_object_id
    )
SELECT tbl.name 'Table Name'
    ,c.NAME 'Column Name'
    ,t.NAME 'Data type'
    ,c.max_length 'Max Length'
    ,c.precision 'Precision'
    ,c.scale 'Scale'
    ,c.is_nullable 'Is Nullable'
    ,c.is_identity 'Is Identity'
    ,ISNULL(i.is_unique, 0) 'Is Unique'
    ,ISNULL(i.is_primary_key, 0) 'Primary Key'
    ,CASE 
        WHEN fk.ColName IS NULL
            THEN 0
        ELSE 1
        END 'Foreign Key'
FROM sys.tables tbl
INNER JOIN sys.columns c ON c.object_id = tbl.object_id
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id
    AND ic.index_id = i.index_id
LEFT OUTER JOIN foreign_data fk ON fk.parent_column_id = c.column_id
    AND fk.parent_object_id = c.object_id
WHERE c.object_id = OBJECT_ID('Siswa')

Maka hasilnya akan seperti berikut :

Get Structure Table

Saya ingin melihat semua struktur dari semua tabel yang ada pada database Akademik, maka query yang dapat digunakan sebagai berikut :

WITH foreign_data
AS (
    SELECT DISTINCT f.parent_object_id
        ,fc.parent_column_id
        ,OBJECT_NAME(f.parent_object_id) TableName
        ,COL_NAME(fc.parent_object_id, fc.parent_column_id) ColName
        ,OBJECT_NAME(f.referenced_object_id) ReferencedTableName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
    INNER JOIN sys.tables ta ON ta.OBJECT_ID = fc.referenced_object_id
    )
SELECT tbl.name 'Table Name'
    ,c.NAME 'Column Name'
    ,t.NAME 'Data type'
    ,c.max_length 'Max Length'
    ,c.precision 'Precision'
    ,c.scale 'Scale'
    ,c.is_nullable 'Is Nullable'
    ,c.is_identity 'Is Identity'
    ,ISNULL(i.is_unique, 0) 'Is Unique'
    ,ISNULL(i.is_primary_key, 0) 'Primary Key'
    ,CASE 
        WHEN fk.ColName IS NULL
            THEN 0
        ELSE 1
        END 'Foreign Key'
FROM sys.tables tbl
INNER JOIN sys.columns c ON c.object_id = tbl.object_id
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id
    AND ic.index_id = i.index_id
LEFT OUTER JOIN foreign_data fk ON fk.parent_column_id = c.column_id
    AND fk.parent_object_id = c.object_id
ORDER BY tbl.name

Maka hasilnya akan seperti berikut :

Get All Structure Table

Demikian sharing sedikit dari saya untuk mendapatkan semua struktur tabel yang ada. Mudah-mudahan bermanfaat buat teman-teman semua untuk mempermudah dalam mendapatkan informasi dari sebuah database terutama bagi yang sering berhubungan dengan database.

Sekian dari saya, terima kasih dan selamat beristirahat… 😀

Category: Database, Programming, Tentang Komputer | LEAVE A COMMENT

Juni 30

Menggunakan SQL Search untuk mencari kata di database

Halo semua, pada malam ini saya ingin membagikan sebuah software yang berguna banget buat saya pribadi saat saya kesulitan dalam pekerjaan untuk mencari sebuah tabel dalam sekumpulan stored procedure, function, maupun view.

Jadi, ceritanya begini, dulu saya pernah mengerjakan sebuah project dimana kalau dikategorikan termasuk project maintenance. Dan sudah bisa ditebak, yuph, dalam project itu aplikasi sudah ada dengan berbagai flow di dalamnya termasuk berbagai macam SP, function, dan view. Aplikasinya pun terikat/terhubung dengan beberapa aplikasi lainnya dan pastinya terdapat beberapa database di dalamnya (satu aplikasi satu database saat itu). Dalam task maintenance tersebut, terkadang saya mendapatkan task dimana saya diharuskan untuk menambahkan sebuah kolom dalam beberapa tabel. Dan sialnya, banyak stored procedure melakukan script insert tanpa menyebutkan detail kolom-kolom yang akan diinsert. Dalam kasus saya, insert yang dipakai biar ada gambaran contohnya yaitu insert into table1 select * from table2. Tahukan apa yang akan terjadi saat terjadi penambahan kolom pada table2 tapi tidak terjadi penambahan kolom yang sama pada table1? Yuph, akan terjadi error.

Nah, untuk mempermudah saya mencari query apa aja yang terhubung dengan table yang akan saya ubah (dalam kasus td table2), saya menggunakan tools ini untuk mencarinya. Tools tersebut adalah SQL Search dari RedGate. Eits… jangan khawatir, tools tersebut free kok, namun teman-teman mesti submit data dahulu saat mengunduhnya. Tools tersebut sebagai plugin pada aplikasi Ms SQL Server Management Studio. Berikut link untuk mengunduh toolsnya :

https://www.red-gate.com/products/sql-development/sql-search/installer/

Adapun setelah teman-teman melakukan instalasi, maka menu akan tampil pada toolbar SSMS. Kira-kira akan seperti ini tampilannya :

Dan cara penggunaannya pun terbilang mudah, teman-teman hanya perlu melakukan klik pada menunya seperti yang saya tandai pada gambar di atas. Maka tampilan akan seperti gambar di atas. Kemudian pilih database yang akan dilakukan pencarian, kemudian masukkan kata/kolom/tabel/apapun itu yang akan dicari pada kotak pencarian. Maka list hasil akan terlihat. Teman-teman pun dapat melakukan penyaringan tipe object apa aja yang akan dicari. Dalam contoh saya, saya ingin mencari semua yang berhubungan dengan kata name, baik itu kolom maupun tabel. Maka tampilan di laptop saya, hasil akan seperti berikut :

Dalam hasil pencarian tersebut akan terlihat semua kata yang terkait dengan kata “name”, baik itu sebagai kolom dalam sebuah tabel, maupun parameter & isi dari sebuah stored procedure.

Nah, untuk kasus awal saya sebelumnya, saya menggunakan kata kunci “SELECT * FROM table2” untuk pencarian sehingga saya mengetahui semua stored procedure dan fungsi, bahkan trigger yang melakukan insert dari table2 menggunakan query select * from.

Demikian sedikit info untuk sebuah tools yang sering saya pakai untuk pencarian kata terkait database SQL Server. Mudah-mudahan bermanfaat untuk teman-teman yang mengalami masalah yang sama dan tools tersebut menjadi solusi jitu untuk permasalahan teman-teman juga.

Terima kasih dan selamat malam… 😀

Category: Software, Tentang Komputer | LEAVE A COMMENT

S	S	R	K	J	S	M
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31