//列出所有用户表
SELECT [name]
FROM [dbo].[sysobjects]
WHERE [type]='u'
ORDER by [name]
//列出有记录的表
一、
select name,rowcnt
from sysindexes
where ( OBJECTPROPERTY(object_id(name), N'IsUserTable') = 1 ) and (rowcnt>0)
order by name
二、
set nocount on
if object_id(N'tempdb.db.#temp') is not null
drop table #temp
create table #temp (name sysname,count numeric(18))
insert into #temp
select o.name,i.rows
from sysobjects o,sysindexes i
where o.id=i.id and o.Xtype='U' and i.indid<2
select count(count) 总表数,sum(count) 总记录数 from #temp
select * from #temp where count>0
order by name
set nocount off
三、
SELECT a.name, b.rows
FROM sys.sysobjects AS a INNER JOIN sys.sysindexes AS b ON a.id = b.id
WHERE (a.xtype = 'U') AND (b.indid < 2)
ORDER BY b.rows DESC
//列用用户表中的字段名
SELECT [name]
,[id]
,[xtype]
,[length]
,[colid]
FROM [JiaDeXing].[dbo].[syscolumns]
where [id] in(SELECT [id]
FROM [dbo].[sysobjects]
WHERE [type]='u')