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, [RunTimeMS] int )  --CreateDate datetime, 
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 --, @create_date datetime
declare @gd datetime

select table_name as [TableName], count(column_name) as [FieldCount]
into #fieldCount from information_schema.columns 
group by table_name
while @x <= @numTables
  set @TableName = (select TableName from #tables where num=@x)
  --set @create_date = (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], 0 as [RunTimeMS] from '+@TableName
  set @gd = getdate()
  print @sql
  exec sp_executesql @sql
  print 'runtime: '+cast(datediff(ms,CONVERT(VARCHAR, @gd, 21),CONVERT(VARCHAR, getdate(), 21)) as varchar)+'ms'
  set @x=@x+1

--update #data select 99 as fields 

select * from #data

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results