I use: c.colstat & 1 = 1

Here's a query I wrote a while back that gets all the column details like you see in design table mode...

SELECT c.name,
type_name(c.xusertype) AS type,
c.length,
c.isnullable,
p.value AS description,
cm.text AS defaultvalue,
c.xprec AS [precision],
c.xscale AS scale,
c.colstat & 1 AS isidentity,
CASE c.colstat & 1
WHEN 1 THEN ident_seed(t.name)
ELSE NULL
END AS identityseed,
CASE c.colstat & 1
WHEN 1 THEN ident_incr(t.name)
ELSE NULL
END AS identityincrement,
CASE c.colstat & 1
WHEN 1 THEN ColumnProperty(t.id, c.name, 'IsIDNotForRepl')
ELSE NULL
END AS identitynotforreplication,
ColumnProperty(t.id, c.name, 'IsRowGuidCol') AS isrowguid,
c.iscomputed,
f.text AS formula,
c.collation

FROM sysobjects t

INNER JOIN syscolumns c
ON c.id = t.id

LEFT JOIN sysproperties p
ON p.id = c.id
AND p.smallid = c.colid
AND p.name = 'MS_Description'

LEFT JOIN sysobjects o
ON o.parent_obj = c.id
AND o.xtype = 'D'
AND o.info = c.colid

LEFT JOIN syscomments cm
ON o.id = cm.id

LEFT JOIN syscomments f
ON c.id = f.id
AND c.colid = f.number

WHERE t.name = @TableName

ORDER BY c.colorder

Take Outs for 12 April 2004

Very nice! Thanks for the script, Jim. And, thank you, Jason, for bringing up the topic.

re: IDENTITY,ROWGUID,???????

this one is "legal", and will not use internal columns:

SELECT name = c.name
FROM sysobjects o, syscolumns c
WHERE COLUMNPROPERTY(o.id, c.name, 'IsIdentity')= 1 AND o.id = c.id

Nice script!!
It was useful.

Thks!

Jim's script worked great. I could able to identify the columns that are 'Identity' easily in the database. - Thanks Jim.

thanx

Very Very HelpFUl and Guiding

Incredible Jim's scripts...
It's very useful for me.