The Sql Server Profiler is working please see the below script taken from Sql Server profiler and run on Query window.
select
tabs.name as TABLE_NAME,
cols.name as COLUMN_NAME,
cols.colorder as COLUMN_ORDER,
types.name as DATA_TYPE,
types.name +
case types.name
when 'char' then '(' + cast(cols.prec as varchar) + ') COLLATE ' + coalesce(cols.collation, types.collation)
when 'varchar' then '(' + cast(cols.prec as varchar) + ') COLLATE ' + coalesce(cols.collation, types.collation)
when 'nvarchar' then '(' + cast(cols.prec as varchar) + ') COLLATE ' + coalesce(cols.collation, types.collation)
when 'numeric' then '(' + cast(cols.xprec as varchar) + ', ' + cast(cols.xscale as varchar) + ')'
else ''
end + ' ' +
case cols.isnullable
when 1 then 'NULL '
when 0 then 'NOT NULL '
end
as TYPE_EXTENDED,
case
when keys.keyno is not null then 'True'
else 'False'
end as IS_PK,
keys.keyno as PK_ORDER,
case cols.isnullable
when 1 then 'True'
when 0 then 'False'
end AS IS_NULLABLE,
case types.name
when 'char' then cast(cols.prec as varchar)
when 'varchar' then cast(cols.prec as varchar)
when 'nvarchar' then cast(cols.prec as varchar)
when 'numeric' then cast(cols.xprec as varchar)
else NULL
end AS DATA_PRECISION,
case types.name
when 'numeric' then cast(cols.xscale as varchar)
else NULL
end AS DATA_SCALE,
CASE (cols.status & 128)
WHEN 128 THEN 'True'
ELSE 'False'
END AS IS_IDENTITY,
const_text.text AS DEFAULT_VALUE
from syscolumns cols
inner join sysobjects tabs
on tabs.id = cols.id
inner join systypes types
on types.xtype = cols.xtype
and types.xusertype = cols.xusertype
left outer join sysobjects pks
on pks.parent_obj = tabs.id
and OBJECTPROPERTY(pks.id, 'IsPrimaryKey') = 1
left outer join sysindexes idx
on idx.id = tabs.id
and idx.name = pks.name
left outer join sysindexkeys keys
on keys.id = tabs.id
and keys.colid = cols.colid
and keys.indid = idx.indid
left outer join sysconstraints const
on const.id = tabs.id
and const.colid = cols.colid
left outer join syscomments const_text
on const_text.id = const.constid
where
OBJECTPROPERTY(tabs.id, 'IsUserTable') = 1
AND OBJECTPROPERTY(tabs.id, 'IsMSShipped') = 0
order by tabs.name, cols.colorder
