CODEWITHSUNDEEP
sql-server
Franck
Franck

Reputation: 4440

Executing dynamic SQL with return value as a column value for each rows

I have a rather simple query that I started to modify in order to remove temp table as we have concurrency issues over many different systems and clients.

Right now the simple solution was to break up the query in multiple separate queries to replicate what SQL was doing before.

I am trying to figure out a way to return the result of a dynamic SQL query as a column value. The new query is quite simple, it look in the system objects for all table with specific format and output. What i am missing is that for each record i need to output the result of a dynamic query on each of those table.

The query :

SELECT [name] as 'TableName' 
FROM SYSOBJECTS WHERE xtype = 'U' 
AND (CHARINDEX('_PCT', [name]) <> 0 
OR CHARINDEX('_WHT', [name]) <> 0)

All these table have a common column called Result which is a float. What i am trying to do is return the count of this column under some WHERE clause that is generic and will work will all tables as well.

A desired query (i know it's not valid) would be :

SELECT [name] as 'TableName',
sp_executesql 'SELECT COUNT(*) FROM ' + [name] + ' WHERE Result > 0 OR (Result < 139 AND CurrentIndex < 15)' as 'ResultValue'
FROM SYSOBJECTS WHERE xtype = 'U' 
AND (CHARINDEX('_PCT', [name]) <> 0 
OR CHARINDEX('_WHT', [name]) <> 0)

Before it used to be easy. We had a temp table with 2 columns and were filling the table name first. Then we iterate on the temp table and execute the dynamic sql and return the value in an OUTPUT variable and simply update the record of the temp table and finally return the table.

I have tried a scalar function but it doesn't support dynamic SQL so it doesn't work. I would rather not create the 13,000~ different queries for the 13,000~ tables.

I have tried using a reference table and use trigger to update the status but it slow the system way to much. The average tables insert and delete 28 millions records. The original temp table query only took 5-6 minutes to execute due to very good indexing and now we are reaching 25-30 minutes.

Is there any other solution available than Querying the table list then the Client query each table one by one to know it status ?

We are using SQL Server 2017 if some new features are available now

Upvotes: 0

Views: 727

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

You can use this script for your purpose (tested in SQL Server 2016).

Updated: It should work now as the results are a single set now.

EXEC sp_msforeachtable
@precommand = 'CREATE TABLE ##Statistics 
    (TableName varchar(128) NOT NULL, 
    NumOfRows int)',
@command1 ='INSERT INTO ##Statistics (TableName, NumOfRows)
    SELECT ''?'' Table_Name, COUNT(*) Row_Count FROM ? WHERE Result > 0 OR (Result < 139 AND CurrentIndex < 15)',
@postcommand = 'SELECT TableName, NumOfRows FROM ##Statistics;
    DROP TABLE ##Statistics'
,@whereand = ' And Object_id In (Select Object_id From sys.objects
    Where name like ''%_PCT%'' OR name like ''%_WHT%'')'

For more details on sp_msforeachtable Please visit this link

Upvotes: 1

Related Questions