34,589
社区成员
发帖
与我相关
我的任务
分享
select
d.name ,a.tablenum
from master.dbo.sysdatabases d,
master.dbo.ESP_INIT_LOG l ,
(select count(name) tablenum from ysbi.dbo.sysobjects AS a where type ='U' ) AS a
where d.name = l.DATABASE_NAME
--查看当前数据库的所有表
declare @str varchar(max)
set @str=''
select @str=@str+' use '+name+
' select '+quotename(name,'''')+' as databasename,COUNT(name) as [count]
from sysobjects where xtype=''U'' and category=0'
from (select name from sys.databases)a
print @str
exec(@str)
--本机测试结果
databasename count
master 124
databasename count
tempdb 6
databasename count
model 0
databasename count
msdb 17
DECLARE @T TABLE(DBName sysname,TabCount int)
INSERT @T EXEC sp_msforeachdb 'select ''?'',count(*) FROM ?.sys.tables WHERE type=''U'''
SELECT * FROM @T
--查看所有数据库:
select name from sys.databases
--查看当前数据库的所有表的名字
select name from sysobjects where xtype='U' and category=0