How many tables are on an instance of SQL Server

开发者_如何学PythonHow do I find out how many tables are on my instance of SQL Server? I can get it for a single schema using select count(*) from sysobjects where type = 'U' (from how to count number of tables/views/index in my database)

---

You're using the word "schema", but I think you're really asking to count tables across all "databases".

declare @t table (
    DBName sysname,
    NumTables int
)

insert into @t
    exec sp_MSforeachdb N'select ''?'', count(*) 
                              from [?].dbo.sysobjects 
                              where type = ''U'''

select DBName, NumTables
    from @t 
    where DBName not in ('distribution','master','model','msdb','tempdb')
    order by DBName

select SUM(NumTables) as TotalTables
    from @t
    where DBName not in ('distribution','master','model','msdb','tempdb')

---

An option without using the hidden, undocumented sp_MSforeachdb

declare @sql nvarchar(max)

select @sql = coalesce(@sql + ' + ', '') + REPLACE('
 (select count(*)
 from ::DB::.sys.objects
 where is_ms_shipped = 0
   and type_desc = ''USER_TABLE'')', '::DB::', QUOTENAME(name))
from master.sys.databases
where owner_sid != 0x01

select @sql = 'select ' + @sql

exec (@sql)  -- returns a single count of all [user] tables in the instance

>

A note on performance. It is insignificant in the greater scheme of things, but with all things interesting, someone is bound to time it. Here is a comparison of the ms_foreachdb approach passing through a temp table (it internally uses a cursor) against the string-concat method.

-- all the variables that we will use
declare @i int -- loop variable
declare @sql nvarchar(max) -- statement var used for 1st approach
declare @t table (DBName sysname, NumTables int) -- table used for 2nd approach

-- init plan cache and buffers
dbcc freeproccache dbcc dropcleanbuffers

print convert(varchar(30), getdate(), 121)

set @i = 0 while @i < 5 begin
 set @sql = null
 select @sql = coalesce(@sql, '') + REPLACE('
  select @c = @c + count(*)
  from ::DB::.sys.objects
  where is_ms_shipped = 0
    and type_desc = ''USER_TABLE''', '::DB::', QUOTENAME(name))
 from master.sys.databases
 where owner_sid != 0x01
 select @sql = 'set nocount on declare @c int set @c = 0 ' + @sql + ' select @c'
 exec (@sql)

 -- clear plan cache and buffers after each run
 dbcc freeproccache dbcc dropcleanbuffers set @i = @i + 1
end

print convert(varchar(30), getdate(), 121)

set @i = 0 while @i < 5 begin
 insert into @t
  exec sp_MSforeachdb N'select ''?'', count(*) 
          from [?].dbo.sysobjects 
          where type = ''U'''

 select SUM(NumTables) as TotalTables
  from @t
  where DBName not in ('distribution','master','model','msdb','tempdb')

 -- unfortunately this is required
 delete from @t

 -- clear plan cache and buffers after each run
 dbcc freeproccache dbcc dropcleanbuffers set @i = @i + 1
end

print convert(varchar(30), getdate(), 121)

The result obtained for only 5 invocations (loop iterations) of each. YMMV

start                 : 2011-01-21 14:21:45.180
end of string-concat  : 2011-01-21 14:21:57.497 (12.317)
end of sp_msforeachdb : 2011-01-21 14:22:13.937 (16.440)

It has to be noted that the temp table has to be emptied between each iteration of the 2nd approach, so that could contribute to the total time. It should have been insignificant though

---

Here is an answer that does not use undocumented functions and works in SQL Server 2005, 2008 and 2008R2. This answer can be used with minor modifications to run any statement across databases.

---

DECLARE @sql varchar(200), @dbname sysname, @dbid smallint;

CREATE table #alltables
(dbname sysname,
 [number of tables] int);

SELECT top 1 @dbname = name, @dbid = database_id
FROM sys.databases
where database_id > 4;

WHILE (@dbname is not null)
begin

    -- the statement below could contain any valid select statement
    set @sql = 'use ' + @dbname + '; insert into #alltables select ''' + @dbname + ''', count(*) from sys.tables';

    EXEC (@sql)

    set @dbname = null;

    SELECT top 1 @dbname = name, @dbid = database_id
    FROM sys.databases
    where database_id > @dbid;

end;

select * FROM #alltables;
SELECT sum([number of tables]) "Total Number of Tables in all user databases" from #alltables;

drop table #alltables;

---

Select Count(*)
From INFORMATION_SCHEMA.TABLES
Where TABLE_TYPE = 'BASE TABLE'

If what you are seeking is a way to determine how many tables exist across all databases on a given SQL Server instance, then you need to cycle through each database. One way would be:

Declare @Databases Cursor
Declare @DbName as nvarchar(64)
Declare @SQL nvarchar(max)
Declare @BaseSQL nvarchar(max)
Declare @Count int
Declare @TotalCount int

Set @Databases = Cursor Fast_Forward For
    select [name]
    from master..sysdatabases
    where [name] Not In('master','model','msdb','tempdb')

Open @Databases
Fetch Next From @Databases Into @DbName

Set @BaseSQL = 'Select @Count = Count(*) 
                    From DatabaseName.INFORMATION_SCHEMA.TABLES
                    Where TABLE_TYPE = ''BASE TABLE'''

Set @TotalCount = 0
While @@Fetch_Status = 0
Begin
    Set @Count = 0
    Set @SQL = Replace(@BaseSQL, 'DatabaseName', QuoteName(@DbName))

    exec sp_executesql @SQL, N'@Count int OUTPUT', @Count OUTPUT

    Set @TotalCount  = @TotalCount + @Count

    Fetch Next From @Databases Into @DbName
End

Close @Databases
Deallocate @Databases

Select @TotalCount

This solution has the advantage of not using any undocumented features such as sp_MSforeachdb however it is obviously more verbose.