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'
查询数据库中某张表的数据结构
最新推荐文章于 2022-10-24 09:29:45 发布