SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 select from tables with same prefix		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DLTaylor
Posting Yak Master

United Kingdom
127 Posts
Posted - 04/11/2012 :  05:51:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 04/11/2012 :  06:51:10  Show Profile  Reply with Quote
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.
Go to Top of Page

DLTaylor
Posting Yak Master

United Kingdom
127 Posts
Posted - 04/11/2012 :  07:09:58  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 04/11/2012 :  07:37:33  Show Profile  Reply with Quote
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,'');
Go to Top of Page

DLTaylor
Posting Yak Master

United Kingdom
127 Posts
Posted - 04/11/2012 :  09:08:59  Show Profile  Reply with Quote
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?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 04/11/2012 :  10:01:15  Show Profile  Reply with Quote
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;
Go to Top of Page

DLTaylor
Posting Yak Master

United Kingdom
127 Posts
Posted - 04/11/2012 :  10:37:11  Show Profile  Reply with Quote
thank you for your advice sunitabeck.
This has all been really useful information
have a good day!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000