一、sql语句查询数据库里的所有表名,字段名
1.SQL Server
Select
SysObjects.Name as TableName,
SysColumns.Name as ColumnsName,
SysTypes.Name as DateType,
SysColumns.Length as DateLength,
SysProperties.Value as Remark --列描述
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'
查看所有表名: select name from sysobjects where type='U'
查询表的所有字段名: Select name from syscolumns Where ID=OBJECT_ID('表名')
select * from information_schema.tables select * from information_schema.views select * from information_schema.columns
2.ACCESS
查看所有表名: select name from MSysObjects where type=1 and flags=0 MSysObjects是系统对象,默认情况是隐藏的。通过工具、选项、视图、显示、系统对象可以使之显示出来。
二、Sql 查询时加自动增长列
a:没有主键的情形: Select identity(int,1,1) as iid,* into #tmp from TableName Select * from #tmp Drop table #tmp b:有主键的情形: Select (Select sum(1) from TableName where KeyField <= a.KeyField) as iid,* from TableName a order by iid
|