Count records with a specified condition from from a list of tables in SQLServer(T-SQL)

Question

I'm trying to achieve something similar to this example PostgreSQL code but on SQLServer(T-SQL). I will attach the PGSQL code below.

The output I'm expecting is i have a list of table names in another table. SELECT table_name FROM dbo.table_list. This list could also come from the information schema.

Then i would like to count(*) the records in this table with the same where clause every time WHERE status = 'CURRENT' this field is present on each table in this list.

Here is an example of the PostgreSQL code:

SELECT
table_schema, 
table_name, 
(xpath('/row/cnt/text()' , xml_count))[1]::TEXT::INT AS row_count

FROM (
SELECT 
table_name, 
table_schema, 
query_to_xml(format('SELECT SUM(CASE WHEN status = ''CURRENT'' THEN 1 ELSE 0 END) AS cnt
             FROM %I.%I', table_schema, table_name), false, true, '') AS xml_count
FROM  information_schema.tables
WHERE table_schema =    'my_schema'
  AND table_name   LIKE 'new_%'
) my_fancy_table

This is the output I'm getting:

table_schema     table_name      row_count

-------------------    ---------------      --------------

my_schema         new_tt1                1265

my_schema         new_tt2                2111

my_schema         new_tt3                564589

my_schema         new_tt4                9999

my_schema         new_tt5                0

Ideally i would need this in a function like this:

CREATE OR REPLACE FUNCTION public.count_currs()
  RETURNS TABLE(a character varying, b character varying, c integer) AS
$BODY$
BEGIN
  RETURN QUERY (SELECT table_schema::VARCHAR, table_name::VARCHAR, (xpath('/row/cnt/text()', xml_count))[1]::text::int FROM ( SELECT table_name, table_schema, query_to_xml(format('select SUM(CASE WHEN status = ''CURRENT'' THEN 1 ELSE 0 END) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count from information_schema.tables where table_schema = 'my_schema' and table_name LIKE 'new_') t) ;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION public.count_currs()
  OWNER TO postgres;

So in my code i would have:

SELECT * FROM public.count_currs();

Please excuse my verbose explanation. I appreciate all the assistance!

-- EDIT

As has been requested you can use this to create some sample data and I'll attached the expected outcome. Please note that the number of tables and the table names are not set and could be 1 table could be 1000 tables i need this to be built dynamically based off the information schema (see code above) or from another table (see code above). The idea is that for each table in this list of tables returned we execute a query that will do something like this SELECT COUNT(*) FROM my_schema.table_name WHERE status = 'CURRENT' then group the counts from the x number of tables into a single result set :

Code to create sample data:

CREATE SCHEMA [my_schema];

CREATE TABLE [my_schema].[tt1](
    [status] [varchar](100) NULL);

CREATE TABLE [my_schema].[tt2](
    [status] [varchar](100) NULL);

CREATE TABLE [my_schema].[tt3](
    [status] [varchar](100) NULL);

CREATE TABLE [my_schema].[tt4](
    [status] [varchar](100) NULL);

CREATE TABLE [my_schema].[tt5](
    [status] [varchar](100) NULL);


INSERT INTO [my_schema].[tt1] VALUES ('CURRENT');
INSERT INTO [my_schema].[tt1] VALUES ('CURRENT');
INSERT INTO [my_schema].[tt1] VALUES ('CURRENT');
INSERT INTO [my_schema].[tt1] VALUES ('NOT_CURRENT');
INSERT INTO [my_schema].[tt1] VALUES ('NOT_CURRENT');
INSERT INTO [my_schema].[tt1] VALUES ('NOT_CURRENT');


INSERT INTO [my_schema].[tt2] VALUES ('CURRENT');
INSERT INTO [my_schema].[tt2] VALUES ('NOT_CURRENT');

INSERT INTO [my_schema].[tt3] VALUES ('CURRENT');
INSERT INTO [my_schema].[tt3] VALUES ('CURRENT');
INSERT INTO [my_schema].[tt3] VALUES ('CURRENT');
INSERT INTO [my_schema].[tt3] VALUES ('CURRENT');
INSERT INTO [my_schema].[tt3] VALUES ('NOT_CURRENT');
INSERT INTO [my_schema].[tt3] VALUES ('NOT_CURRENT');


INSERT INTO [my_schema].[tt4] VALUES ('CURRENT');
INSERT INTO [my_schema].[tt4] VALUES ('CURRENT');
INSERT INTO [my_schema].[tt4] VALUES ('CURRENT');
INSERT INTO [my_schema].[tt4] VALUES ('CURRENT');
INSERT INTO [my_schema].[tt4] VALUES ('CURRENT');

INSERT INTO [my_schema].[tt5] VALUES ('CURRENT');
INSERT INTO [my_schema].[tt5] VALUES ('NOT_CURRENT');
INSERT INTO [my_schema].[tt5] VALUES ('NOT_CURRENT');
INSERT INTO [my_schema].[tt5] VALUES ('NOT_CURRENT');
INSERT INTO [my_schema].[tt5] VALUES ('NOT_CURRENT');

Then i would expect this a result: Example of output

Answer 1

Good day,

Assuming I understand your need and we can avoid using function, then this is a simple case of dynamic query. To confirm it please check if the following query fits your need. Use Stored Procedure to execute the code instead of function (again, assuming this fit your needs).

As you said, you have a table with the list of the tables (or we can use the system tables in order to get the list of tables), so first I will create it - this is not part of the solution:

CREATE TABLE table_list (S_Name sysname, T_Name sysname)
GO
INSERT table_list (S_Name, T_Name) values 
('my_schema','tt1'),
('my_schema','tt2'),
('my_schema','tt3'),
('my_schema','tt4'),
('my_schema','tt5')
GO

And now we can solve your needs with simple dynamic query

----------------------------- Solution
DECLARE @query NVARCHAR(MAX)
;With MyCTE AS (
SELECT 
    MyQueryOnSingleTable = 'SELECT ''' + S_Name + ''' as [Schema_name], ''' + T_Name + ''' as [Table_Name]'
    + ', Rows_Counted_as_Current = COUNT(status) FROM ' 
    + QUOTENAME(S_Name) + '.' + QUOTENAME(T_Name)
    + 'WHERE status = ''CURRENT'''
FROM table_list
)
SELECT @query = STRING_AGG(MyQueryOnSingleTable, 'UNION ALL ')
FROM MyCTE

PRINT @query
EXECUTE sp_executesql @query
GO

Answer 2

SQL server has no analog of query_to_xml and no way of EXEC-ing a dynamic SQL Statement built up as part of the same query except in a CLR function.

The CLR function below does what you need (TODO: add error handling) but you might also consider generating the entire dynamic SQL statement accessing all of the tables up front - or just execute separate queries to get the same result.

using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{

    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlInt32 CountRowsWithCurrentStatus(SqlString schemaName, SqlString tableName)
    {
        string quotedSchemaName = string.Format("[{0}]", schemaName.Value.Replace("]", "]]"));
        string quotedTableName = string.Format("[{0}]", tableName.Value.Replace("]", "]]"));

        string query = "SELECT COUNT(*) FROM " + quotedSchemaName + "." + quotedTableName + " WHERE status = 'CURRENT'";

        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            using (SqlCommand cmd = new SqlCommand(query, connection))
            {
                return new SqlInt32((int)cmd.ExecuteScalar());

            }
        }
    }
}

with that in place the query is

SELECT table_name,
       table_schema,
       [dbo].[CountRowsWithCurrentStatus](table_schema, table_name)
FROM   information_schema.tables
WHERE  table_schema = 'my_schema'
       AND table_name LIKE 'tt_%'