insert a count into a table

• craig 84462

  Say Hey Kid

  Points: 669

  More actions

  March 17, 2010 at 5:00 pm

  #230972

  the following code is used to do a criterion based on if the statisticvalue column is <=100, I want the program to insert a cateogry level count in that place, This following program tends to just 'hang' and do nothing, any tips:

  *******************************8

  use Craig

  go

  Declare@DatabaseSysname

  Declare@SchemaNameSysname

  Declare@TableNameSysname

  Declare@ColumnNameSysname

  Declare@strExecnvarChar(Max)

  Declare@StatisticvalueBigInt

  -- Loop over all databases/tables/columns that are available.

  DeclarecsrDatabaseTableColumnstat Cursor For

  SELECT TOP 5 -- debug

  DatabaseName

  ,SchemaName

  ,TableName

  ,ColumnName

  ,Statisticvalue

  FROMdemo

  OpencsrDatabaseTableColumnstat

  FetchNext

  FromcsrDatabaseTableColumnstat

  Into @Database

  ,@SchemaName

  ,@TableName

  ,@ColumnName

  ,@Statisticvalue

  While@@FETCH_STATUS=0

  if @Statisticvalue <=100

  Begin

  Set@strExec=N'Insert'+' '+'demo2'+' '+'(Databasename,SchemaName,Tablename,

  Columnname,Statistic,Statisticsubpart,Statisticvalue,Snapshotdate)'

  Set@strExec=@strExec+'Select'+' '+''''+@Database+''''+','+

  +''''+@SchemaName+''''+','

  +''''+@TableName+''''+','

  +''''+@ColumnName+''''+','

  +'''CountDistinct'''+','

  +'Convert(varChar(100), '+@ColumnName+')'+','

  +'Count(0)'+','

  +'Getdate()'

  Set@strExec=@strExec+' '+'From'+' '+@Database+'.'+@SchemaName+'.'+@TableName

  +'group by' +' '+ 'Convert(varChar(100), '+@ColumnName+')' +'Order by 2 Desc'

  exec(@strExec)

  FetchNext

  FromcsrDatabaseTableColumnstat

  Into @Database

  ,@SchemaName

  ,@TableName

  ,@ColumnName

  ,@Statisticvalue

  End

  ClosecsrDatabaseTableColumnstat

  DeallocatecsrDatabaseTableColumnstat