SQL Server Performance

How to SELECT FROM a variabletablename

Discussion in 'General Developer Questions' started by Karen, Oct 13, 2004.

  1. Karen New Member

    I expect this to a simple answer that I am just not seeing! lol

    I have tried this many different ways, and every time that I attempt a tsql 'select from variabletablename' I get the message 'Must declare the variable '@tablename''

    For example:

    DECLARE @TableName varchar(255)
    DECLARE @sql varchar(255)
    select @tablename = 'accitype'
    select * from @tablename

    DECLARE @TableName varchar(255)
    DECLARE @sql varchar(255)
    select @tablename = 'accitype'
    select @sql= 'Select count(*) from @tablename'
    exec(@sql)

    The last line in both return the error. All other lines run fine. I have tried more specific table names by using

    SELECT distinct table_schema + '.' + table_name
    FROM information_schema.tables,dbo.sysobjects

    in case the error returned did not reflect the problems.


    My original code has no problem until the final select statement:

    DECLARE @TableName varchar(255)

    DECLARE TableCursor CURSOR FOR
    SELECT distinct table_schema + '.' + table_name
    FROM information_schema.tables,dbo.sysobjects
    where table_type = 'base table'
    order by table_schema + '.' + table_name

    OPEN TableCursor

    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT 'table ' + @TableName
    Select count(*) from @TableName
    FETCH NEXT FROM TableCursor INTO @TableName
    END

    CLOSE TableCursor
    DEALLOCATE TableCursor

    This is quite frustrating as I was trying to produce a list for a meeting in 20 minutes. Guess it's not going to be produced!

    What I was working up to producing is a list of tables with their record counts. Perhaps there is an easier way, but this is what I was trying.

    Thanks for all help & input in advance!!
    Karen

    Token SQL Goddess
  2. Adriaan New Member

    Just remember that you need the table name in your query statement, not the variable name.

    So instead of:
    select @sql= 'Select count(*) from @tablename'
    exec (@sql)

    Do this:
    select @sql= 'Select count(*) from ' + @tablename
    exec (@sql)

    You should also do a search on "SQL injection", because this sort of code may leave the door wide open to such attacks.
  3. Karen New Member

    Thanks, Adrian... I knew somewhere there was something simple that I didn't see.... It's going to be one of 'those' days! lol

    Why can't I use this

    Select count(*) from @TableName

    but I must use

    select @sql= 'Select count(*) from ' + @tablename
    exec (@sql)


    Thanks!!

    Token SQL Goddess
  4. Adriaan New Member

    "Select count(*) from @TableName" would work if you had declared a variable called @TableName that is of the variable type TABLE. You can access variables of the TABLE type just like the more common TABLE objects, but only within the procedures and functions in whose scope they exist.

    In your situation only a name of a table is passed on through a variable that is of the VARCHAR variable type.
  5. Karen New Member

    Thanks!!!!

    Karen

    Token SQL Goddess
  6. bambola New Member

    Here is another way to do it

    use pubs
    exec pubs..sp_msforeachtable @command1 = 'select ''??'' table_name, count(*) Number_of_rows from ?'
  7. FrankKalis Moderator

    Well, yes, but actually there are some very good reasons why sp_msfor... procedures are undocumented. They use cursors and dynamic sql internally. Both things you shouldn't use frequently.


    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
  8. FrankKalis Moderator

Share This Page