Sql Server查看表结构

article/2025/10/2 19:09:22

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