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, ...
SELECT Company = 'DL', co11, col2, ...
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.