Get count for records

1

Hello,

wanted to know how to get count from all table for the value of column.

eg. i know i have 350 table with same column (idrec) but i want to get count from all 350 tables where idrec values in ('id1','id2','id3''......)

how to get that.

thanks

2

This will gen the SELECTs, then just copy the results and run:

SELECT 'SELECT COUNT(*) FROM [' + SCHEMA_NAME(t.schema_id) + '].' + 
    '[' + OBJECT_NAME(c.object_id) + '] ' +
    'WHERE idrec IN (''id1'', ''id2'', ''id3'' /*, ...*/)'
FROM sys.columns c
INNER JOIN sys.tables t ON t.object_id = c.object_id
WHERE c.name = 'idrec'
1 Like
3

hi,

is it possible to get tablename as well with the query?

4

Yes, it's already getting the table name to use in the query.

If you need to include the table name in the results, you can do this:

SELECT SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(c.object_id) AS table_name,
    'SELECT COUNT(*) FROM [' + SCHEMA_NAME(t.schema_id) + '].' + 
    '[' + OBJECT_NAME(c.object_id) + '] ' +
    'WHERE idrec IN (''id1'', ''id2'', ''id3'' /*, ...*/)'
FROM sys.columns c
INNER JOIN sys.tables t ON t.object_id = c.object_id
WHERE c.name = 'idrec'
1 Like