select from table name with wild card

You will need to create the view from a stored procedure using the EXEC statement..

A view, no.  You would have to create a stored procedure, pass in the number, then execute dynamic SQL against it.

create procedure up_Something (@TableNum int)
as
declare @sql nvarchar(max)
set @sql = N'select * from table_name_' + cast(@tablenum as varchar(20))
 
exec sp_executesql @SQL

Select allOpen in new window

Thought I would add... the data is in sql server.. but I am pulling the data over to oracle through a database link.. thats why I am wanting a view that doesn't change.

I think it is a mix of reb73 and Brandon's solution as you will probably have to use dynamic sql like Brandon showed but instead of a simple select statement you have a create view vw_name as select ...

As for determining the table dynamically without having to pass in the number, you can try to find a column or something unique about the table like maybe the prefix that you can use to search the information schema for name like 'whatever%' or has column name equal 'whatever'.

This way the procedure can run without human intervention and you can schedule it to run on the frequency that the application changes tables and basically create a view with same name all the time.  You can either drop existing view OR after initial creation change the dynamic sql to an alter view statement.

You have to read them using dynamic SQL in a procedure and store them in a temporary table.
In the same sessions you can read the temporary table.

To encounter the table names:
FOR i IN (SELECT table_name  FROM user_tables WHERE table_name LIKE 'table_name_%') LOOP
   construct a string that uses UNION of all tables
END LOOP;

Information_Schema.Tables I think you want there:

select table_name from information_schema.tables where table_name like 'table_name_%'

You could write a stored procedure as indicated in the code snippet below, but there is a  caveat in that all tables created should be identical in structure, otherwise view creation may fail!

You'll need this procedure to be invoked every time a table is created that matches the pattern passed to the view - a trigger on the sysobjects is possible..

Comment the PRINT statement and uncomment the EXEC statement after testing the view initially to see if it generates the correct CREATE VIEW statement..

/*
	create table table_name_1 (testcol1 int null, testcol2 varchar(10) null, testcol3 datetime null)
	create table table_name_2 (testcol1 int null, testcol2 varchar(10) null, testcol3 datetime null)
	create table table_name_3 (testcol1 int null, testcol2 varchar(10) null, testcol3 datetime null)
	create table table_name_4 (testcol1 int null, testcol2 varchar(10) null, testcol3 datetime null)
*/
ALTER PROCEDURE dbo.sp_create_view
	@Pattern	sysname
AS
BEGIN
	SET NOCOUNT ON
	DECLARE  @TabCount	int
		,@Sql		varchar(8000)
		,@TableName	sysname
 
	IF	OBJECT_ID('tempdb..#TmpTables', 'U') IS NOT NULL
	BEGIN
		DROP TABLE #TmpTables
	END
 
	SELECT	TableName = TABLE_CATALOG + '.' + TABLE_NAME 
	INTO	#TmpTables
	FROM	INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE @Pattern + '%'
	ORDER BY 1
 
	SELECT	@SQL = 'CREATE VIEW dbo.DynamicView AS '
	WHILE EXISTS (SELECT 1 FROM #TmpTables)
	BEGIN
		SELECT	TOP 1 @TableName = TableName
		FROM	#TmpTables
 
		SELECT	@Sql = @SQL + 'SELECT * FROM ' + @TableName + ' UNION ALL '		
 
		DELETE #TmpTables WHERE TableName = @TableName
	END
 
	IF	OBJECT_ID('tempdb..#TmpTables', 'U') IS NOT NULL
	BEGIN
		DROP TABLE #TmpTables
	END
 
	SELECT @Sql = LEFT(@Sql, LEN(@Sql) - 10)
	PRINT	@Sql
	--EXEC	@Sql
END
GO
 
/* exec sp_create_view 'table_name_' */

Select allOpen in new window

The data is in SQL Server, so I would think you have to use SQL syntax to get the tables initially.  

ODOTServer -> I'm presuming the SQL Server version is SQL 2000 or later, is that correct?  (Thanks mwvisa1)

If not replace following text in the procedure with -

        SELECT  TableName = TABLE_SCHEMA + '.' + TABLE_NAME
        INTO    #TmpTables
        FROM    INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE @Pattern + '%'

with
        SELECT      TableName = su.[name] + '.' + so.[name]
        FROM      sysobjects so
        INNER JOIN sysusers su on su.uid = so.uid
        WHERE      [name] LIKE @Pattern + '%'

yes, its 2005...

the code above gives Return = 0
when I comment out the print and add the exec then I dont see the view that is created..

also, it looks like I will have a need for it to find where it has column name equal 'whatever'.
looks like there are two different types of tables that all start with tbl_RenderedReport###

Thanks

Like this for example I have a column called timestamp.

select t.table_schema + '.' + t.table_name
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name
where t.table_type = 'BASE TABLE' and c.column_name = 'timestamp'

Select allOpen in new window

> the code above gives Return = 0

If the table pattern is correct, then the procedure should certainly print the Create View statement for all matched tables  (with PRINT uncommented and EXEC commented out).  Even if there are no tables, it should still print out an incomplete CREATE VIEW statement!

If you definitely want a UNION of the tables, I would try it more like this -- as I think you may be getting an error as the above will leave an extra UNION ALL at the end.  Also to ensure that the SQL is not being cutoff by the length of the VARCHAR change length to MAX.

(I would also change tablename sql to this: select TableName = '[' + t.table_schema + '].[' + t.table_name + ']')

WHILE EXISTS (SELECT 1 FROM #TmpTables)
BEGIN
 
DECLARE @SQL AS VARCHAR(MAX);
SELECT @SQL = ISNULL(@SQL + ' UNION ALL ', '') 
    + 'SELECT * FROM ' + TableName
FROM #TmpTables;
 
SET @SQL = 'CREATE VIEW dbo.DynamicView AS ' + @SQL;
 
IF	OBJECT_ID('dbo.DynamicView', 'V') IS NOT NULL
	BEGIN
		DROP VIEW dbo.DynamicView
	END
 
PRINT	(@Sql)
END

Select allOpen in new window

AH, just saw in reb73 procedure, the last bit of subtracting 10 from the SQL string is probably to get rid of the extra UNION ALL, but if this may be more safe as it will only execute if you have a table and so your view won't be dropped unless have one to be created and so on.  As before, change PRINT(@SQL) to EXEC(@SQL) once tested.  My snippet wasn't intened to be complete as it was alterations to reb73 solution.  Will repost if unclear all of what I moved around.

"Even if no tables, it should still print out" - if no tables are found I get:  

Msg 102, Level 15, State 1, Procedure Dyna, Line 1
Incorrect syntax near 'Dyna'.

If tables are found, I get return=0

Still catching up... the mwvisa1 update at 09:08AM  got the code working...
its running.. (lots of records)

will try the column filter once it finishes...

"the mwvisa1 update at 09:08AM  got the code working..."


its running in a continuous loop.. i have 7300 of these lines so far.. trying the code with the column filter:

CREATE VIEW dbo.DynamicView AS CREATE VIEW dbo.DynamicView AS CREATE VIEW dbo.DynamicView AS CREATE VIEW dbo.DynamicView AS CREATE VIEW dbo.DynamicView AS CREATE VIEW dbo.DynamicView AS CREATE VIEW dbo.DynamicView AS CREATE VIEW dbo.DynamicView AS CREATE VIEW dbo.DynamicView AS CREATE VIEW dbo.DynamicView AS CREATE VIEW dbo.DynamicView AS CREATE VIEW dbo.DynamicView AS CREATE VIEW

Move this line to before the IF segment -

                SET @SQL = 'CREATE VIEW dbo.DynamicView AS ' + @SQL;

Actually no, ignore my last post.. <sheepish>

mwvista1's update at 09:28AM  worked..

to pull out duplicate records, I changed the line SELECT @SQL = ISNULL(@SQL + ' UNION ALL ', '')
to SELECT DISTINCT @SQL = ISNULL(@SQL + ' UNION ALL ', '')

if you agree thats the right location for the distinct.. then its all done.... thanks for all the help...

Yep, sorry about that.  That is why I was glad I took time to test and rewrite.  The WHILE loop should NOT have been in there.  It should be an IF so only executes once.  The DISTINCT shouldn't be necessary as I added GROUP BY to the statement getting the table names in the first place.  However if you are getting duplicate records, this may be what you need:
(UNION creates distinct results whereas UNION ALL does not)

ALTER PROCEDURE dbo.sp_create_view
	@TablePattern NVARCHAR(255)
	, @SearchColumn NVARCHAR(255)
AS
BEGIN
	SET NOCOUNT ON;
 
	-- ensure temp tables cleaned from last run
	IF	OBJECT_ID('tempdb..#TmpTables', 'U') IS NOT NULL
	BEGIN
		DROP TABLE #TmpTables
	END
 
	-- get matching table names from database
	SELECT TableName = '[' + t.table_schema + '].[' + t.table_name + ']'
	INTO #TmpTables
	FROM information_schema.tables t
	INNER JOIN information_schema.columns c on c.table_name = t.table_name
	WHERE t.table_type = 'BASE TABLE' 
		AND c.column_name LIKE @SearchColumn /* can use exact match or include % in passed string */
		AND t.table_name LIKE @TablePattern + '%'
	GROUP BY t.table_schema, t.table_name;
 
	-- if tables where found, run updates
	IF EXISTS (SELECT 1 FROM #TmpTables)
	BEGIN	 
		-- build dynamic sql script for view
		DECLARE @SQL AS VARCHAR(MAX);
		SELECT @SQL = ISNULL(@SQL + ' UNION ', '') 
			+ 'SELECT * FROM ' + TableName
		FROM #TmpTables;
		 
		SET @SQL = 'CREATE VIEW dbo.DynamicView AS ' + @SQL;
		 
		-- remove old view
		IF	OBJECT_ID('dbo.DynamicView', 'V') IS NOT NULL
			BEGIN
				DROP VIEW dbo.DynamicView
			END
		 
		-- execute dynamic sql script
		EXEC(@SQL);
	END
 
	-- cleanup temp tables
	IF	OBJECT_ID('tempdb..#TmpTables', 'U') IS NOT NULL
	BEGIN
		DROP TABLE #TmpTables
	END
END
GO

Select allOpen in new window

Left it with the union all... just doing union I got timeout errors doing a query on the view..


is a 50/50 split on the points between mwvisa1 and reb73, ok with everyone..

Ok by me!  Just glad it worked for you.  

Hopefully there are no longer duplicate records.  Not sure why you got an error doing the query -- can double check that when you remove ALL that still ended up with a space:
' UNION ALL' ==> ' UNION '
The space is important because when joined to the 'SELECT ...'.

Remember, you can always change the EXEC(@SQL) to PRINT(@SQL) to see what is wrong with the resulting script.

Otherwise, if space wasn't problem and just won't work, you can try this:
SELECT @SQL = ISNULL(@SQL + ' UNION ', '')
+ 'SELECT DISTINCT * FROM ' + TableName
FROM #TmpTables;

Or

SELECT @SQL = ISNULL(@SQL + ' UNION ', '')
+ 'SELECT * FROM ' + TableName
FROM #TmpTables GROUP BY TableName;

Depending on where you were seeing duplicate records.