Instance 1:
Select a. name columnname, c. name as typename, case when a. is_nullable = 0 then 'Not Null 'else' null' end as nullable, .*
From sys. columns a, sys. objects B, sys. types c
Where a. object_id = B. object_id and B. name = 'table name' and a. system_type_id = c. system_type_id order by a. column_id
Example 2:
Select
C. name as [field name], t. name as [field type]
, Convert (bit, c. IsNullable) as [can be empty]
, Convert (bit, case when exists (select 1 from sysobjects where xtype = 'pk' and parent_obj = c. id and name in (
Select name from sysindexes where indid in (
Select indid from sysindexkeys where id = c. id and colid = c. colid) then 1 else 0 end)
As [primary key?]
, Convert (bit, COLUMNPROPERTY (c. id, c. name, 'isidentity ') as [automatic growth]
, C. Length as [byte]
, COLUMNPROPERTY (c. id, c. name, 'precision ') as [length]
, Isnull (COLUMNPROPERTY (c. id, c. name, 'Scale'), 0) as [decimal places]
, ISNULL (CM. text, '') as [default value]
, Isnull (ETP. value, '') AS [field description]
--, ROW_NUMBER () OVER (order by c. name) AS [Row]
From syscolumns c
Inner join policypes t on c. xusertype = t. xusertype
Left join sys. extended_properties ETP on ETP. major_id = c. id and ETP. minor_id = c. colid and ETP. name = 'Ms _ description'
Left join syscomments CM on c. cdefault = CM. id
Where c. id = object_id ('table name ')
