Select from DynamicNamed Table which is Based on Static Table's Data

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

Hii EveryOne,

Once more I need help from all of u..

I have Unknown numbers of Dynamic Tables in SQL DataBase

Just like Below,

=>User_Master
UserCode UserName
aaa	      AAA
bbb	      BBB

=>aaa_EmailInfo
UserCode	EmailId
aaa	      aaa@gmail.com
aaa	      aaa1@gmail.com

=>bbb_EmailInfo
UserCode	EmailId
bbb	      bbb@gmail.com
bbb	      bbb1@gmail.com
bbb	      bbb2@gmail.com


Declare @CheckEmail VARCHAR(500) = 'aaa@gmail.com'

SELECT
	
	User_Master.UserCode,
	
	(SELECT User_Master.UserCode+'_EmailInfo'.EmailId FROM User_Master.UserCode+'_EmailInfo') EmailId

FROM User_Master

Here,
User_Master is Static Table,
But aaa_EmailInfo and bbb_EmailInfo are Dynamically generated with Named on UsedCode..
Now i want to Check that @CheckEmail is Exist in Dynamic Tables on Not??

How is it Possible??

Can anyone Suggest me SELECT Query ???

Posted 14-Nov-14 1:56am

Kiran Akabari

Add a Solution

Comments

Sinisa Hajnal 14-Nov-14 8:12am

it may be objectname instead of name below, check the column names without the condition and adapt.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sinisa Hajnal · Accepted Answer · 2014-11-14T02:11:00

Solution 1

Writing from memory, but it goes something like this:

SQL

SELECT *
FROM sys.tables WHERE name like '%_EmailInfo'
-- or any other search condition

SQL

DECLARE @email_tables table (id int IDENTITY (1,1), table_name varchar(255))

INSERT INTO @email_tables( table_name)
SELECT objectname FROM sys.tables WHERE name like '%_EmailInfo'

DECLARE @i int
SET @i = 0

DECLARE @sql_query nvarchar(MAX)
DECLARE @table_name varchar(255)
DECLARE @exists int
WHILE (SELECT TOP 1 FROM @email_tables WHERE id > @i ORDER BY id)
BEGIN
SELECT TOP 1 @i = id, @table_name = table_name FROM @email_tables ORDER BY id)

SET @sql_query = 'SELECT COUNT(*) FROM ' + @table_name + ' WHERE emailid = ''' + @CheckEmail + '''

EXEC @exists = sp_Execute @sql_query (you'll have to check exact syntax here )

END

If this helps please take time to accept the solution. Thank you.

Posted 14-Nov-14 2:11am

Sinisa Hajnal

Updated 14-Nov-14 2:41am

v2

Comments

Kiran Akabari 14-Nov-14 8:26am

Ya friend,
It gives me Just all Table Names,
But how can i check datas of EmailId in all that tables??

Sinisa Hajnal 14-Nov-14 8:31am

Create temporary table and fill it with the result of the above query. Then loop through the result and do IF EXISTS (your query to check for @Email)

Sinisa Hajnal 14-Nov-14 8:41am

Updated solution.

Kiran Akabari 14-Nov-14 23:55pm

I tried this but cant understand that while statement, and it also gives error in while statement..

Is their any change in it?? if yes then plz suggest me...

Sinisa Hajnal 17-Nov-14 2:00am

While statement: it takes first lowest ID (which is ordered sequence number) greater then @i (which is initially zero) - it simply loops through all the records in @table_name. And "@table_name" should be "@email_tables". This variable holds the list of your EmailInfo tables.

Kiran Akabari 14-Nov-14 23:58pm

I gives Syntax error near FROM

Sinisa Hajnal 17-Nov-14 1:58am

As I said, I wrote from memory, I didn't have SQL server handy. You could try and debug. For example, there is closed bracket - ) - in SELECT TOP 1 under BEGIN at the end of the line.

It's not hard.

Kiran Akabari 17-Nov-14 2:20am

Relax bro,
I have solved it..
Its too long process but its ok..
My works going on with your given hint..

Thanks to u so much.. thank u very very much..

Sinisa Hajnal 17-Nov-14 2:44am

Sorry, I get miffed when people expect "pixel perfect" solution. I wrote without tools and thus made couple of mistakes. I'm glad the idea helped, that was ...well, the idea :)

Kiran Akabari 17-Nov-14 2:20am

Have some changes in it..

But Greatful to thanking u.. i solved it..

Thanks