Get Data fields (field and type information for a table)
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 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 a LEFT join systypes B on A.xtype=b.xusertype INNER join sysobjects D on a.id=d.id and D.xtype='U'and d.name<>'dtproperties'Left join syscomments e on A.cdefault=E.id left Join Sys.extended_properties G on a.id=G.MAJOR_ID and A.colid=g.minor_id left join Sys.extended_properties F on d.id=f.major_id and f.minor_id =0--whereD.name='the table to query'--If you query only the specified table, add this condition to order by A.id,a.colorder
SQL Server Get Data fields (table field and type information)