...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Woodworking Meta

Q&A for professional and amateur woodworkers

set nocount on

create table #data ( [TableName] varchar(50), [Fields] int, [Records] bigint, CreateDate datetime, [RunTimeMS] int )  

select 
  name as [TableName], 
  create_date, 
  row_number() over(order by name) num
into #tables from sys.tables 

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

select table_name as [TableName], count(column_name) as [FieldCount]
into #fieldCount from information_schema.columns 
group by table_name

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()
  --print @sql
  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 RunTimeMS, TableName, RunTimeMS from #data
order by Records desc

Enter Parameters

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