Obtain various attributes of SQL Server table fields

-- SQL Server 200

Select a. Name as field name, case when exists
(Select 1
From sysobjects
Where xtype = 'pk' and parent_obj = A. ID and name in
(Select name
From sysindexes
Where indid in
(Select indid
From sysindexkeys
Where id = A. ID and colid = A. colid )))
Then '1' else '0' end as primary key, case when columnproperty (A. ID, A. Name,
'Isidentity ') = 1 then'1' else '0' end as ID, B. Name as type,
A. length as occupies the number of bytes, columnproperty (A. ID, A. Name, 'precision ') as length,
A. XScale as decimal, A. isnullable as can be empty, isnull (E. Text, '') as default value, isnull (G. [value],
'') As field description
From syscolumns a left Outer Join
Policypes B on A. xusertype = B. xusertype inner join
Sysobjects D on A. ID = D. id and D. xtype = 'U' and
D. Name <> 'dtproperties' left Outer Join
Syscomments e on A. cdefault = E. Id left Outer Join
Sysproperties g on A. ID = G. ID and A. colid = G. smallid left Outer Join
Sysproperties F on D. id = f. ID and F. smallid = 0
Where (D. Name = 'table name ')

 

-- 2. SQL Server 2005

Select case when exists
(Select 1
From sysobjects
Where xtype = 'pk' and parent_obj = A. ID and name in
(Select name
From sysindexes
Where indid in
(Select indid
From sysindexkeys
Where id =. ID and colid =. colid) Then '1' else '0' end as 'key', case when columnproperty (. ID,. name,
'Isidentified') = 1 then'1' else '0' end as 'identifier',. name as colname, C. name as typename,. length as 'byte', columnproperty (. ID,. name,
'Precision ') as 'length',. XScale,. isnullable, isnull (E. text, '') as 'default', isnull (P. value, '') as 'comment'
From SYS. syscolumns as a inner join
SYS. sysobjects as B on A. ID = B. ID inner join
SYS. policypes as C on A. xtype = C. xtype left Outer Join
SYS. syscomments as E on A. cdefault = E. Id left Outer Join
SYS. extended_properties as P on A. ID = P. major_id and A. colid = P. minor_id
Where (B. Name = 'keyfactory ') and (C. Status <> '1 ')
-- B. Name = 'keyfactory ', 'keyfactory' the data table you want to query.

 

-- 2. SQL Server 2005

Select case when exists
(Select 1
From sysobjects
Where xtype = 'pk' and parent_obj = A. ID and name in
(Select name
From sysindexes
Where indid in
(Select indid
From sysindexkeys
Where id =. ID and colid =. colid) Then '1' else '0' end as 'key', case when columnproperty (. ID,. name,
'Isidentified') = 1 then'1' else '0' end as 'identifier',. name as colname, C. name as typename,. length as 'byte', columnproperty (. ID,. name,
'Precision ') as 'length',. XScale,. isnullable, isnull (E. text, '') as 'default', isnull (P. value, '') as 'comment'
From SYS. syscolumns as a inner join
SYS. sysobjects as B on A. ID = B. ID inner join
SYS. policypes as C on A. xtype = C. xtype left Outer Join
SYS. syscomments as E on A. cdefault = E. Id left Outer Join
SYS. extended_properties as P on A. ID = P. major_id and A. colid = P. minor_id
Where (B. Name = 'keyfactory ') and (C. Status <> '1 ')
-- B. Name = 'keyfactory ', 'keyfactory' the data table you want to query.