select from tables with same prefix

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select from tables with same prefix

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2012-04-11 : 05:51:43

Every night an Table is created with the prefix:

ErrLog_

…Followed by a date.

Is there a way to select * from all tables without using the full table name

select * from ErrLog_date1
select * from ErrLog_date2
select * from ErrLog_date3

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-11 : 06:51:10

There is no way to select data from a table without specifying the full name.

You can try to construct a dynamic SQL query something along these lines. First run this query:

SELECT 'SELECT * FROM '+NAME + ' UNION ALL' 
FROM sys.tables 
WHERE NAME LIKE 'ErrLog%'

Copy the results of this query, remove the last "UNION ALL" and run that as a query.

If at all possible, for future, it might be worthwhile to avoid creating a new table each day. Instead store the data in a single table with DATE column and appropriate keys and indexes. That would make querying and managing much easier.

DLTaylor
Posting Yak Master

136 Posts

Posted - 2012-04-11 : 07:09:58

Thanks sunitabeck

Thats great the script worked!

Is there a way to modify your scrip so as the last column drops the union all.
This would allow me to send the values to a table through an automated\scheduled tasks
(I would then view the results via SSRS)
don’t worry the errors tables rarely contain values ;-) we are talking a few records every so often.

Totally agree that having data in the tables with a date column will be easier. Unfortunately the Tables are created by 3rd party

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-11 : 07:37:33

You could concatenate them and remove one UNION ALL like this:

SELECT STUFF
((
     SELECT ' UNION ALL SELECT * FROM ' + NAME AS [text()]
     FROM sys.tables
     WHERE NAME LIKE 'ErrLog%' FOR XML PATH('')
) ,1,11,'');

DLTaylor
Posting Yak Master

136 Posts

Posted - 2012-04-11 : 09:08:59

Thanks sunitabeck

That does exactly what was asked for ;-)

I’m beginning to see now that a script can be generated, but this can’t be Executed – is this true?
Or are there examples when you can when you can?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-11 : 10:01:15

You can use dynamic SQL to execute it. The code is below. But using dynamic SQL and the general approach, while probably required in this case, should be avoided if at all possible:

DECLARE @sql NVARCHAR(4000);

SELECT @sql = STUFF
((
     SELECT ' UNION ALL SELECT * FROM ' + NAME AS [text()]
     FROM sys.tables
     WHERE NAME LIKE 'ErrLog%' FOR XML PATH('')
) ,1,11,'');

EXEC sp_executesql @sql;

DLTaylor
Posting Yak Master

136 Posts

Posted - 2012-04-11 : 10:37:11

thank you for your advice sunitabeck.
This has all been really useful information
have a good day!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources