Sql Server查看表结构

1、表结构

2、通过 t abc 查询出的表结构

3、存储过程 t

CREATE proc t                        
@TableName nvarchar(200)                        
as                        
SELECT             
   (select top 1 isnull(value,'') from sys.extended_properties ex_p where ex_p.minor_id=0              
      and ex_p.major_id in (select id from sys.sysobjects a where a.name=@TableName)) 表注释,            
      sysobjects.name AS 表名, syscolumns.name AS 列名,                         
      systypes.name AS 类型, syscolumns.length AS 长度,                        
      syscolumns.prec as 总长度,syscolumns.scale as 精度,                         
      CONVERT(nvarchar(100),sys.extended_properties.[value]) AS 备注,                        
      case syscolumns.isnullable when 1 then 'null' else 'not null' end as 是否允许为空,        
      外键名称, 主键表名,主键列名,  
      d.IndexName as 索引名, Sort 排序, PrimaryKey 是否主键,[UQIQUE] 是否唯一键                    
FROM  sys.extended_properties RIGHT OUTER JOIN                        
      sysobjects INNER JOIN                        
      syscolumns ON sysobjects.id = syscolumns.id INNER JOIN                        
      systypes ON syscolumns.xtype = systypes.xtype ON                         
      sys.extended_properties.major_id = syscolumns.id AND                         
      sys.extended_properties.minor_id = syscolumns.colid             
   left join  (SELECT a.name 外键名称,         
  外键表名= c.name,         
  外键表ID   = b.fkeyid ,        
  外键表名称 = object_name(b.fkeyid) ,         
  外键列名   = (SELECT name FROM syscolumns WHERE colid = b.fkey AND id = b.fkeyid) ,         
  主键表名   = object_name(b.rkeyid) ,         
  主键列名   = (SELECT name FROM syscolumns WHERE colid = b.rkey AND id = b.rkeyid) ,        
  级联更新   = ObjectProperty(a.id,'CnstIsUpdateCascade') ,        
  级联删除   = ObjectProperty(a.id,'CnstIsDeleteCascade')         
  FROM sysobjects a         
  JOIN sysforeignkeys b ON a.id = b.constid         
  JOIN sysobjects c ON a.parent_obj = c.id          
       ) b on sysobjects.name=外键表名 and  syscolumns.name=外键列名      
   left join   
   (   
 select    
  TableName=O.Name,   
  IndexName=IDX.Name,  
  IndexType=ISNULL(KC.type_desc,'Index'),  
  Index_Column_id=IDXC.index_column_id,  
  ColumnID=C.Column_id,  
  ColumnName=C.Name,  
  Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')  
   WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,  
  PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'1'ELSE N'' END,  
  [UQIQUE]=CASE WHEN IDX.is_unique=1 THEN N'1'ELSE N'' END    
 FROM sys.indexes IDX  
  INNER JOIN sys.index_columns IDXC  
   ON IDX.[object_id]=IDXC.[object_id]  
    AND IDX.index_id=IDXC.index_id  
  LEFT JOIN sys.key_constraints KC  
   ON IDX.[object_id]=KC.[parent_object_id]  
    AND IDX.index_id=KC.unique_index_id  
  INNER JOIN sys.objects O  
   ON O.[object_id]=IDX.[object_id]  
  INNER JOIN sys.columns C  
   ON O.[object_id]=C.[object_id]  
    AND O.type='U'  
    AND O.is_ms_shipped=0  
    AND IDXC.Column_id=C.Column_id   
  where CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')  
   WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END>''  
   ) d on d.TableName=sysobjects.name and d.ColumnName=syscolumns.name                  
WHERE sysobjects.name = @TableName AND (systypes.name <> 'sysname')                        
order by syscolumns.colid