Getting tables in a like

Question

Post reply

Getting tables in a like

giontech

Ten Centuries

Points: 1094
More actions
May 21, 2008 at 6:02 am

#187989

I have some tables that have prefixes like WRT_(WRT_01, WRT_02, WRT_03). I need help in how to get data from the tables that are for example like 'WRT_%' and are in a specific date range (all the tables have the same columns including the date)
Is this possible? Or a workaround?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

w.lengenfelder SSC Eights! Points: 884 More actions · Answer 1

you can create the SQL statement as following

DECLARE @sql VARCHAR(MAX)

SET @sql = ''

SELECT @sql = @sql +'SELECT * FROM [' + name + '] ' + char(13) + 'UNION '

FROM sys.objects

WHERE TYPE = 'U'

AND Name Like 'WRT_%'

SET @sql = left(@SQL,len(@SQL) - 7)

PRINT @sql

EXEC (@SQL)

You can put this into a Stored Proc to get the results adhoc

w.lengenfelder

giontech Ten Centuries Points: 1094 More actions · Answer 2

I'm getting "Invalid length parameter passed to the SUBSTRING function.", also how would this be put into a stored procedure?

Thanks

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 3

I do have to ask the stupid question... you are running the code from the database that has the WRT_% tables in it, aren't you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

giontech Ten Centuries Points: 1094 More actions · Answer 4

Yes I have tables like 'WRT_%', and I think I solved the left length problem by storing it in a variable.

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 5

Cool... if you get the chance, can you post your solution? I'm sure lot's of folks would like to see it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)