How to count occurrences of value in all tables that have a specific column?

Question

I have a database with hundreds of tables, some having a column named column1. I want to count occurrences of a specific value in these tables like this:

Table  | Count
-------|------
table1 |  0
table2 |  5
...

First part (finding list of tables) is done using the code from here. But I don't know how to put these names in a query. As explained in another question, table names and column names should be static in a static query. What I get from that answer is that I have to generate some tsql like:

SELECT 'table1' AS [Table], count(*) AS [Count] FROM table1 WHERE column1 = somevalue
UNION SELECT 'table2' AS [Table], count(*) AS [Count] FROM table2 WHERE column1 = somevalue
UNION ...

and then run it using EXEC or sp_executesql.

So, are there any other options to automate this?

There is not any other options. You need to query `sys.columns` to get your tables that have that column, and from that return you need to dynamically stitch together you big UNION sql, and then execute that sql. You can do that in a stored proc, or really any language ourside your server that can connect to the server and execute sql. — JNevill, Dec 15 '18 at 12:04
Something like `SELECT 'SELECT ''' + t.name + ''' AS [Table], count(*) as [Count] FROM [' + t.name + '] WHERE [' + c.name + '] = somevalue ' + CASE WHEN LEAD(c.name) OVER (ORDER BY t.name) IS NOT NULL THEN 'UNION ALL ' END FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE '%MyCol%';` (if I wrote that correctly) should pop out your big UNION without having to muck around with a cursor or other scripting. Then you can just execute whatever is spit out (still using EXEC or sp_executesql or manual). I don't write TSQL often though, so that may need some tweaking. — JNevill, Dec 15 '18 at 12:09
I agree with JNevill, a UNION ALL and generating the SQL either using his approach or using excel. — Eponyme Web, Dec 15 '18 at 12:43
Thanks @JNevill, I think it is an answer if it concatenates the rows and executes the result. Actually I tried it myself, but the strange thing is that if I copy the concatenated result manually and run it as a new query it works fine, but when I keep it in a variable and try to run it in same query using `EXEC`, it returns rows correctly but counts are different (reduced in fact)! Any idea? — saastn, Dec 15 '18 at 13:58
Tip: The best practice when assembling object names into dynamic SQL statements is to use [`QuoteName()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. — HABO, Dec 15 '18 at 15:53

score 0 · Accepted Answer · answered Dec 17 '18 at 05:34

Thanks to JNevil, I used the code suggested in his comment. Parts that need to be changed are marked with -- <--.

Briefly, it finds names of all tables that have a column named columnname, and does some string manipulations to generate a SELECT command for each one of them, then all command are concatenated to form a single UNION command. Finally it runs this command using EXEC.

USE dbname; -- <-- the db name

DECLARE @Command AS VARCHAR(MAX);

SET @Command = (
        SELECT (
                SELECT (
                        'SELECT ''' + t.NAME + ''' AS [Table], count(*) as [Count] FROM [' + t.
                        NAME + '] WHERE [' + c.NAME + '] like ''%somevalue%'' ' + CASE  -- <-- the value that you are looking for
                            WHEN LEAD(c.NAME) OVER (
                                    ORDER BY t.NAME
                                    ) IS NOT NULL
                                THEN 'UNION ALL ' + CHAR(10)
                            END
                        )
                FROM sys.columns c
                INNER JOIN sys.tables t ON c.object_id = t.object_id
                WHERE c.NAME = 'columnname' -- <-- the column name
                FOR XML path('')
                )
        );

SET @Command = SUBSTRING(@Command, 1, LEN(@Command) - 12); -- remove last UNION ALL

-- PRINT @Command; -- just to see the result

EXEC (@Command);

How to count occurrences of value in all tables that have a specific column?

1 Answers1