T-SQL Query to find SQL database age and number of tables in SQL Server

The following T-SQL statement helps find the SQL Database age based on the created date and number of tables. You can use this script to identify databases without the user tables for cleanup in SQL Server.

use master;
set nocount on

if object_id(‘tempdb..##table_count’) is not null
drop table ##table_count
create table ##table_count ([database] varchar(255), [tablecount] int)

declare @find_empty_databases varchar(max)
set @find_empty_databases = ”
select @find_empty_databases = @find_empty_databases +
‘use [‘ + [name] + ‘];’ + char(10) +
‘insert into ##table_count select db_name(), count(*) from sysobjects where [xtype] = ”u”’ + char(10)
from sys.databases where [database_id] > 4 and [state_desc] = ‘online’ order by [name] asc
exec (@find_empty_databases)

select
‘database’ = tc.[database]
, ‘created_on’ = convert(char, [create_date], 23)
, ‘days_ago’ = cast(datediff(d, [create_date], getdate()) as varchar) + ‘ (‘ + cast(datediff(d, [create_date], getdate()) / 365 as varchar) + ‘ years)’
, ‘table_count’ = tc.[tablecount]
from
sys.databases sd join ##table_count tc on sd.[name] = tc.[database]
where
tc.[tablecount] <=20
order by
sd.[create_date] asc

drop table ##table_count
In my demo environment, it gives the following result.

T-SQL Query to find SQL database age and number of tables in SQL Server