Avatar of ODOTServer
ODOTServerFlag for United States of America asked on

select from table name with wild card

I have an application that, for example,
creates table_name_1, table_name_2, table_name_3
then when it creates table_name_4 it deletes table_name_1.
then when it creates table_name_5 it deletes table_name_2.
and so on.. It drives me nuts, but its an application that I have no control over changing..
All tables have exact same columns.

Is this even possible..  for me to create a view that is "select * from table%" to pull the data from all the tables together in a name that doesn't change..
Oracle DatabaseMicrosoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Kevin Cross

8/22/2022 - Mon
reb73

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

BrandonGalderisi

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

Open in new window

ASKER
ODOTServer

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Kevin Cross

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.
schwertner

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;
Kevin Cross

Information_Schema.Tables I think you want there:

select table_name from information_schema.tables where table_name like 'table_name_%'
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
reb73

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_' */

Open in new window

Kevin Cross

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
reb73

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 + '%'
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
ODOTServer

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..
ASKER
ODOTServer

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



Kevin Cross

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'

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
reb73

> 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!

Kevin Cross

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

Open in new window

Kevin Cross

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
ODOTServer

"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
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
ODOTServer

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...




ASKER
ODOTServer

"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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
reb73

Move this line to before the IF segment -

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

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

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...
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Kevin Cross

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

Open in new window

ASKER
ODOTServer

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..
Kevin Cross

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.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.