Stats about all SEDE TABLES including response time and last update date

1

Please login or register to vote for this query.

(click on this box to dismiss)

stats about all SEDE tables including response time

Woodworking Meta

Q&A for professional and amateur woodworkers

set nocount on
--create temp table for final output
create table #data ( [TableName] varchar(50), [Fields] int, [Records] bigint, CreateDate datetime, [RunTimeMS] int )  

--create temp table of table names and [create_date] (last SEDE data update date for this table)
select name as [TableName], create_date, row_number() over(order by name) num
into #tables from sys.tables 

--create temp table of field counts per SEDE table
select table_name as [TableName], count(column_name) as [FieldCount] 
into #fieldCount from information_schema.columns group by table_name

declare @gd datetime, @runTime int, @x int = 1, @numTables int  = (select count(*) as numTables from #tables)
declare @sql nvarchar(200), @TableName nvarchar(200), @FieldCount int, @createdate datetime



while @x <= @numTables
begin
  set @TableName = (select TableName from #tables where num=@x)
  set @createdate = (select create_date from #tables where num=@x)
  set @FieldCount = (select FieldCount from #fieldCount where TableName=@TableName)
  set @sql = 'insert into #data select '''+@TableName+''' as [TableName], '+cast(@FieldCount as varchar)
          +' as [Fields], count(*) as [Records], '''+convert(varchar,@createdate,20)+''' as CreateDate, 0 as [RunTimeMS] from '+@TableName
  set @gd = getdate()

  exec sp_executesql @sql
  set @runTime = datediff(ms,convert(varchar, @gd, 21),convert(varchar, getdate(), 21))
  print @TableName+' runtime: '+cast(@runTime as varchar)+'ms'
  update #data set [RunTimeMS] = @runTime where [TableName]=@TableName
  set @x=@x+1
end

select * from #data order by Records desc

--for GRAPH uncomment the following line
--select row_number() over(order by tablename) Num, TableName, RunTimeMS from #data order by tablename

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results