--
获取M$ SQL Server用户表的字段信息
USE database1
SELECT
表名 = CASE a.colorder WHEN 1 THEN c.name ELSE '' END ,
序 = a.colorder,
字段名 = a.name,
标识 = CASE COLUMNPROPERTY (a.id,a.name, ' IsIdentity ' ) WHEN 1 THEN ' √ ' ELSE '' END ,
主键 = CASE
WHEN EXISTS (
SELECT *
FROM sysobjects
WHERE xtype = ' PK ' AND 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
)
)
)
)
THEN ' √ '
ELSE ''
END ,
类型 = b.name,
字节数 = a.length,
长度 = COLUMNPROPERTY (a.id,a.name, ' Precision ' ),
小数 = CASE ISNULL ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 )
WHEN 0 THEN ''
ELSE CAST ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ) AS VARCHAR )
END ,
允许空 = CASE a.isnullable WHEN 1 THEN ' √ ' ELSE '' END ,
默认值 = ISNULL (d. [ text ] , '' ),
说明 = ISNULL (e. [ value ] , '' )
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = b.xusertype
INNER JOIN sysobjects c ON a.id = c.id AND c.xtype = ' U ' AND c.name <> ' dtproperties '
LEFT JOIN syscomments d ON a.cdefault = d.id
LEFT JOIN sysproperties e ON a.id = e.id AND a.colid = e.smallid
ORDER BY c.name, a.colorder
USE database1
SELECT
表名 = CASE a.colorder WHEN 1 THEN c.name ELSE '' END ,
序 = a.colorder,
字段名 = a.name,
标识 = CASE COLUMNPROPERTY (a.id,a.name, ' IsIdentity ' ) WHEN 1 THEN ' √ ' ELSE '' END ,
主键 = CASE
WHEN EXISTS (
SELECT *
FROM sysobjects
WHERE xtype = ' PK ' AND 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
)
)
)
)
THEN ' √ '
ELSE ''
END ,
类型 = b.name,
字节数 = a.length,
长度 = COLUMNPROPERTY (a.id,a.name, ' Precision ' ),
小数 = CASE ISNULL ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 )
WHEN 0 THEN ''
ELSE CAST ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ) AS VARCHAR )
END ,
允许空 = CASE a.isnullable WHEN 1 THEN ' √ ' ELSE '' END ,
默认值 = ISNULL (d. [ text ] , '' ),
说明 = ISNULL (e. [ value ] , '' )
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = b.xusertype
INNER JOIN sysobjects c ON a.id = c.id AND c.xtype = ' U ' AND c.name <> ' dtproperties '
LEFT JOIN syscomments d ON a.cdefault = d.id
LEFT JOIN sysproperties e ON a.id = e.id AND a.colid = e.smallid
ORDER BY c.name, a.colorder