How can I get a row count from all tables based on specific criteria

Question

I can get the row count of all tables using this query.

    SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
    i.rows
    FROM sys.tables AS t INNER JOIN
    sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2

**AND
t.name.programID = 4**

but I need to count all rows where programID = n I keep getting the following error when adding the bolded line to the where clause

Cannot call methods on nvarchar. Any help is greatly needed an appreciated Andy

Are you sure the column has name 'name.programID' ?

Robert
– Robert

2012-09-07 19:31:35 +00:00
Commented Sep 7, 2012 at 19:31 — Robert
– Robert, Commented Sep 7, 2012 at 19:31

Rodney Adams · Accepted Answer · 2012-09-07 19:49:25Z

The table name in your example can not be accessed like a property. It's just a string and not the table itself. I've comprised a solution that may work for you. It feels a little like a hack, but It will get you on your way.

DECLARE @Value INT, @Column_Name VARCHAR(MAX)

SELECT @Value = 4, @Column_Name = 'ProgramId'

IF OBJECT_ID(N'tempdb..#Results',N'U') IS NOT NULL
            DROP TABLE #Results

SELECT  '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name, SCHEMA_NAME(t.schema_id) AS     schema_name, t.name AS table_name,
i.rows [Row_Count]
INTO #Results
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2
INNER JOIN information_schema.COLUMNS  C ON t.name = c.TABLE_NAME AND SCHEMA_NAME(t.schema_id)= c.TABLE_SCHEMA  
WHERE C.COLUMN_NAME = @Column_Name

DECLARE @SQL VARCHAR(MAX)

SELECT @SqL = COALESCE(@SqL +' UNION ',' ')  + 'SELECT '''+ fulltable_name + ''', COUNT(*) FROM ' +     fulltable_name +     ' where ' + @Column_Name + ' = ' + CAST(@Value AS VARCHAR(1000))+ ' GROUP BY ' + @Column_Name     + ' '
FROM #Results

PRINT(@SqL)
EXEC (@SqL)

IF OBJECT_ID(N'tempdb..#Results',N'U') IS NOT NULL
            DROP TABLE #Results

EricZ · Accepted Answer · 2012-09-07 20:49:38Z

0

Are you sure all of your tables in the DB have column "programID"? If so.. try this

exec sp_MSforeachtable 'SELECT ''?'' AS TableName,COUNT(*) AS cnt FROM ? WHERE programID = 4'

Edit: This query will skip count the table without column"programID"

CREATE TABLE #t (table_name Varchar(500), record_count int)

INSERT INTO #t (table_name,record_count)
exec sp_MSforeachtable '
     IF EXISTS(SELECT * FROM sys.columns WHERE name = ''programID'' 
             and object_id = object_id(''?''))
     EXEC(''SELECT ''''?'''' AS TableName,COUNT(*) AS cnt FROM ? WHERE programID = 4'')'

SELECT * FROM #t

edited Sep 7, 2012 at 20:49

answered Sep 7, 2012 at 19:51

EricZ

6,2151 gold badge33 silver badges30 bronze badges

Collectives™ on Stack Overflow

How can I get a row count from all tables based on specific criteria

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related