SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with Logic
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sandesh.ravi
Posting Yak Master

United Kingdom
110 Posts

Posted - 10/12/2012 :  08:23:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/12/2012 :  09:20:03  Show Profile  Reply with Quote
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;
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

Saudi Arabia
260 Posts

Posted - 10/12/2012 :  09:32:51  Show Profile  Reply with Quote
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

United Kingdom
110 Posts

Posted - 10/12/2012 :  09:52:50  Show Profile  Reply with Quote
Hi sunitabeck,

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



Thanks,
Sandesh
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000