Is there a way to combine multiple query into one in SQL Server 2019?-CodePudding

I am wondering if there is a function in SQL Server or service in Azure to provide an opportunity to combine multiple queries into one. For example,

ALTER TABLE1 
    ADD RetailValue AS (CAST(QtyAvailable AS int) * CAST(UnitPrice AS int) * 1.5);
ALTER TABLE2 
    ADD RetailValue AS (CAST(QtyAvailable AS int) * CAST(UnitPrice AS int) * 1.5);
ALTER TABLE3 
    ADD RetailValue AS (CAST(QtyAvailable AS int) * CAST(UnitPrice AS int) * 1.5);
...

Is there a way to combine all these together into 1 query that can affect all the tables? the columns will be all same for every table and there are simply just too many tables to do copy and paste.

Thank you!

CodePudding user response：

use the stored procedure sp_msforeachtable with filtering table.

The placeholder `?' is denoting the table.

EXEC sp_msforeachtable
 @command1 ='ALTER TABLE ? 
    ADD RetailValue AS (CAST(QtyAvailable AS int) * CAST(UnitPrice AS int) * 1.5)'
,@whereand = ' And Object_id In (Select Object_id From sys.objects
Where name in ( ''TABLE1'',''TABLE2''))'

CodePudding user response：

Using Dynamic SQL you can easily construct the required alter statements and aggregate them into a single string to execute:

declare @Sql nvarchar(max);
select @Sql = String_Agg(
  'alter table dbo.'   QuoteName([name])   ' ADD RetailValue AS (CAST(QtyAvailable AS int) * CAST(UnitPrice AS int) * 1.5); '
  ,'')
from sys.tables where name in ('table1','table2', 'table3')
  and schema_id = Schema_Id('dbo');

print @Sql;
exec (@Sql);

See a demo Fiddle