Access a table with a "dynamic name"

• R. van Laake

  SSCommitted

  Points: 1533

  More actions

  July 29, 2008 at 8:24 am

  #191650

  Hi,

  This query works fine:

  SET @cnt = ( SELECT COUNT(*) FROM myTable )

  How do I do this if I don't know the tablename during designtime?

  For example, I have three tables named myTable1, myTable2 and myTable3

  I would like this:

  SET @nr = '2'

  SET @cnt = ( SELECT COUNT(*) FROM 'myTable'+@nr )

  Obviously this doesn't work.... what should I do?

  EXEC('SELECT COUNT(*) FROM myTable'+@nr) should work, but how do I get the return value?

  Thanks,

  Ray

• Chuck Rivel

  SSCrazy

  Points: 2991

  More actions

  July 29, 2008 at 8:48 am

  #849852

  here is a quick example that can get you started:

  declare @sql varchar(150), @rec_count int

  --hold the temp rec count for the dynamic query into a temp table of the procedure

  create table #tmpcount(rec_count int)

  --setup the dynamic sql and execute

  set @sql = 'insert into #tmpcount (rec_count) select count(*) rec_count from mytable' --place your number concat here

  exec (@sql)

  --return the temp table value into a variable

  set @rec_count = (select rec_count from #tmpcount)

  --just printing so you can see the result

  print @rec_count

  --clean up the temp table

  drop table #tmpcount

  hth

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  July 29, 2008 at 8:52 am

  #849856

  Executing your final statement will return the value. Now if you are talking about running this in a loop you need a table variable to store the results and the do:

  Insert Into @table

  Exec(Sql statement)

  I would also insert the table name that I am getting the rowcount from. If you really just want the row counts without a filter you could run:

  [font="Courier New"]SELECT

      T.name AS table_name

      P.rows

  FROM

      sys.partitions P JOIN

      sys.tables T ON

          P.OBJECT_ID = T.OBJECT_ID JOIN

      sys.indexes I ON

          P.index_id = I.index_id AND

          T.OBJECT_ID = I.OBJECT_ID

  WHERE

      I.index_Id <= 1 AND-- clustered index or heap

      T.name IN (

  )

  [/font]

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• Grant Fritchey

  SSC Guru

  Points: 398999

  More actions

  July 29, 2008 at 8:55 am

  #849861

  If you must do this, parameterize the query and use sp_executesql. You can then capture output parameters. Something like this:

  DECLARE @sql NVARCHAR(MAX)

  DECLARE @count INT

  DECLARE @tablename NVARCHAR(MAX)

  SET @tablename = N'TestTable'

  SET @sql = N'SELECT @count = COUNT(*) FROM ' + @tablename

  EXEC sp_executesql @sql, @params = N'@count int OUTPUT',@count = @count OUTPUT

  SELECT @count

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning