sql server programming:
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
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, [quoted text, click to view] "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 [quoted text, click to view] "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, [quoted text, click to view] "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!! [quoted text, click to view] "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, [quoted text, click to view] "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 [quoted text, click to view] "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
> >
> >
> >
Don't see what you're looking for? Try a search.
|
