How can I return a distinct count of a variable for all tables in my database?

Question

I have a SQL database with 60+ tables, almost all of which are populated with a CLIENTID field. I want to count the number of unique client IDs in each table.

The results I'm looking for are:

TABLE_NAME; CLIENTID_COUNT
dbo.HISTORY; 650
dbo.VISITS; 596
dbo.SALES; 1053
...; ...

This seems like it should be so simple but I've been playing around with cursors for hours and can't figure this one out. Please help!

Can you share your code and the results you have gotten so far? — Jeremy J., Oct 03 '18 at 19:29
Possible duplicate of [Find a string by searching all tables in SQL Server Management Studio 2008](https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server-management-studio-2008) — Ivan Starostin, Oct 03 '18 at 19:34
Essentially, there are system stored procedures which will list all tables. Use that as the basis and see the suggestion from @IvanStarostin — Peter Smith, Oct 03 '18 at 19:41

Ian-Fogelman · Answer 1 · 2018-10-03T19:53:48.953

IF OBJECT_ID('tempdb..#temp_RESULTS') IS NOT NULL DROP TABLE #temp_RESULTS
CREATE TABLE #TEMP_RESULTS
(
TABLENAME VARCHAR(MAX),
CLIENTCNT BIGINT
)

DECLARE @TABLENAME VARCHAR(MAX)
DECLARE @command VARCHAR(MAX)

IF OBJECT_ID('tempdb..#temp_PROCESS') IS NOT NULL DROP TABLE #temp_PROCESS
SELECT * INTO #TEMP_PROCESS FROM sys.tables 

WHILE EXISTS(SELECT * FROM [#TEMP_PROCESS])
BEGIN
SET @TABLENAME = (SELECT TOP 1 [NAME] FROM [#TEMP_PROCESS])
SET @command = ('SELECT ''' + @TABLENAME + ''', COUNT(DISTINCT CLIENTID) AS CLIENTCNT FROM ' + @TABLENAME)
SELECT @command
INSERT INTO #TEMP_RESULTS
EXEC(@command) 

DELETE FROM [#TEMP_PROCESS] WHERE [NAME] = @TABLENAME
END

SELECT * FROM [#TEMP_RESULTS]

I think the insert should actually be "SELECT ''' + @TABLENAME + ''', COUNT(DISTINCT CLIENTID) AS CLIENTCNT FROM ' + @TABLENAME" — UnhandledExcepSean, Oct 03 '18 at 19:52

score 1 · Answer 2 · answered Oct 03 '18 at 20:07

Assuming the column is exactly ClientId in every table, you should be able to use this as is:

DROP TABLE IF EXISTS #clientId
CREATE TABLE #clientId
(
    TableName nvarchar(1000),
    ClientIdCount bigint
)

DECLARE @TableName nvarchar(1000);
DECLARE @CurrentQuery nvarchar(2000);

DECLARE result_cursor CURSOR local fast_forward FOR
SELECT DISTINCT
    '['+TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMN_NAME = 'ClientId'

OPEN result_cursor
FETCH NEXT FROM result_cursor into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN 

SET @CurrentQuery = 'SELECT ''' + @TableName + ''', COUNT(DISTINCT ClientId) FROM ' + @TableName
--print @CurrentQuery

INSERT INTO
    #clientId
(
    TableName,
    ClientIdCount
)
EXEC(@CurrentQuery)

FETCH NEXT FROM result_cursor into @TableName
END
--end loop

--clean up
CLOSE result_cursor
DEALLOCATE result_cursor

GO

SELECT
    *
FROM
    #clientId

score 1 · Answer 3 · answered Oct 03 '18 at 20:15

You could use dynamic sql.

This will read through your system tables, find those that have a ClientID column, and build the text of a query that's in the general shape of 'Select Count(DISTINCT ClientID)' from each table.

DECLARE @SQLQuery as nvarchar(max) = ''

------------------------------------
-- GET THE TABLES THAT HAVE A CLIENTID FROM SCHEMA
SELECT @SQLQuery = @SQLQuery + qryTxt FROM (
    SELECT DISTINCT 'SELECT ''' + tables.name + ''', COUNT(DISTINCT CLIENTID) FROM ' + tables.name + ' UNION ' AS qryTxt
    FROM sys.columns left join sys.tables on columns.object_id = tables.object_id where columns.name = CLIENTID AND isnull(tables.name, '') <> '') subquery

------------------------------------
-- REMOVE THE LAST 'UNION' KEYWORD FROM SQLQUERY 
SET @SQLQuery = left(@sqlQuery, len(@sqlQuery) - 5)

------------------------------------
-- EXECUTE
execute sp_executesql @SQLQuery

LOL you and I posted very similar solutions just seconds apart. :) — Sean Lange, Oct 03 '18 at 20:15

score 1 · Answer 4 · answered Oct 03 '18 at 20:15

I really dislike cursors and loops. Even though this is not going to be much difference for a performance perspective I like to share how you can leverage the system tables and dynamic sql to avoid using a cursor, while loop or temp tables for something like this.

This code is literally all you need to to do this.

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'select TableName = ''' + t.name + ''', ClientID_Count = count(distinct clientID)
from ' + QUOTENAME(t.name) + ' UNION ALL ' 
from sys.tables t
join sys.columns c on c.object_id = t.object_id
where c.name = 'clientID'

select @SQL = left(@SQL, len(@SQL) - 10) --removes the last UNION ALL

select @SQL
--once your comfortable the dynamic sql is correct just uncomment the line below.
--exec sp_executesql @SQL

Nick Allan · Answer 5 · 2018-10-03T21:45:41.080

A similar pattern to other answers here, but this is how I would tackle it:

IF OBJECT_ID('#Tables', 'U') IS NOT NULL
    DROP TABLE #Tables;
SELECT ID = IDENTITY(INT, 1, 1),
    SchemaName = OBJECT_SCHEMA_NAME([object_id]),
    TableName = OBJECT_NAME([object_id]),
    ColumnName = name,
    DistinctCount = 0
    INTO #Tables
    FROM sys.columns
    WHERE name = 'CLIENTID';

DECLARE @ID INT = 1,
    @MaxID INT = (SELECT MAX(ID) FROM #Tables);
WHILE @ID < @MaxID
BEGIN;
    DECLARE @SQLCommand VARCHAR(MAX);
    SELECT @SQLCommand = FORMATMESSAGE('
UPDATE #Tables SET DistinctCount = (
    SELECT COUNT(DISTINCT %s) FROM %s.%s
    )
    WHERE ID = %i;',
        QUOTENAME(ColumnName), QUOTENAME(SchemaName), QUOTENAME(TableName), ID)
        FROM #Tables
        WHERE ID = @ID;
    EXEC (@SQLCommand);
    SET @ID += 1;
END;

SELECT *
    FROM #Tables;

How can I return a distinct count of a variable for all tables in my database?

5 Answers5