October 12, 2012 at 6:30 am
I have a table (TableCOunts) that stores the name of the tables and the row counts.
and the individual tables(Table1 and Table2)
Create Table [TableCounts]
(
TableName varchar(50),
RecCount int
)
Create Table [Table1]
(
[Names] varchar(50)
)
Create Table [Table2]
(
[Names] varchar(50)
)
Create Table [ErrorCOunt]
(
TableName varchar(50),
RecCount int,
ActualCount int
)
Insert INTO [TableCounts] ('Table1', 1)
Insert INTO [TableCounts] ('Table2', 2)
Insert INTO [Table1] ('MS')
Insert INTO [Table2] ('SQL')
Insert INTO [Table2] ('SERVER')
Insert INTO [Table2] ('SERVERS')
I would like to create a SP that follows thelogic
1. Check the count(*) of each individual table against the corresponding table name that contains the row counts.
For eg. Table 1 had 1 record. and reccount to Table1 in Tablecount is also 1.
If the record count does not match then insert the tablename,reccount and the actual count (count(*)) in the Error table.
The [TableCounts] table have all the tables names that exists in the database.
So I Would require to do a check on for Table1 and Table2 and this list would extend future.
Kindly Help me out with to achieve this.
Thanks,
Sandesh
October 12, 2012 at 8:24 am
This has to be one of the strangest processes I have ever seen. You have a table to record the rowcount from every table. Why? It will always be out of synch. With the way you described the process you will have nothing but errors unless there is something to update this table frequently. This just seems like there is nothing to gain from this and lots that can go wrong.
As silly as this is this I tossed this together. You should be able to modify this a little bit to get what you want.
create table #RowCounts
(
TableName varchar(255),
RecCount int,
ActualCount int
)
declare @sql varchar(max) = ''
select @sql = @sql + 'insert #RowCounts select ''' + TableName + ''', ' + cast(RecCount as varchar(10)) + ', count(*) from ' + TableName + ';'
from TableCounts
--select @sql --this will let you view the dynamic sql
exec(@sql)
insert ErrorCOunt
select * from #RowCounts
where RecCount <> ActualCount
select * from errorCount
drop table #RowCounts
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 12, 2012 at 8:28 am
Wow. As Sean said, this is pretty weird as is always going to be out of sync.
Because it's always going to be out of sync, you might as well do something like this: -
SELECT t.[name] AS [Table],
SUM(p.rows) AS [RowCount]
FROM sys.tables t
LEFT OUTER JOIN sys.partitions p ON t.object_id = p.object_id
LEFT OUTER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE p.index_id IN(0,1)
AND p.rows IS NOT NULL AND a.type = 1 -- row-data only , not LOB
GROUP BY t.[name];
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply