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

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

Answers


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

Need Your Help

Why does C++11 allow for GC?

c++ c++11 garbage-collection

C++11 allows its implementations to perform (some) garbage collection utilities. Why would the standard allow this? I was always under the impression that in C++, you don't pay for what you don't u...

fast SVD algorithm

java algorithm math linear-algebra

I'm looking for a fast library to compute SVD (Singular Value Decomposition) in Java.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.