May 12, 2009 at 10:32 am
I want to verify counts from each table from 2 different servers in similar databases before and after restoring DB from one server to another.
Is ther any script out there that automates such process. Right now I check counts on each table on server A, restore same DB on server B and then check counts again and display all the counts in an excel spread sheet to verify that both are equal.
May 12, 2009 at 11:01 am
If you just want to compare counts, create a Linked Server between them and try this query out (replace server1.database and server2.database):
SELECT object_name(a.object_id), a.object_id, a.[rowcount], b.object_id, b.[rowcount]
FROM (
SELECT p.object_id [object_id], sum(p.rows) [rowcount]
FROM server1.database.sys.partitions p
INNER JOIN server1.database.sys.tables t on t.object_id = p.object_id
GROUP by p.object_id) a
FULL OUTER JOIN (
SELECT p.object_id [object_id], sum(p.rows) [rowcount]
FROM server2.database.sys.partitions p
INNER JOIN server2.database.sys.tables t on t.object_id = p.object_id
GROUP by p.object_id) b on a.object_id = b.object_id
If you want to compare the actual records, check out INTERSECT and EXCEPT in MS BOL
May 12, 2009 at 12:36 pm
I want to report all the counts in a spread sheet with in my backup -restore sql job.
May 12, 2009 at 3:17 pm
I have create a table for each database i restore so that i can save counts with timestamp for audit purpose.
CREATE TABLE [dbo].[SIRS_Counts](
Col1 [int] NULL,
Col2 [int] NULL,
Col3 [int] NULL,
Col4 [int] NULL,
Col5 [int] NULL,
Col6 [int] NULL,
Col7 [int] NULL,
Col8 [int] NULL,
Col9 [int] NULL,
RecordDate [datetime] NULL CONSTRAINT [DF_SIRS_RecordDate] DEFAULT (getdate())
) ON [PRIMARY]
How can i insert the result of this query into a table.
SELECT [TableName] = so.name, [RowCount] = MAX(si.rows)
FROM sysobjects so, sysindexes si
WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
Here is the result for query
TableName RowCount
Col10
Col20
Col3102407505
Col474879698
Col52869649
Col62919697
Col75522121
Col83000146
Col95734579
May 13, 2009 at 5:29 am
You can pivot the results. You have to make sure the table names are correct in the query or it will produce null value.
INSERT [dbo].[SIRS_Counts]
SELECT [Col1],[Col2],
[Col3],[Col4],
[Col5],[Col6],
[Col7],[Col8],
[Col9], GETDATE() [RecordDate]
FROM (
SELECT so.name [TableName], si.rows [RowCount]
FROM sysobjects so
INNER JOIN sysindexes si on si.id = OBJECT_ID(so.name)
WHERE so.xtype = 'U'
) base
PIVOT (MAX([RowCount]) FOR
[TableName] IN ([Col1],[Col2],[Col3],[Col4],
[Col5],[Col6],[Col7],[Col8],[Col9])) piv
May 14, 2009 at 9:19 am
Thank you JAmie
I am able to insert a record in the table but i do have 30 such tables to insert 1 record in each which has to query 30 databases to get result. Do i need to run 30 such queries to insert into each table or is there an easy way. All tables have same structure.
May 19, 2009 at 2:33 pm
How can i run the same query to pull data from 30 such databases where in it should also insert a filed with the databasename from where the counts are pulled.
I just want to run this code from any where instead of running it within the database
SELECT so.name [TableName], si.rows [RowCount]
FROM sysobjects so
INNER JOIN sysindexes si on si.id = OBJECT_ID(so.name)
WHERE so.xtype = 'U'
thanks
May 20, 2009 at 6:57 am
My doubt on using this code
SELECT so.name [TableName], si.rows [RowCount]
FROM sysobjects so
INNER JOIN sysindexes si on si.id = OBJECT_ID(so.name)
WHERE so.xtype = 'U'
The above code will get me row count from the sys tables but i doubt if that is the exact count or approximate or do i need to run update usage before running the counts.
But again i run the counts after restoring a bak up file that means it has already updated usage, am i right?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply