SELECT ROW_NUMBER() OVER (ORDER BY id) AS ROW_NUMBER, FieldName, fieldDescn AS cnName, FieldType, fieldLength, isIdentifier, iskeyField, allowNull, defaultValue, | ||||||
fieldDescn | ||||||
FROM (SELECT TOP 100 a.id, d .name N'TableName', a.colorder N'FieldNumber', a.name N'FieldName', (CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') | ||||||
= 1 THEN '○' ELSE ' ' END) N'IsIdentifier', (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) N'IsKeyField', | ||||||
b.name N'FieldType', a.length N'FieldSize', COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS N'FieldLength', isnull(COLUMNPROPERTY(a.id, a.name, | ||||||
'Scale'), 0) AS N'DecimalDigits', (CASE WHEN a.isnullable = 1 THEN ' ' ELSE '○' END) N'AllowNull', isnull(e.text, '') N'DefaultValue', isnull(g.[value], '') | ||||||
AS N'FieldDescn' | ||||||
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 | ||||||
ORDER BY object_name(a.id), a.colorder) ttt |
转载于:https://www.cnblogs.com/jishu/archive/2009/12/04/1940071.html