How to avoid Msg 106 Too many table names in the query. The maximum allowable is 256.

Due to a design bug, we have 70,000 tables in one of our databases. Most of these tables contain the same kind of data, but for different "customers". In other words, the tables have identical data structure, but contain different data for each of our clients.

In this thread from the sqlserver.programming newsgroup, I noticed a lot of wise people discuss ways around this design. In particular, “RON” described the design this way:

The tables are preceded by the something similar to the company code of a company. For example the companies Microsoft and Dell have the same tables in the DB - Table1, Table2 & Table3 but for Microsoft, the names of the 3 tables are MSTable1, MSTable2 & MSTable3. Similarly, the names of the 3 tables for Dell are DLTable1, DLTable2 & DLTable3.

Our design problem was of the same nature. We use dynamic SQL for all the queries in order to build the correct table name for the present query. This is not very fast, and it is very difficult to get a total for all “customers”.

When discussing this with others, many have said that we should fix the table design problem. Joe Celko even had a name for it: Attribute Splitting. However we were not allowed to move the data at this time. There would be too much code to change at once. So we have to come up with another solution.

One solution could be to create a view as Erland Sommarskog pointed out: a view that does a UNION ALL on all identical tables. We could do something like this:

Create view vwTable1 as
   SELECT Company = 'MS', col1, col2,  ...
    FROM   MSTable1
    UNION  ALL
    SELECT Company = 'DL', co11, col2, ...
    FROM   DLTable1

But then we ran into this error:

Msg 106, Level 15, State 1, Procedure vwTable1, Line 262

Too many table names in the query. The maximum allowable is 256.

So we have tried several things to get around this problem, and after several attempts, we think we have a solution. A Multi-Statement Table-Valued User Defined Function (UDF) to rescue. It has to be a Multi-Statement, because an ordinary table-valued function does not work. Here is some code to illustrate the problem:

SET NOCOUNT ON
DECLARE @loopcounter INTEGER, @max INTEGER, @sstr NVARCHAR(max)
DECLARE @UDFtype INTEGER
SET @UDFtype=0 -- 0=table-valued function, 1=Multi-Statement table-valued function
-- 0 gives this error:
--Msg 106, Level 15, State 1, Procedure myview, Line 262
--Too many table names in the query. The maximum allowable is 256.
SET @UDFtype=1 -- 0=table-valued function, 1=Multi-Statement table-valued function
SET @max =270 -- more than 256 tables
SET @loopcounter=0
WHILE @loopcounter < @max 
 BEGIN
  SET @sstr=N'
   begin try
     DROP TABLE HenrikStaunPoulsentest' + cast(@loopcounter as varchar)+ '
   end try
   begin catch
   end catch
  CREATE TABLE HenrikStaunPoulsentest'
    + cast(@loopcounter as varchar)+ ' ( myint INTEGER )
  INSERT INTO HenrikStaunPoulsentest'
   + cast(@loopcounter as varchar)+ ' VALUES('
   + cast(@loopcounter as varchar)+ ')
      '
      --PRINT @sstr
  EXEC sp_executesql @sstr
  SET @loopcounter=@loopcounter+1
 END
 IF @UDFtype=1 
   BEGIN -- 1=Multi-Statement table-valued function
    SET @sstr=N'
    CREATE FUNCTION myview( @j int)
      RETURNS @Mytable TABLE
      (
            CustomerCode INTEGER,
            myint INTEGER
      )
      AS BEGIN
        INSERT INTO @Mytable
        SELECT 0 as CustomerCode, * FROM HenrikStaunPoulsentest0
      '
   END
 ELSE 
   BEGIN --0=table-valued
    SET @sstr=N'
    CREATE FUNCTION myview( @j int)
      RETURNS TABLE
      AS return
      (
        SELECT 0 as CustomerCode, * FROM HenrikStaunPoulsentest0
      '
   END
SET @loopcounter=1
WHILE @loopcounter<@max 
 BEGIN
   SET @sstr=@sstr + N'    UNION ALL SELECT ' + cast(@loopcounter as varchar)+ ' as CustomerCode, * FROM HenrikStaunPoulsentest' + cast(@loopcounter as varchar)+ '
   '
   SET @loopcounter=@loopcounter+1
 END
IF @UDFtype = 1 
 BEGIN -- 1=Multi-Statement table-valued function
   SET @sstr=@sstr + N'  RETURN
     END
    '
 END
 ELSE 
  BEGIN --0=table-valued
    SET @sstr=@sstr + N')'
  END
 --PRINT @sstr
 --select @sstr
BEGIN TRY
  EXEC sp_executesql @sstr
  SELECT *FROM dbo.myview(1)
END TRY
BEGIN CATCH
   SELECT
       ERROR_NUMBER() AS ErrorNumber,
       ERROR_SEVERITY() AS ErrorSeverity,
       ERROR_STATE() AS ErrorState,
       ERROR_PROCEDURE() AS ErrorProcedure,
       ERROR_LINE()AS ErrorLine,
       ERROR_MESSAGE() AS ErrorMessage;
   PRINT ERROR_MESSAGE()
END CATCH
-- clean up
SET @loopcounter=0
WHILE @loopcounter < @max 
  BEGIN
   SET @sstr=N'DROP TABLE HenrikStaunPoulsentest' +cast(@loopcounter as varchar)+ '      '
   EXEC sp_executesql @sstr
   SET @loopcounter=@loopcounter+1
  END
 BEGIN TRY
    DROP FUNCTION myview
 END TRY
 BEGIN CATCH
 END CATCH
--******************************************************************************

(The example will clean up after itself).

If you enable the print statements, you can see what it does. First it will set up a bunch of tables to demonstrate the problem. 271 tables to be accurate, but it does not matter, it will clean them up later in the code. Then it will create a multi-statement UDF that starts like this:

 CREATE FUNCTION myview( @j int)
 RETURNS @Mytable TABLE
 (
      CustomerCode INTEGER,
      myint INTEGER
 )
 AS BEGIN
      INSERT INTO @Mytable
      SELECT 0 as CustomerCode, * FROM HenrikStaunPoulsentest0

At this point it will add one line of

 UNION ALL SELECTfields FROM customerTable

for each of tables it has just created. It does this by running a while loop that spans all the tables. You will need to modify this while loop so that it fits your own table design if you choose to use this code. Finishing off, it will close the UDF by adding the RETURN and END statements.

You can use this to query all Customers one at a time or all at once. It runs surprisingly fast, given that it has to query more than 255 tables.

In the thread mentioned above, a quick series of postings took place after I posted by solution. Erland Sommarskog made my day, by writing “Brilliant!”

Mike C# asked about code such as this:

INSERT INTO MSTable1 (Col1, Col2)VALUES (12345, 67890)   

Which means; how can this handle inserts on the old tables? I asked “Would an "INSTEAD OF Trigger" work?”, which Hugo Kornelis answered like this:

 CREATE TRIGGER tr_ins_MSTable1 ON MSTable1 INSTEAD OFINSERT
 AS
      INSERT INTO Table1 (Company, Col1, Col2)
      SELECT 'MS', Col1, Col2
      FROM inserted;

Which means; yes, an INSTEAD OF trigger will work and here it the skeleton design for such a trigger.

David Portas commented that “once you have created the view you may be able to migrate painlessly to a better design by replacing the view with a table and replacing the legacy collection of tables with views”. But this will then convert the problem to the view tab of Management Studio, instead of the Tables tab. Then we will have 70000 views, instead of 70000 tables.

The real solution is to fix the root cause, update the legacy code that expects the bad table design, bit by bit. Now there is a way of doing so without re-writing the entire system between two releases. And yes, we will probably do as David suggests, just being on the safe side.

As you can see, a lot of people have contributed to the solution of the problem. So far I have not mentioned jhofm, Rick Sawtell, and Andrew J. Kelly, but they all helped. This happened in a newsgroup, and I really wanted to tell the world that my idea was tagged “Brilliant!” by no one but Erland Sommarskog.

If you have read this far, I have succeeded.