CODEWITHSUNDEEP
sqlsql-serversql-server-2008
I A Khan
I A Khan

Reputation: 8841

Counting rows for all tables for a particular column and column value

I am using SQL Server 2008 and want to get total row count of a column from all tables in a database.

here is my sample data

Table 1

T1Id       Name
1          XCV
2          ASD
3          GHJ

Table 2

  T2Id      T1Id       Name
  1         1          TYU
  2         1          BBB
  3         2          LLL
  4         1          JJJ
  5         1          LKM
  6         2          POI

Table 3

  T3Id      T1Id       Name
  1         3          DDS
  2         3          BSS
  3         2          SQA
  4         2          FWF
  5         1          IKM
  6         2          PLO

I have Table4 also but don't want to add in result count

I am trying 

    SELECT     TOP (100) PERCENT ta.name AS TableName, SUM(pa.rows) AS RowCnt
FROM         sys.tables AS ta LEFT OUTER JOIN
                      sys.partitions AS pa ON pa.object_id = ta.object_id LEFT OUTER JOIN
                      sys.schemas AS sc ON ta.schema_id = sc.schema_id LEFT OUTER JOIN
                      INFORMATION_SCHEMA.COLUMNS ON ta.name = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE     (ta.is_ms_shipped = 0) AND (pa.index_id IN (1, 0)) AND (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = N'T1Id') AND 
                      (ta.name <> N'Table4')
GROUP BY sc.name, ta.name
HAVING      (SUM(pa.rows) > 0)
ORDER BY RowCnt DESC

Above SQL giving me result 

 TableName    RowCnt
 XCV          5
 ASD          5
 GHJ          2

but I want to get only XCV (Table1 and T1Id = 1) records counts . How to do this with above query or any other suggestion ?

Expected result 

 Name    RCount
 XCV     5

Edit :

I have more then 200 tables in my database and it have T1Id in All tables, my aim to find the count of T1Id = 1 from All tables and I dont want to include Table 4 in Results.

Upvotes: 1

Views: 2436

Answers (1)

StuartLC
StuartLC

Reputation: 107237

It seems you want to 'generically' find all values in any table column named T1Id with a value of 1 (except for Table4, and I guess you'll want to exclude Table1 row counts as well, as this seems to be the 'master' reference table of column value-name mappings).

AFAIK you will need to resort to dynamic Sql in order to apply the filter.

Unfortunately your sample query is misleading - the query actually returns 

Table2  6
Table3  6
Table1  3

Which are the overall rowcounts of the tables obtained from sys.partitions.rows (i.e. not filtered in anyway).

I believe the approach needed is, after finding the 'Eligible' Tables containing the column of interest, what you need to be doing is actually querying each table, and then filtering each with your required predicate (i.e. T1ID = '1'). You then need to sum up all of these results to project your final result.

My solution is too horrid to contemplate posting here, but I've put up a Fiddle here and its on a Gist Here. Basically, it generates the following dynamic Sql:

WITH summedCte AS 
( 
    SELECT T1ID, COUNT(*) AS RowInTable  FROM [dbo].[Table2] GROUP BY T1ID  
    UNION ALL  
    SELECT T1ID, COUNT(*) AS RowInTable  FROM [dbo].[Table3] GROUP BY T1ID 
)
SELECT T.Name, t.T1Id, SUM(c.RowInTable) AS RowsInAllTables
    FROM summedCte c
    INNER JOIN Table1 t
      on c.T1Id = t.T1Id
    WHERE t.Name = 'XCV'
    GROUP BY t.Name, t.T1Id;

Which you can obviously tailor to meet your predicate needs.

Edit - Caveat

After seeing the OP's comment, viz that the intended usage is to enforce a 'may not delete if in parent row is in use' business rule in an application, it is clear that use of a cursor and loop is not fit for production.

The preferred approach should be along the lines of an ORM or other productivity tool:

  • Use the dynamic sql hack to build the CTE based query (i.e. instead of sp_executesql @sql, simply print @sql and grab the resultant SQL)
  • The resultant CTE (UNION of ~200 tables) can then be baked into your application to enforce the business rule.
  • However, each time any relevant database DDL is changed, then the CTE will need to be regenerated and re-absorbed into the app e.g. a build could be avoided e.g. by retaining the CTE query as a tokenized script.

Upvotes: 1

Related Questions