查询某个库里所有表的定义,包括字段名,值类型,长度,是否为空,是否为主键等
SELECT
SysObjects.Name
as
tb_name, SysColumns.Name
as
col_name
, SysTypes.Name
as
col_type, SysColumns.Length
as
col_len,
isnull
(SysProperties.Value,SysColumns.Name)
as
col_memo,
case when SysColumns.name in
( select ' 主键 ' = a.name
FROM syscolumns a inner join sysobjects b on a.id = b.id and b.xtype = ' U ' and b.name <> ' dtproperties '
where exists
( SELECT 1 FROM sysobjects where xtype = ' PK ' and name in
( SELECT name FROM sysindexes WHERE indid in
( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid
)
)
)
and b.name = SysObjects.Name)
then 1 else 0 end as is_key
FROM SysObjects,SysTypes,SysColumns
LEFT JOIN SysProperties ON (Syscolumns.Id = Sysproperties.Id AND
Syscolumns.Colid = Sysproperties.Smallid)
WHERE (Sysobjects.Xtype = ' u ' OR Sysobjects.Xtype = ' v ' )
AND Sysobjects.Id = Syscolumns.Id AND SysTypes.XType = Syscolumns.XType
AND SysTypes.Name <> ' sysname ' AND Sysobjects.Name Like ' % ' ORDER By SysObjects.Name, SysColumns.colid
case when SysColumns.name in
( select ' 主键 ' = a.name
FROM syscolumns a inner join sysobjects b on a.id = b.id and b.xtype = ' U ' and b.name <> ' dtproperties '
where exists
( SELECT 1 FROM sysobjects where xtype = ' PK ' and name in
( SELECT name FROM sysindexes WHERE indid in
( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid
)
)
)
and b.name = SysObjects.Name)
then 1 else 0 end as is_key
FROM SysObjects,SysTypes,SysColumns
LEFT JOIN SysProperties ON (Syscolumns.Id = Sysproperties.Id AND
Syscolumns.Colid = Sysproperties.Smallid)
WHERE (Sysobjects.Xtype = ' u ' OR Sysobjects.Xtype = ' v ' )
AND Sysobjects.Id = Syscolumns.Id AND SysTypes.XType = Syscolumns.XType
AND SysTypes.Name <> ' sysname ' AND Sysobjects.Name Like ' % ' ORDER By SysObjects.Name, SysColumns.colid
将最后一个“%”号改为某个数据表名,即是查看该表的定义