| 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 |
