Show detailed of table properties
February 28, 2008 — foruminformatikaUntuk mengetahui object2 yang ada di dalam database SQL Server ternyata bisa menggunakan scripting query yang disediakan sendiri oleh sql server. Untuk diketahui ini sangat berguna sekali buat developer pada level database maupun pada lever bahasa pemrograman.
1. Table sysobjects
Table ini mempunyai struktur sebagai berikut :
| Column_Name | Type | Computed | Length | Prec | Scale | Nullable |
| name | sysname | no | 256 | no | ||
| id | int | no | 4 | 10 | 0 | no |
| xtype | char | no | 2 | no | ||
| uid | smallint | no | 2 | 5 | 0 | yes |
| info | smallint | no | 2 | 5 | 0 | yes |
| status | int | no | 4 | 10 | 0 | yes |
| base_schema_ver | int | no | 4 | 10 | 0 | yes |
| replinfo | int | no | 4 | 10 | 0 | yes |
| parent_obj | int | no | 4 | 10 | 0 | no |
| crdate | datetime | no | 8 | no | ||
| ftcatid | smallint | no | 2 | 5 | 0 | yes |
| schema_ver | int | no | 4 | 10 | 0 | yes |
| stats_schema_ver | int | no | 4 | 10 | 0 | yes |
| type | char | no | 2 | yes | ||
| userstat | smallint | no | 2 | 5 | 0 | yes |
| sysstat | smallint | no | 2 | 5 | 0 | yes |
| indexdel | smallint | no | 2 | 5 | 0 | yes |
| refdate | datetime | no | 8 | no | ||
| version | int | no | 4 | 10 | 0 | yes |
| deltrig | int | no | 4 | 10 | 0 | yes |
| instrig | int | no | 4 | 10 | 0 | yes |
| updtrig | int | no | 4 | 10 | 0 | yes |
| seltrig | int | no | 4 | 10 | 0 | yes |
| category | int | no | 4 | 10 | 0 | yes |
| cache | smallint | no | 2 | 5 | 0 | yes |
2. Table syscolumns
Table ini mempunyai struktur sebagai berikut :
| Column_Name | Type | Computed | Length | Prec | Scale | Nullable |
| name | sysname | no | 256 | yes | ||
| id | int | no | 4 | 10 | 0 | no |
| xtype | tinyint | no | 1 | 3 | 0 | no |
| typestat | tinyint | no | 1 | 3 | 0 | yes |
| xusertype | smallint | no | 2 | 5 | 0 | yes |
| length | smallint | no | 2 | 5 | 0 | no |
| xprec | tinyint | no | 1 | 3 | 0 | no |
| xscale | tinyint | no | 1 | 3 | 0 | no |
| colid | smallint | no | 2 | 5 | 0 | yes |
| xoffset | smallint | no | 2 | 5 | 0 | yes |
| bitpos | tinyint | no | 1 | 3 | 0 | yes |
| reserved | tinyint | no | 1 | 3 | 0 | yes |
| colstat | smallint | no | 2 | 5 | 0 | yes |
| cdefault | int | no | 4 | 10 | 0 | no |
| domain | int | no | 4 | 10 | 0 | no |
| number | smallint | no | 2 | 5 | 0 | yes |
| colorder | smallint | no | 2 | 5 | 0 | yes |
| autoval | varbinary | no | 8000 | yes | ||
| offset | smallint | no | 2 | 5 | 0 | yes |
| collationid | int | no | 4 | 10 | 0 | no |
| language | int | no | 4 | 10 | 0 | yes |
| status | tinyint | no | 1 | 3 | 0 | yes |
| type | tinyint | no | 1 | 3 | 0 | no |
| usertype | smallint | no | 2 | 5 | 0 | yes |
| printfmt | varchar | no | 255 | yes | ||
| prec | smallint | no | 2 | 5 | 0 | yes |
| scale | int | no | 4 | 10 | 0 | yes |
| iscomputed | int | no | 4 | 10 | 0 | yes |
| isoutparam | int | no | 4 | 10 | 0 | yes |
| isnullable | int | no | 4 | 10 | 0 | yes |
| collation | sysname | no | 256 | yes | ||
| tdscollation | binary | no | 5 | yes |
3. Table systypes
Table ini mempunyai struktur sebagai berikut :
| Column_Name | Type | Computed | Length | Prec | Scale | Nullable |
| name | sysname | no | 256 | no | ||
| xtype | tinyint | no | 1 | 3 | 0 | no |
| status | tinyint | no | 1 | 3 | 0 | yes |
| xusertype | smallint | no | 2 | 5 | 0 | yes |
| length | smallint | no | 2 | 5 | 0 | no |
| xprec | tinyint | no | 1 | 3 | 0 | no |
| xscale | tinyint | no | 1 | 3 | 0 | no |
| tdefault | int | no | 4 | 10 | 0 | no |
| domain | int | no | 4 | 10 | 0 | no |
| uid | smallint | no | 2 | 5 | 0 | yes |
| reserved | smallint | no | 2 | 5 | 0 | yes |
| collationid | int | no | 4 | 10 | 0 | yes |
| usertype | smallint | no | 2 | 5 | 0 | yes |
| variable | bit | no | 1 | no | ||
| allownulls | bit | no | 1 | yes | ||
| type | tinyint | no | 1 | 3 | 0 | no |
| printfmt | varchar | no | 255 | yes | ||
| prec | smallint | no | 2 | 5 | 0 | yes |
| scale | tinyint | no | 1 | 3 | 0 | yes |
| collation | sysname | no | 256 | yes |
Sebagai contoh anda bisa menjalankan query di bawah ini untuk menampilkan seluruh table dan colum setiap table beserta type data kolomnya.
SELECT sysobjects.name as table_name,
syscolumns.name as column_name,
systypes.name as datatype,
syscolumns.length as length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype=’U‘
ORDER BY sysobjects.name,syscolumns.colid
