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