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


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
  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

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

Switch sites:
loading Hold tight while we fetch your results