获取表SQLSERVER 的表结构信息（字段名，长度，精度，类型，NULL,ID,PRI）

select sys.columns.name, sys.types.name, sys.columns.precision,sys.columns.scale, sys.columns.is_nullable,    

•   (select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity ,  
•   (select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as description,  
• ( SELECT count(*) FROM sysobjects    
• WHERE (name in (SELECT name FROM sysindexes    
• WHERE (id = a.id) AND (indid in    
• (SELECT indid FROM sysindexkeys    
• WHERE (id = a.id) AND (colid in    
• (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))    
• AND (xtype = 'PK') ) as is_key   
•   from sys.columns, sys.tables, sys.types ,  
•    syscolumns a   
•   where   
•   sys.columns.object_id = sys.tables.object_id and   
•   sys.columns.system_type_id=sys.types.system_type_id and   
•   a.id = sys.tables.object_id  and a.id = sys.columns.object_id and a.name = sys.columns.name and   
•   sys.tables.name='yk_typk'   
•   order by sys.columns.column_id  

select sys.columns.name, sys.types.name, sys.columns.precision,sys.columns.scale, sys.columns.is_nullable,  
  (select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity ,
  (select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as description,
( SELECT count(*) FROM sysobjects  
WHERE (name in (SELECT name FROM sysindexes  
WHERE (id = a.id) AND (indid in  
(SELECT indid FROM sysindexkeys  
WHERE (id = a.id) AND (colid in  
(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))  
AND (xtype = 'PK') ) as is_key 
  from sys.columns, sys.tables, sys.types ,
   syscolumns a 
  where 
  sys.columns.object_id = sys.tables.object_id and 
  sys.columns.system_type_id=sys.types.system_type_id and 
  a.id = sys.tables.object_id  and a.id = sys.columns.object_id and a.name = sys.columns.name and 
  sys.tables.name='yk_typk' 
  order by sys.columns.column_id

 

 

YPCD    numeric 4   0   0   0   NULL    1
CDMC    varchar 0   0   1   0   NULL    0
PYDM    varchar 0   0   1   0   NULL    0
CDQC    varchar 0   0   1   0   NULL    0