求数据库中所有带数据的表

//列出所有用户表

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