Query to Display Table Structure in SQL Server.

Barangkali ada yang lagi nyari cara bagaimana menampilkan table structure dari SQL server. berikut saya share query nya:

select
    'Table_name'            = ao.name,
    'Column_name'           = ac.name,
    'Type'                  = type_name(user_type_id),
    'Computed'              = case when ColumnProperty(ac.object_id, ac.name, 'IsComputed') = 0 then 'no' else 'yes' end,
    'Length'                    = convert(int, max_length),
    -- for prec/scale, only show for those types that have valid precision/scale
    -- Search for type name + ',', because 'datetime' is actually a substring of 'datetime2' and 'datetimeoffset'
    'Prec'                  = case when charindex(type_name(system_type_id) + ',',  N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,') > 0
                                then convert(char(5),ColumnProperty(ac.object_id, ac.name, 'precision'))
                                else '     ' end,
    'Scale'                 = case when charindex(type_name(system_type_id) + ',',  N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,') > 0
                                then convert(char(5),OdbcScale(system_type_id,scale))
                                else '     ' end,
    'Nullable'              = case when is_nullable = 0 then 'no' else 'yes' end,
    'TrimTrailingBlanks'    = case ColumnProperty(ac.object_id, ac.name, 'UsesAnsiTrim')
                                when 1 then 'no'
                                when 0 then 'yes'
                                else '(n/a)' end,
    'FixedLenNullInSource'  = case
                when type_name(system_type_id) not in ('varbinary','varchar','binary','char')
                    then '(n/a)'
                when is_nullable = 0 then 'no' else 'yes' end,
    'Collation'     = collation_name
from sys.all_columns ac
    INNER JOIN sys.all_objects ao
        ON ac.object_id = ao.object_id and ao.type = N'U'
where ao.name like 'nama_table' --comment baris ini jika akan melihat semua table di dalam database
ORDER BY ao.object_id, ac.column_id

Note:

Silahkan input nama table yang akan di display pada klause where, jika ingin menampilkan semua silahkan di beri comment saja pada klausa where.

Semoga bermanfaat…