question

KavithaChandra-0714 avatar image
0 Votes"
KavithaChandra-0714 asked MelissaMa-msft commented

How to get the table name from the subquery to the mainquery.

Hello,

New to the sql server need help with the below scenario.
Need help to get the table name from a sub query to the main query and also help me on selecting all the columns and records from the fetched table (i.e. table name fetched from the subquery)? Below is the sub query I have used.

select schema_name(t.schema_id) as schema_name,
t.name as table_name,
cast( REPLACE(t.name, 'storeId_', '') as int),
t.create_date,
t.modify_date
from sys.tables t
where t.name like 'storeId_2%' and
cast( REPLACE(t.name, 'storeId_', '') as int) =
(select MAX(cast(REPLACE(t.name, 'storeId_', '') as int))
from sys.tables t
where t.name like 'storeId_2%')

Thanks in Advance.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered MelissaMa-msft commented

If your query returns a single table name, then try something like this:

 declare @schema_name sysname
 declare @table_name sysname
    
 select 
     @schema_name = schema_name(t.schema_id),
     @table_name = t.name
 from sys.tables t
 where . . . etc. . . .
    
 declare @q nvarchar(max) = concat(N'select * from ', quotename(@schema_name), N'.', quotename(@table_name))
    
 exec (@q)


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the help, above query works.

0 Votes 0 ·

Hi @KavithaChandra-0714,

Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

No, Viorel's Answer is not the correct one.

The correct Answer is that there should not be tables storeid_1, storeid_2 etc. There should be a one single store table, and that number should be the leading key column of that table. Once you have this design, it all becomes a lot easier, because then you are in the realm for which a relational database is designed.

Viorel's answer only leads you further astray from the path that an SQL Server newbie should walk.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ErlandSommarskog commented

Hi @KavithaChandra-0714,

Welcome to Microsoft Q&A!

Please refer below:

 DECLARE @sql NVARCHAR(max);
    
 SELECT  @sql = 'SELECT * FROM ' + schema_name(t.schema_id) +'.'+
 t.name 
 from sys.tables t
 where t.name like 'storeId_2%' and
 cast( REPLACE(t.name, 'storeId_', '') as int) =
 (select MAX(cast(REPLACE(t.name, 'storeId_', '') as int))
 from sys.tables t
 where t.name like 'storeId_2%')
    
 EXECUTE sp_executesql @sql

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I maintain that this is the wrong solution. Kavitha is new to SQL Server. We should teach newbies what is the proper way to use a relational database, and not just blindly answer questions that are based on the wrong presumptions.

0 Votes 0 ·