CODEWITHSUNDEEP
sql-servert-sql
bumble_bee_tuna
bumble_bee_tuna

Reputation: 3563

Database Table Row Counts Where Column Value is True

I'm trying to modify this query to give me the row counts for each table in the database where the table contains the column "Archive" and the total row counts along with the row count where "Archive = 1". I've got the first part down but I'm having a brain fart with the second part.

SELECT sc.name +'.'+ ta.name TableName,SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 AND OBJECT_NAME(pa.object_id) IN 
 (
       --Criteria 1
   SELECT iq.TABLE_NAME
   FROM INFORMATION_SCHEMA.COLUMNS iq
   WHERE COLUMN_NAME LIKE '%Archive%'
 )
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

Thanks in advance for any help or suggestions.

Upvotes: 0

Views: 176

Answers (1)

JohnLBevan
JohnLBevan

Reputation: 24470

It's a bit nasty, but hopefully this will work:

create table #result 
(
    schemaName sysname, tableName sysname, recordCount bigint, archiveCount bigint, sqlCommand nvarchar(max)
)
alter table #result add primary key clustered (schemaName, tableName)

insert #result (schemaName, tableName, recordCount, archiveCount, sqlCommand)
SELECT sc.name, ta.name, 0, 0, 'update r set sqlCommand=null, recordCount=cnt, archiveCount=arch from #result r inner join 
(select ' + quotename(sc.name,'''') + ' schemaName, ' + quotename(ta.name,'''') + ' tableName, count(1) cnt, count(case' +
   (SELECT
        ' when ' + QUOTENAME(c2.name) + ' = 1 then 1' 
        FROM sys.columns c2
        WHERE c2.object_id = ta.object_id
        and c2.name like '%Archive%'
        and c2.system_type_id = 56 --int
        FOR XML PATH(''), TYPE
   ).value('.','varchar(max)')
   + ' else null end) arch from ' + QUOTENAME(sc.name) + '.' + QUOTENAME(ta.name) + ') x on x.schemaName=r.schemaName and x.tableName=r.tableName' 
FROM sys.tables ta
INNER JOIN sys.schemas sc 
    ON sc.schema_id = ta.schema_id
INNER JOIN sys.columns c
    ON c.object_id = ta.object_id
WHERE ta.is_ms_shipped = 0 
and c.name like '%Archive%'
and c.system_type_id = 56 --select name, system_type_id from sys.types where name='int'
GROUP BY sc.name,ta.object_id,ta.name

declare @sql nvarchar(max)
select @sql = sqlCommand from #result where sqlCommand is not null
while @sql is not null
begin
    print @sql
    exec(@sql)
    set @sql = null
    select @sql = sqlCommand from #result where sqlCommand is not null
end

select * from #result

Upvotes: 1

Related Questions