Query the structure of a user table: Select
Table Name = Case When A. colorder Is Not Null Then D. Name Else '' End ,
FIELD No. = A. colorder,
Field name = A. Name,
Identifier = Case When Columnproperty (A. ID, A. Name, ' Isidentity ' ) = 1 Then ' √ ' Else '' End ,
Primary Key = Case When Exists ( Select 1 From Sysobjects Where Xtype = ' PK ' And Name In ( Select Name From Sysindexes Where Indid In ( Select Indid From Sysindexkeys Where ID = A. ID And Colid = A. colid ))) Then ' √ ' Else '' End ,
Type = B. Name,
Bytes occupied = A. length,
Length = Columnproperty (A. ID, A. Name, ' Precision ' ),
Decimal places = Isnull ( Columnproperty (A. ID, A. Name, ' Scale ' ), 0 ),
Null allowed = Case When A. isnullable = 1 Then ' √ ' Else '' End ,
Default Value = Isnull (E. Text , '' ),
Field description = Isnull (G. [ Value ] , '' )
From Syscolumns Left Join Policypes 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 Sysproperties g On A. ID = G. ID And A. colid = G. smallid
Where D. Name = ' Table1 ' -- Data Table Name
Order By A. colorder -- Sort
query a user table: select table name = name from sysobjects where xtype = ' U ' and name ' dtproperties '
