从DynamicNamed表中选择基于静态表的数据

问题描述

Hii EveryOne，



再一次，我需要你们所有人的帮助..



我在SQL数据库中有未知数量的动态表



就像下面一样，

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	      [email protected]
aaa	      [email protected]

=>bbb_EmailInfo
UserCode	EmailId
bbb	      [email protected]
bbb	      [email protected]
bbb	      [email protected]


Declare @CheckEmail VARCHAR(500) = '[email protected]'

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

FROM User_Master

这里，

User_Master是静态表，

但aaa_EmailInfo和bbb_EmailInfo是动态生成的，名称为UsedCode ..

现在我想检查@CheckEmail是否存在于动态表中？不是??



怎么可能？



任何人都可以建议我SELECT查询???

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

推荐答案

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

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.

这篇关于从DynamicNamed表中选择基于静态表的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！