在使用sql server进行表管理时，可以通过sql语句来获取 表相关的结构
主要用到的是
sys.sysobjects数据库对象表。包括 表名对象 、表的约束等，都存在这张表上
sys.syscolumns 数据库所有物理表的列存储所有表的列
sys.syscomments 默认值信息表主要存储了表的列的默认值
sys.extended_properties扩展属性列的相关扩展属性，像 列字段的说明等
select TB.[Name] As TableID, TC.[name] As FieldName, convert(int,TC.[colorder]) As SortIndex, (Case When TK.TABLE_NAME Is not Null Then 'Y' Else 'N' End) As IsKey, (Case When TK.TABLE_NAME Is not Null Then (Case When COLUMNPROPERTY( TC.id,TC.name,'IsIdentity')=1 Then 'Identity' Else 'Customer' END) Else null End) As KeyRule, T0.name FieldType,(Case When TC.length > 0 and T0.name in ('NVhar','NVarChar') Then TC.length / 2 When T0.name='ntext' Then '2147483646'else TC.length end) Length, (Case TC.[isnullable] When 0 Then 'Y' Else 'N' End) As NotNull, SUBSTRING(TM.text,4 , len(TM.text)-5) As DefaultValue,TE.value AS FieldDescfrom sys.sysobjects TB --ob 所以取B 表示对象 left join sys.syscolumns TC on TB.id = TC.id --Column 列 就取 TC替代left join sys.systypes T0 on TC.xtype = T0.XUserTypeleft join INFORMATION_SCHEMA.KEY_COLUMN_USAGE TK on TB.[name]= TK.TABLE_NAME and TK.COLUMN_NAME = TC.[name]left join sys.syscomments TM on TC.cdefault=TM.id left join sys.extended_properties TE on TC.id=TE.major_id and TC.colid=TE.minor_idwhere TB.[Name] = '表名'order by TC.id,TC.colorder
想要更改表结构信息的 可以参照 这篇文章数据库 SQL Server 修改 表结构 脚本 sql语句
附：
网上扒来的 包含是否为索引 的 sql查询
SELECT a.colorder as colOrder, d.name as tableName,a.name as FieldName, convert(int,case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else 0 end) as isIndex, convert(int,case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end) as isPk, b.name as sqlTypeName, a.length as 'length', convert(int,COLUMNPROPERTY(a.id,a.name,'PRECISION')) as 'size', convert(int,isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)) as 'scale', convert(int,case when a.isnullable=1 then 1 else 0 end) as 'Nullable', convert(varchar(100), isnull(e.text,'')) as defaultVal, convert(varchar(255), isnull(g.[value],'')) as remarks FROM syscolumns a left join systypes b on a.xusertype=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 sys.extended_properties g on a.id=G.major_id and a.colid=g.minor_id left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 where d.name='表名'order by a.id,a.colorder
