|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
2 databases with same tables..compare rowcountsI have 2 databases with the same table names and want to compare rowcounts
between them. How can I do so ? I guess the output should look like DB1Name TableName Rowcount DB2Name TableName Rowcount Status DB1 T1 100 DB2 T1 100 Good DB1 T2 100 DB2 T2 90 Fail You must create a link between these two servers and then launch something
like this: select count(*) from table1 union select count(*) from server2.dbo.database2.table1 union select count(*) from table2 union select count(*) from server2.dbo.database2.table2 Regards, Show quote "Hassan" wrote: > I have 2 databases with the same table names and want to compare rowcounts > between them. How can I do so ? > > I guess the output should look like > > DB1Name TableName Rowcount DB2Name TableName Rowcount > Status > DB1 T1 100 DB2 T1 > 100 Good > DB1 T2 100 DB2 T2 > 90 Fail > > > > Hi
You could do something like: use tempdb create table counts ( dbname sysname, tablename sysname, countval int ) use mydb1 INSERT INTO tempdb..counts ( dbname, tablename, countval ) exec sp_msforeachtable 'select db_name(),''?'',count(*) from ?' use mydb2 INSERT INTO tempdb..counts ( dbname, tablename, countval ) exec sp_msforeachtable 'select db_name(),''?'',count(*) from ?' Use tempdb SELECT ISNULL(c1.dbname,c2.dbname), ISNULL(c1.tablename,c2.tablename), c1.countval, c2.countval FROM counts c1 FULL JOIN counts c2 ON c1.tablename = c2.tablename WHERE c1.dbname = 'Mydb1' AND c2.dbname = 'Mydb2' John Show quote "Hassan" wrote: > I have 2 databases with the same table names and want to compare rowcounts > between them. How can I do so ? > > I guess the output should look like > > DB1Name TableName Rowcount DB2Name TableName Rowcount > Status > DB1 T1 100 DB2 T1 > 100 Good > DB1 T2 100 DB2 T2 > 90 Fail > > > > I was sleeping, sorry I thought -i don't know why- in different servers.
regards, Show quote "John Bell" wrote: > Hi > > You could do something like: > > use tempdb > create table counts ( dbname sysname, tablename sysname, countval int ) > > use mydb1 > > INSERT INTO tempdb..counts ( dbname, tablename, countval ) > exec sp_msforeachtable 'select db_name(),''?'',count(*) from ?' > > > use mydb2 > INSERT INTO tempdb..counts ( dbname, tablename, countval ) > exec sp_msforeachtable 'select db_name(),''?'',count(*) from ?' > > Use tempdb > > SELECT ISNULL(c1.dbname,c2.dbname), > ISNULL(c1.tablename,c2.tablename), > c1.countval, > c2.countval > FROM counts c1 > FULL JOIN counts c2 ON c1.tablename = c2.tablename > WHERE c1.dbname = 'Mydb1' > AND c2.dbname = 'Mydb2' > > > John > > "Hassan" wrote: > > > I have 2 databases with the same table names and want to compare rowcounts > > between them. How can I do so ? > > > > I guess the output should look like > > > > DB1Name TableName Rowcount DB2Name TableName Rowcount > > Status > > DB1 T1 100 DB2 T1 > > 100 Good > > DB1 T2 100 DB2 T2 > > 90 Fail > > > > > > > > They may be, only the OP knows!!
Show quote "Enric" wrote: > I was sleeping, sorry I thought -i don't know why- in different servers. > > regards, > > "John Bell" wrote: > > > Hi > > > > You could do something like: > > > > use tempdb > > create table counts ( dbname sysname, tablename sysname, countval int ) > > > > use mydb1 > > > > INSERT INTO tempdb..counts ( dbname, tablename, countval ) > > exec sp_msforeachtable 'select db_name(),''?'',count(*) from ?' > > > > > > use mydb2 > > INSERT INTO tempdb..counts ( dbname, tablename, countval ) > > exec sp_msforeachtable 'select db_name(),''?'',count(*) from ?' > > > > Use tempdb > > > > SELECT ISNULL(c1.dbname,c2.dbname), > > ISNULL(c1.tablename,c2.tablename), > > c1.countval, > > c2.countval > > FROM counts c1 > > FULL JOIN counts c2 ON c1.tablename = c2.tablename > > WHERE c1.dbname = 'Mydb1' > > AND c2.dbname = 'Mydb2' > > > > > > John > > > > "Hassan" wrote: > > > > > I have 2 databases with the same table names and want to compare rowcounts > > > between them. How can I do so ? > > > > > > I guess the output should look like > > > > > > DB1Name TableName Rowcount DB2Name TableName Rowcount > > > Status > > > DB1 T1 100 DB2 T1 > > > 100 Good > > > DB1 T2 100 DB2 T2 > > > 90 Fail > > > > > > > > > > > > Dear John,
What did you mean? Sorry, I'm spanish and so many times I am not be able to understand some "sentences". Could you be more explicit man? regards, Show quote "John Bell" wrote: > They may be, only the OP knows!! > > "Enric" wrote: > > > I was sleeping, sorry I thought -i don't know why- in different servers. > > > > regards, > > > > "John Bell" wrote: > > > > > Hi > > > > > > You could do something like: > > > > > > use tempdb > > > create table counts ( dbname sysname, tablename sysname, countval int ) > > > > > > use mydb1 > > > > > > INSERT INTO tempdb..counts ( dbname, tablename, countval ) > > > exec sp_msforeachtable 'select db_name(),''?'',count(*) from ?' > > > > > > > > > use mydb2 > > > INSERT INTO tempdb..counts ( dbname, tablename, countval ) > > > exec sp_msforeachtable 'select db_name(),''?'',count(*) from ?' > > > > > > Use tempdb > > > > > > SELECT ISNULL(c1.dbname,c2.dbname), > > > ISNULL(c1.tablename,c2.tablename), > > > c1.countval, > > > c2.countval > > > FROM counts c1 > > > FULL JOIN counts c2 ON c1.tablename = c2.tablename > > > WHERE c1.dbname = 'Mydb1' > > > AND c2.dbname = 'Mydb2' > > > > > > > > > John > > > > > > "Hassan" wrote: > > > > > > > I have 2 databases with the same table names and want to compare rowcounts > > > > between them. How can I do so ? > > > > > > > > I guess the output should look like > > > > > > > > DB1Name TableName Rowcount DB2Name TableName Rowcount > > > > Status > > > > DB1 T1 100 DB2 T1 > > > > 100 Good > > > > DB1 T2 100 DB2 T2 > > > > 90 Fail > > > > > > > > > > > > > > > > Hi
The OP (original poster) did not specify where the databases were located, it is an assumption that they are both on the same server. John Show quote "John Bell" wrote: > Hi > > You could do something like: > > use tempdb > create table counts ( dbname sysname, tablename sysname, countval int ) > > use mydb1 > > INSERT INTO tempdb..counts ( dbname, tablename, countval ) > exec sp_msforeachtable 'select db_name(),''?'',count(*) from ?' > > > use mydb2 > INSERT INTO tempdb..counts ( dbname, tablename, countval ) > exec sp_msforeachtable 'select db_name(),''?'',count(*) from ?' > > Use tempdb > > SELECT ISNULL(c1.dbname,c2.dbname), > ISNULL(c1.tablename,c2.tablename), > c1.countval, > c2.countval > FROM counts c1 > FULL JOIN counts c2 ON c1.tablename = c2.tablename > WHERE c1.dbname = 'Mydb1' > AND c2.dbname = 'Mydb2' > > > John > > "Hassan" wrote: > > > I have 2 databases with the same table names and want to compare rowcounts > > between them. How can I do so ? > > > > I guess the output should look like > > > > DB1Name TableName Rowcount DB2Name TableName Rowcount > > Status > > DB1 T1 100 DB2 T1 > > 100 Good > > DB1 T2 100 DB2 T2 > > 90 Fail > > > > > > > >  |
|||||||||||||||||||||||
Other interesting topics