-------------------------------------------------------------------------------------------------------
-----code:start
-------------------------------------------------------------------------------------------------------
DECLARE USER_TABLES CURSOR FOR
(
select
cast(a.table_name as varchar(35))
from information_schema.columns a join sysobjects b
on a.table_name=b.name
where xtype='U' and type='U'
group by a.table_name
)
OPEN USER_TABLES
FETCH NEXT FROM USER_TABLES INTO @TabName
select 'Table Name : ' + @TabName
select
cast(ordinal_position as varchar(3)) as Field
,cast(column_name as varchar(25)) as Field_Name
,cast(data_type as varchar(15)) as Type
,cast(character_maximum_length as varchar(5)) as Width
from information_schema.columns join sysobjects
on sysobjects.name=information_schema.columns.table_name
where
xtype='U'
and type='U'
and table_name=@TabName
order by ordinal_position
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM USER_TABLES INTO @TabName
select 'Table Name : ' + @TabName
select
cast(ordinal_position as varchar(3)) as Field
,cast(column_name as varchar(25)) as Field_Name
,cast(data_type as varchar(15)) as Type
,cast(character_maximum_length as varchar(5)) as Width
from information_schema.columns join sysobjects
on sysobjects.name=information_schema.columns.table_name
where
xtype='U'
and type='U'
and table_name=@TabName
order by ordinal_position
END
CLOSE USER_TABLES
DEALLOCATE USER_TABLES
-------------------------------------------------------------------------------------------------------
-----code:end
-------------------------------------------------------------------------------------------------------
Example Result Messages:
-----------------------------------------------------
Table Name : _Temp_Request_Cuti_All
(1 row(s) affected)
Field Field_Name Type Width
----- ------------------------- --------------- -----
1 Nama nvarchar 255
2 SaldoCuti varchar 50
3 ID int NULL
4 NIK char 8
5 mulai_cuti datetime NULL
6 selesai_cuti datetime NULL
7 jumlah_hari int NULL
(16 row(s) affected)