查询数据库中某张表的数据结构

SELECT c.column_id  as Indexs,TableName = OBJECT_NAME(c.object_id), ColumnsName = c.name, 
Description = ex.value, ColumnType=t.name, Length=c.max_length , c.precision as MaxLength , c.Scale as FloatLength ,sc.IsNullable , 
(case when COLUMNPROPERTY( sc.id,sc.name,'IsIdentity')=1 then 1 else 0 end) N'IsIdentity',
(case when (SELECT count(*)  FROM sysobjects WHERE (name in
    (SELECT name FROM sysindexes WHERE (id = sc.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = sc.id) AND (
      colid in (SELECT colid  FROM syscolumns  WHERE (id = sc.id) AND (name = sc.name))))))) AND
        (xtype = 'PK'))>0 then 1 else 0 end) N'PK'
FROM sys.columns c LEFT OUTER JOIN sys.extended_properties ex 
ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description' 
left outer join systypes t on c.system_type_id=t.xtype 
LEFT OUTER JOIN  syscolumns as sc on c.object_id=sc.id and c.name = sc.name
WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 AND OBJECT_NAME(c.object_id) ='Plan_ProgressTask_Manual' 
order by c.column_id asc


select * FROM 
sys.columns c  where OBJECT_NAME(c.object_id) ='Plan_ProgressTask_Manual'

--原脚本

SELECT 
TableName = OBJECT_NAME(c.object_id), 
ColumnsName = c.name, 
Description = ex.value, 
ColumnType=t.name, 
Length=c.max_length 
FROM 
sys.columns c 
LEFT OUTER JOIN 
sys.extended_properties ex 
ON 
ex.major_id = c.object_id 
AND ex.minor_id = c.column_id 
AND ex.name = 'MS_Description' 
left outer join 
systypes t 
on c.system_type_id=t.xtype 
WHERE 
OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 
AND OBJECT_NAME(c.object_id) ='P_ModelTreeData'