获取表结构

[把 'sysobjects' 替换 成 'tablename' 即可]

SELECT   CASE   IsNull (I.name,  '' ) 
When   ''   Then   ''  
Else   ' * '  
End   as  IsPK, 
Object_Name (A.id)  as  t_name, 
A.name  as  c_name, 
IsNull ( SubString (M. text ,  1 ,  254 ),  '' )  as  pbc_init, 
T.name  as  F_DataType, 
CASE   IsNull ( TYPEPROPERTY (T.name,  ' Scale ' ),  '' ) 
WHEN   ''   Then   Cast (A.prec  as   varchar ) 
ELSE   Cast (A.prec  as   varchar )  +   ' , '   +   Cast (A.scale  as   varchar ) 
END   as  F_Scale, 
A.isnullable  as  F_isNullAble 
FROM  Syscolumns  as  A 
JOIN  Systypes  as  T 
ON  (A.xType  =  T.xUserType  AND  A.Id  =   Object_id ( ' sysobjects ' ) ) 
LEFT   JOIN  ( SysIndexes  as  I 
JOIN  Syscolumns  as  A1 
ON  ( I.id  =  A1.id  and  A1.id  =   object_id ( ' sysobjects ' )  and  (I.status  &   0x800 )  =   0x800   AND  A1.colid  <=  I.keycnt) ) 
ON  ( A.id  =  I.id  AND  A.name  =   index_col ( ' sysobjects ' , I.indid, A1.colid) ) 
LEFT   JOIN  SysComments  as  M 
ON  ( M.id  =  A.cdefault  and   ObjectProperty (A.cdefault,  ' IsConstraint ' )  =   1  ) 
ORDER   BY  A.Colid  ASC