. NET Common tricks _ get the various properties of a SQL Server table field

--SQL Server 2000

Select A.name as field name, Case when EXISTS (select1From 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') =1Then'1'ELSE'0'END as ID, b.name as type, a.length as takes up bytes, ColumnProperty (a.id, A.name,'PRECISION'As length, a.xscale as Decimal, a.isnullable as Nullable, ISNULL (E.text,"') As default value, ISNULL (G.[value],"'As field description from syscolumns A to OUTER JOIN systypes 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 joins syscomments e on A.cdefault=E.id left OUTER joins 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 =0WHERE (D.name='Table name')

--2,sql SERVER 2005

Select Case When EXISTS (select1From 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'Key', case when ColumnProperty (a.ID, A.name,'isidentity') =1Then'1'ELSE'0'END as'Identity', A.name as ColName, c.name as TypeName, a.length as'byte', ColumnProperty (a.id, A.name,'PRECISION') as'length', A.xscale, A.isnullable, ISNULL (E.text,"') as'default', ISNULL (P.value,"') as'Comment'From Sys.syscolumns as a INNER joins Sys.sysobjects as B on a.ID=b.id INNER JOIN sys.systypes as C on A.xtype=C.xtype left OUTER joins 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'For the data table you want to find.

--2,sql SERVER 2008

SELECT Table name= CaseWhen a.colorder=1Then D.nameElse "'End, table description= CaseWhen a.colorder=1Then IsNull (F.value,"')Else "'End, field ordinal=a.colorder, field name=a.name, Logo= CaseWhen ColumnProperty (A.id,a.name,'isidentity')=1Then'√'Else "'end, primary key= CaseWhen exists (SELECT1From sysobjectswhereXtype='PK'and Parent_obj=a.id and nameinch(SELECT name from sysindexes WHERE indidinch(SELECT indid from Sysindexkeys WHERE id = a.id and colid=a.colid))) Then'√' Else "'End, type=b.name, number of bytes occupied=a.length, Length= ColumnProperty (A.id,a.name,'PRECISION'), number of decimal digits= IsNull (ColumnProperty (A.id,a.name,' Scale'),0), allow null= CaseWhen a.isnullable=1Then'√'Else "'end, default value= IsNull (E.text,"'), field description= IsNull (G.[value],"') from syscolumns aleft join systypes b on A.xusertype=B.xusertypeinner join sysobjects D on a.id=d.id and D.xtype='U'and d.name<>'dtproperties'Left join syscomments e on A.cdefault=E.idleft join Sys.extended_properties g on a.id=G.MAJOR_ID and A.colid=g.minor_id left join sys.extended_properties fon d.id=F.MAJOR_ID and F.minor_id=0whereD.name='Product'--If you query only the specified table, add this condition to order by A.id,a.colorder