October 19, 2011 at 2:42 pm
I'm not even sure how to ask this question. I'm an ASP.Net/C# developer who uses Management Studio to noodle around in our SQL Server databases, but I'm still a rookie in SQL. Please bear with me.
We have a database with just over 15 billion rows (really) (telecom messaging records). The rows are scattered across about 2500 tables, each with identical schemas. The table names are dynamic, as new data comes in the front and goes out the back - we keep a rolling 7-days.
I'd like to submit a SQL statement to go against ALL the tables, not just one. In C# I'd get a collection of table names, then FOREACH through the collection and union the results. But I don't know how to do it in SQL.
Here is an example:
SELECT * FROM Table0001 WHERE ProtocolName = 'ISUP'
UNION
SELECT * FROM Table0002 WHERE ProtocolName = 'ISUP'
UNION
...
SELECT * FROM Table2500 WHERE ProtocolName = 'ISUP';
Can anyone help?
Thanks in advance,
Brad
October 19, 2011 at 3:16 pm
SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY object_id) > 1 THEN 'UNION ALL ' ELSE '' END +
'SELECT * FROM ' + NAME + ' WHERE PROTOCOLNAME = ''ISUP''' FROM SYS.TABLES
Run this and copy the results to a new window and run that.
October 19, 2011 at 3:27 pm
SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY object_id) > 1 THEN 'UNION ALL ' ELSE '' END +
'SELECT * FROM ' + '[' + sc.name + '].[' + st.NAME + '] WHERE PROTOCOLNAME = ''ISUP''' FROM SYS.TABLES ST
INNER JOIN SYS.SCHEMAS SC ON ST.schema_id = SC.schema_id
Made a little amendment to Steve's code to add the schema (just in case it needs it) and to put it round square tags in case the table name doesn't follow proper naming conventions.
October 19, 2011 at 3:34 pm
You may also need to filter with a WHERE clause on the table name (WHERE name like '%XYZ%') or whatever filter you might need.
October 20, 2011 at 2:59 am
You can generate a dynamic query with Steve's query and use it with exec sp_executesql in case you do not want to execute manually.
- arjun
https://sqlroadie.com/
October 20, 2011 at 3:39 am
declare @stmt nvarchar(max);
select @stmt = stuff((
SELECT ' UNION ALL SELECT * FROM ' + quotename(sc.name) + '.' + quotename(st.NAME) + ' WHERE PROTOCOLNAME = ''ISUP''' as [text()]
FROM SYS.TABLES ST
INNER JOIN SYS.SCHEMAS SC ON ST.schema_id = SC.schema_id
for xml path(''), type
).value('.','nvarchar(max)'), 1, 11, '');
if @stmt is not null
exec sp_executesql @stmt;
Yet more amendments to vince_sql and Steve's code.
- Instead of putting the square brackets around the text yourself, use the quotename() function to make sure that any name is properly escaped.
- Create a single statement and execute it immediately
October 20, 2011 at 11:35 am
YOU GUYS ARE STUDS!!! All of you!
I used the code from R.P.Rozema's post, which was, of course, an evolution from each of the previous contributors.
Gives me exactly what I need.
I am still amazed that 4 people who will never know me took time to write or edit code for me and will never get anything for their efforts other than my undying gratitude. The mainframe world was never like this. I raise a virtual pint to you all.
Brad
October 20, 2011 at 11:41 am
Perhaps this is why mainframes aren't used as much anymore.. The spirit of cooperation was absent..
CEWII
October 20, 2011 at 11:49 am
Thank you for the update.
We all learn here. We try to give some of it back. It helps us all.
October 20, 2011 at 7:41 pm
brad.woody (10/19/2011)
I'm not even sure how to ask this question. I'm an ASP.Net/C# developer who uses Management Studio to noodle around in our SQL Server databases, but I'm still a rookie in SQL. Please bear with me.We have a database with just over 15 billion rows (really) (telecom messaging records). The rows are scattered across about 2500 tables, each with identical schemas. The table names are dynamic, as new data comes in the front and goes out the back - we keep a rolling 7-days.
I'd like to submit a SQL statement to go against ALL the tables, not just one. In C# I'd get a collection of table names, then FOREACH through the collection and union the results. But I don't know how to do it in SQL.
Here is an example:
SELECT * FROM Table0001 WHERE ProtocolName = 'ISUP'
UNION
SELECT * FROM Table0002 WHERE ProtocolName = 'ISUP'
UNION
...
SELECT * FROM Table2500 WHERE ProtocolName = 'ISUP';
Can anyone help?
Thanks in advance,
Brad
The dynamic solution folks wrote is good. I do have to ask, though... I know these tables hold CDR's (telephony messages) but what's in each table? By that I mean, what is the relationship of one table's data to another? For example, does each table contain only a specific range of dates? Is there a relationship at all or are they just full of random records with no regard to date/time, CustomerID, or???
I'd also like to verify that the 15 billion rows across these 2,500 some-odd tables truly represents only 7 days of data collection.
The reason why I'm asking is because simply having a view, proc, or script with 2,499 UNION ALL's across 15 billion rows in it might be only a part of what the proverbial doctor ordered and will actually cause a bit of a performance problem if allowed to exist by itself. If we can figure out something unique about these tables, such as each table containing a date range not included in the other tables, then we might be able to take advantage of what is known as a "Partitioned View" which requires a constraint on each "member" table that provides a uniqueness between tables. Using date/times would be optimal because the latest dates are usually the ones that are "hit" on the most often in many cases. Of course, there are other columns that may be more appropriate but only you would know that for sure.
If you have the Enterprise Edition of SQL Server, there might be an even better way... Table Partitioning where each of the 7 days lives in it's own partition which makes dropping and loading a whole day's worth of data a fairly simple matter.
--Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply