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

33 views0 comments