Executing the Contents of a Variable

• Andrew Liles

  Mr or Mrs. 500

  Points: 520

  More actions

  August 21, 2004 at 6:27 pm

  #109041

  Hello all, I hope that you are having a very nice weekend

  Here is the problem that I am having. I want to loop thru all the tables in a DB and do a row count. So have I created a dynamic string that contains the Update statement along with the name of the table that it counted the rows from.

  Tables -- DBTableNames, Classes

  DECLARE @dbName varchar(250)

  SELECT @dbName = Min(TableName)

  FROM DBTableNames

  WHILE @dbName IS NOT NULL

  SET @dbName = 'Classes'

  SET @SQLCmd = 'UPDATE DBTableNames SET DataRowCount = (SELECT COUNT(*) FROM ' + @dbName + ') WHERE TableName = ''' + @dbName + ''''

  -- Loops thru all the columns in the table, these contain the names of all the

  --tables in this database

  SELECT @dbName = Min(TableName) FROM DBTableNames WHERE TableName > @dbName

  END

  SELECT @SQLCmd

  --Results of the Select @SQLCmd

  UPDATE DBTableNames SET DataRowCount = (SELECT COUNT(*) FROM Classes) WHERE TableName = 'Classes'

   

  Now, how do I execute the Update statement? I can easily print all of them back to the screen and perform the update manually, but I need it to update as it is looping.

  Thank you in advance for your help

  Andrew

• Gabor Nyul

  SSCrazy Eights

  Points: 9459

  More actions

  August 22, 2004 at 2:26 pm

  #520100

  What about

  exec(@SQLCmd)

  or

  sp_executesql @SQLCmd

  
  
  Bye
  Gabor

• Arnie Rowland

  Mr or Mrs. 500

  Points: 525

  More actions

  August 22, 2004 at 6:55 pm

  #520112

  or, if you want to make it a scheduled job, create a script file and use osql

• ChrisMoix

  SSCommitted

  Points: 1568

  More actions

  August 23, 2004 at 7:11 am

  #520155

  I recently wrote a script(with a bit of help from an article on this site) to do what you are talking about. Here's what I use:

  set nocount on

  create table #reccount (tbl_name varchar(100), rec_count int)

  exec sp_MSforeachtable 'insert #reccount select ''?'',count(*)    rec_count from ?'

  Insert into Admin..SLSRecordCount_pre select * from #reccount order by tbl_name

  drop table #reccount

   

   

• Corie Curcillo

  SSC-Addicted

  Points: 418

  More actions

  August 23, 2004 at 9:35 am

  #520193

  Sorry that I don’t have time to fully research this right now, but there has to be an easier way of doing this (through querying a combo of system tables or INFORMATION_SCHEMA).  Just make sure that you do your research though, there are some gotchas to look out for when querying record count values stored in system tables.  Perhaps someone else in the forums can add to this.

  Corie Curcillo
  MCT, MCDBA, MCSD