select * from wild tables - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

select * from wild tables

Author

Topic

richard102
Starting Member

25 Posts

Posted - 2013-08-29 : 08:45:16

Hi all, I want to create a View to show the contents of a bunch of tables that have names like 'tb_20130813'. That is - the names include the date, and new tables are created by the application every day. (yup I know)

I tried ...

select * from (select name from sys.tables where name like 'tb%')

Then I tried a union, then a cross-join.

I know I could use a temp-table to store the names and loop through them but ... there must be a simpler way!

thanks

richard102

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-29 : 09:23:38

You will have to use dynamic SQL - something like this:

DECLARE @sql NVARCHAR(MAX);
SELECT @sql = c FROM (
SELECT 'select * from '+name+'; ' FROM sys.tables  WHERE name LIKE 'tb%' FOR XML PATH('')) t(c);
EXEC(@sql);

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-29 : 12:23:20

You could set up a job to re-create a view every day, but that's that one of the disadvantages of letting an applicaiton create tables (as you aleady know). But, you can't create a view with dynamic sql in it.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-29 : 13:14:15

If these tables all have the same schema and you want a method to dynamically union them into a view:


IF OBJECT_ID('dbo.vwTableUNION') IS NOT NULL DROP VIEW vwTableUNION;
GO
CREATE VIEW dbo.vwTableUNION AS SELECT 1 as A;
GO
DECLARE @sql NVARCHAR(MAX);

SELECT @sql = COALESCE(@sql + CHAR(13) + CHAR(10) + 'UNION ALL' + CHAR(13) + CHAR(10) ,'') + 'select * from ' + s.name + '.' + t.name
FROM sys.tables  t JOIN sys.schemas s on t.schema_id = s.schema_id
WHERE t.name LIKE 'tb%'; 

select @sql = ' ALTER VIEW dbo.vwTableUNION AS ' + @sql;

select @sql

EXEC(@sql);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources