Get number of tables in each database in SQL Server

Question

SELECT d.NAME
,ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
,(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4
GROUP BY d.NAME
ORDER BY d.NAME

I have the above T-SQL script which lists all databases on an SQL Server instance along with their corresponding size in MBs & GBs.

What i'm struggling with is also to include a column for the number of tables in each database.

Does any one also know how i can improve the above script to also show the total numbers of tables in each listed database. Optionally, it would be nice to get also the number of rows in each table but this is not a big issue.

I'm targeting sql server 2005 and obove.

You can't do it just by extending the current script - table definitions are stored within each database in their own metadata tables - and you can't write a single SQL query that joins to an (unknown) number of additional tables (one per database) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 14, 2015 at 9:57

Lord of the Goo · Accepted Answer · 2017-02-28 12:28:33Z

2

A wider report about all your databases could include stats over tables, views, procedures, triggers, amenities (xml, spatial indexes) and so far.

if object_ID('TempDB..#AllTables','U') IS NOT NULL drop table #AllTables
CREATE TABLE #AllTables (
  [DB Name] sysname, 
  [Tables] int, 
  [Views] int, 
  [Procedures] int, 
  [Triggers] int, 
  [Full Text Catalogs] int, 
  [Xml Indexes] int, 
  [Spatial Indexes] int)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL,'') + 'USE ' + quotename(name) + '
insert into #AllTables 
select ' + QUOTENAME(name,'''') + ' as [DB Name], 
    (select count(*) from ' + QUOTENAME(Name) + '.sys.tables),
    (select count(*) from ' + QUOTENAME(Name) + '.sys.views),
    (select count(*) from ' + QUOTENAME(Name) + '.sys.procedures),
    (select count(*) from ' + QUOTENAME(Name) + '.sys.triggers),
    (select count(*) from ' + QUOTENAME(Name) + '.sys.fulltext_catalogs),
    (select count(*) from ' + QUOTENAME(Name) + '.sys.xml_indexes),
    (select count(*) from ' + QUOTENAME(Name) + '.sys.spatial_indexes)
 '
FROM sys.databases
ORDER BY name
-- print @SQL -- debug 
EXECUTE(@SQL)
SELECT * FROM #AllTables

The provided solution, being far to be optimal, is easy to master and extend.

NOT OPTIMAL, just for sporadic report

based on (and credits to): http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-get-information-about-all-databas/

edited Feb 28, 2017 at 12:28

answered Feb 28, 2017 at 12:23

Lord of the Goo

1,31717 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kiltannen Over a year ago

This answer worked beautifully for my use case! I also added the Schema detail, and you are right, it was easy peasy to extend this framework...

StackTrace · Accepted Answer · 2015-05-14 10:54:17Z

1

Not as a single script, you could obtain the requested result by

CREATE TABLE AllTables ([DB Name] sysname, [Schema Name] sysname, [Table Name] sysname)


    DECLARE @SQL NVARCHAR(MAX)

    SELECT @SQL = COALESCE(@SQL,'') + '
    insert into AllTables
    select ' + QUOTENAME(name,'''') + ' as [DB Name], [Table_Schema] as 
    [Table Schema], [Table_Name] as [Table Name] from ' +
    QUOTENAME(Name) + '.INFORMATION_SCHEMA.Tables;' FROM sys.databases
    ORDER BY name 
    EXECUTE(@SQL)     
    SELECT * FROM AllTables ORDER BY [DB Name],[SCHEMA NAME], [Table Name]
    DROP TABLE AllTables

Reference:List of All Tables in All Databases

edited May 14, 2015 at 10:54

StackTrace

9,44636 gold badges122 silver badges211 bronze badges

answered May 14, 2015 at 10:11

Simone

1,9441 gold badge14 silver badges22 bronze badges

Collectives™ on Stack Overflow

Get number of tables in each database in SQL Server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related