Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How To Avoid Msg 106

By Henrik Staun Poulsen,

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.

Total article views: 5954 | Views in the last 30 days: 2
 
Related Articles
FORUM

create function

create function

FORUM

"function"

"function"

FORUM

Select from Variable Table Name under Function.

Fail to select an input table name under a Function !

FORUM

Creating Stored Procedure with SELECT ... inside

how to create a SP with select inside

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones