升级到sqlserver2005,好多系统表都改了,有个语句,怎么也改不好,熟悉的朋友给看看:
sqlserver200的SQL语句:
SELECT a.name AS 字段名, b.name AS 类型,
COLUMNPROPERTY(a.id, a.name,'PRECISION') AS 长度,
a.isnullable AS 允许空, ISNULL(e.text,'') AS 默认值,
标识 = COLUMNPROPERTY(a.id, a.name, 'IsIdentity'),
主键 = 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
FROM syscolumns a LEFT OUTER 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 OUTER JOIN syscomments e ON a.cdefault = e.id LEFT OUTER JOIN sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT OUTER JOIN sysproperties f ON d .id = f.id AND f.smallid = 0WHERE (d .name = '基础数据表') ORDER BY a.id, a.colorder转到SQL2005下,语句如何写?
sqlserver200的SQL语句:
SELECT a.name AS 字段名, b.name AS 类型,
COLUMNPROPERTY(a.id, a.name,'PRECISION') AS 长度,
a.isnullable AS 允许空, ISNULL(e.text,'') AS 默认值,
标识 = COLUMNPROPERTY(a.id, a.name, 'IsIdentity'),
主键 = 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
FROM syscolumns a LEFT OUTER 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 OUTER JOIN syscomments e ON a.cdefault = e.id LEFT OUTER JOIN sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT OUTER JOIN sysproperties f ON d .id = f.id AND f.smallid = 0WHERE (d .name = '基础数据表') ORDER BY a.id, a.colorder转到SQL2005下,语句如何写?
b.name AS 类型 ,
COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度 ,
a.isnullable AS 允许空 ,
ISNULL(e.text, '') AS 默认值 ,
标识 = COLUMNPROPERTY(a.id, a.name, 'IsIdentity') ,
主键 = 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
FROM syscolumns a
LEFT OUTER 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 OUTER JOIN syscomments e ON a.cdefault = e.id
LEFT OUTER JOIN sys.extended_properties g ON a.id = g.major_id
AND a.colid = g.minor_id
LEFT OUTER 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
(case when a.colorder=1 then d.name else '' end) as 表名,--如果表名相同就返回空
a.colorder as 字段序号,
a.name as 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) as 标识,
(case when (SELECT count(*) FROM sysobjects--查询主键
WHERE (name in
(SELECT name FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid FROM syscolumns
WHERE (id = a.id) AND (name = a.name))
)))))
AND (xtype = 'PK'))>0 then '√' else '' end) as 主键,--查询主键END
b.name as 类型,
a.length as 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
(case when a.isnullable=1 then '√'else '' end) as 允许空,
isnull(e.text,'') as 默认值,
isnull(g.[value],'') AS 字段说明
FROM syscolumns a left join systypes b
on a.xtype=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
where d.name='PE_U_ValliMessage'--所要查询的表
order by a.id,a.colorder
也谢谢
阿汤哥参与!给分了,结贴!