Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with Logic

Author  Topic 

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2012-10-12 : 08:23:37
Hi,

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-12 : 09:20:03
[code]SELECT
tc.TableName,
tc.RecCount,
st.row_count
FROM
TableCounts tc
INNER JOIN sys.dm_db_partition_stats st
ON tc.TableName = OBJECT_NAME(st.OBJECT_ID)
WHERE
st.index_id < 2
AND tc.RecCount <> st.row_count;[/code]If you have any partitioned tables, this would need to be modified. If you do, please reply.
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-10-12 : 09:32:51
You can choose one of these approaches based on your requirement:

--1
EXECUTE sp_MSforeachtable @command1 = N'
INSERT INTO ErrorCOunt(TableName, RecCount, ActualCount)
SELECT TC.TableName, TC.RecCount, AC.ActualCount
FROM (SELECT PARSENAME("?", 1) AS TableName, COUNT(*) AS ActualCount
FROM ?) AS AC
INNER JOIN
TableCounts AS TC
ON TC.TableName = AC.TableName
AND TC.RecCount <> AC.ActualCount;';


--2
INSERT INTO ErrorCOunt(TableName, RecCount, ActualCount)
SELECT TC.TableName, TC.RecCount, PS.actual_count
FROM sys.objects AS T
INNER JOIN
TableCounts AS TC
ON TC.TableName = T.name
CROSS APPLY
(SELECT SUM(CASE WHEN P.index_id < 2
AND A.type = 1
THEN P.rows
ELSE 0
END) AS actual_count
FROM sys.partitions AS P
INNER JOIN
sys.allocation_units AS A
ON P.hobt_id = A.container_id
WHERE P.object_id = T.object_id) AS PS
WHERE T.type = 'U'
AND TC.RecCount <> PS.actual_count;

If it is up to me I would go with the later one.



For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha
Go to Top of Page

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2012-10-12 : 09:52:50
Hi sunitabeck,

We do not have partition Tables. Thank you so much for the help. Problem solved.



Thanks,
Sandesh
Go to Top of Page
   

- Advertisement -