Comprehensive Index Information

Hi,

I am writing an in house utility to attempt to compare different
aspects of databases.
I am currently writing the queries to list all of the indexes in the
database (including primary key indexes at present - I may move these
and compare separately at some point).

I would like the following information, in one result set if possible:

Table Name
Index Name
Column Name
Column Position
Unique?

Now on Oracle, this is easily done with the following query:

SELECT IND.TABLE_NAME, IND.INDEX_NAME, IND.COLUMN_NAME,
IND.COLUMN_POSITION, COL.UNIQUENESS
FROM USER_IND_COLUMNS IND,
USER_INDEXES COL
WHEREIND.INDEX_NAME = COL.INDEX_NAME
ORDER BY 1, 2, 3, 4, 5

I have been trying for over an hour now to get the equivalent, and I
really cannot figure it out. If anybody can come up with this then I
would greatly appreciate it!

Many Thanks,

PaulPaul (paulwragg2323@.hotmail.com) writes:

Quote:

Originally Posted by

I am writing an in house utility to attempt to compare different
aspects of databases.

Before you go too far, pay a visit to http://www.red-gate.com and
if SQL Compare meets your needs.

Quote:

Originally Posted by

I am currently writing the queries to list all of the indexes in the
database (including primary key indexes at present - I may move these
and compare separately at some point).
>
I would like the following information, in one result set if possible:
>
Table Name
Index Name
Column Name
Column Position
Unique?

SELECT tablename = t.name, indexname = i.name,
colname = c.name, pos = ic.index_column_id,
indextype = i.type_desc, isunique = i.is_unique
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.index_columns ic ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
JOIN sys.columns c ON t.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE i.is_hypothetical = 0

There are probably more columns should include in the output, but I
levae that as an exercise.

Note: the above works in SQL 2005 only. Next time, please specify which
version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

Thankyou very much for this. Of course, as usual I stupidly forgot to
post the version. Sorry about that. Really I need something that will
work on both SQL Server 2000 and SQL Server 2005.

Thanks for the link - unfortunately this is more of an exercise for
the time being and so we are not willing to spend money on a tool at
present!

Thanks for the help - if you do know something that will work on both
versions that would be good.

Paul|||Paul (paulwragg2323@.hotmail.com) writes:

Quote:

Originally Posted by

Thankyou very much for this. Of course, as usual I stupidly forgot to
post the version. Sorry about that. Really I need something that will
work on both SQL Server 2000 and SQL Server 2005.

Then you need to work against sysobjects, sysindexes, sysindexkeys and
syscolumns. The query will be similar, but you need to filter for
statistics, since in SQL 2000 statistics and indexes live in sysindexes.

These are documented in Books Online, and since this is an exercise for you,
I leave you there. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland.

Complex Query or just me?

i have 2 tables. i want to compare 2 columns from the first table and display the contents in the second table (hard to explain)
example:

[TableA]
id ans1 ans2
=============================
bob abc xyz
joe abc www
mike def www
foo def xyz
bar abc xyz

[TableB]
anscode anstext
=========================================
abc Abc is the first 3 letters
def DEF JAM music
www World Wide Web
xyz XYZ best 3 looking letters in the alphabet

[Result should be]
Column1 Column2 Count
================================================== ========================================
Abc is the first 3 letters XYZ best 3 looking letters in the alphabet 2
Abc is the first 3 letters World Wide Web 1
DEF JAM music World Wide We 1
DEF JAM music XYZ best 3 looking letters in the alphabet 1select distinct
b1.anstext, b2.anstext
from tablea a, tableb b1, tableb b2
where a.ans1 = b1.anscode
and a.ans2 = b2.anscode;|||thnx for the reply
im going to try it out when i get home.

leaving work now

thnx|||actually, instead of DISTINCT, this requires a GROUP BY, because the COUNT is needed

so to modify littlefoot's code slightly...select b1.anstext
, b2.anstext
, count(*) as occurrences
from tablea a
inner
join tableb b1
on a.ans1 = b1.anscode
inner
join tableb b2
on a.ans2 = b2.anscode
group
by b1.anstext
, b2.anstext|||perfect
thats what i needed
thnx

just curious, how much different would it be if i wanted to show every possibly, meaning the of the counts would be 0 if the match never occurs. if it is a major add-on dont worry - not sure what the client really wants. thnx|||Gosh, Rudy ... didn't scroll right enough to see the "count" output column ... Sorry, Vextout.

As of your last post: I'd say you'll need outer join to fetch such records.|||yes, just change INNER to LEFT OUTER in the query i gave you|||thnx again

i started playing with the final query i had that with the inner join trying to display all the possibilites with the counts and realized that the whole query has to be changed, but at least i know what to do now.

thnx again for all the help

Complex Query

Hi,
I want to create a query with which I must compare the production demands with the production results. The production demands can be get by the join of two tables. The production results can be get from an aggregate of 4 tables. The connection of these two objects rely on two fields that exist in both two objects. In order to show all the production demands I must left join the two fields from the demands object to the two fields exist in the aggegate production object. In MsAccess the only way to do it is to create 2 queries one for the demands and one aggregate for the production and in a third query create two left joins from the demands query to the production query and get the right results. How can I do it with MSSQL Server with a query??

Best Regards,
ManolisYou could do this the same way in SQL Server by creating separate VIEWS (equivalent of MS Access Queries) for your two sub-sets and then joining them in a third view or stored procedure.

More efficient, though more difficult to code, would be to write the entire thing as a single TSQL Statement with subqueries.|||need a single sql statement

How can I do it by using two subqueries the second of them to be aggregate and have two left joins from the first to the second??

e.g. How can I left join these two queries with the joinfield1,joinfield2 fields??

1st query
Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value

2nd query
Select sum(agfield1), sum(agfield2),joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4
where agfield5=Value
Group By joinfield1,joinfield2|||SELECT field1,field2,agfield1,agfield2 FROM

(Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value ) as A
INNER JOIN
(Select sum(agfield1) as agfield1, sum(agfield2) as agfield2,joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4
where agfield5=Value
Group By joinfield1,joinfield2) as B

ON A.field1=B.joinfield1
WHERE A.field2=B.joinfield2

That should do it. You may want to switch between field1 and field2 as the inner join condition will perform better with a field that is more specific.

Cheers,
-Kilka|||...
ON A.field1=B.joinfield1
AND A.field2=B.joinfield2|||left joins, the man wanted left joins

why, his question is almost exactly the same as what this other guy wanted in this other thread!! --

http://www.dbforums.com/t1118727.html|||Uncanny coincidence!

comparison of databases

Hi just wondering if there is a tool that can be used to compare 2 databases
on different servers to see if they are the same or have the same data?
Thanks.
Paul G
Software engineer.
www.red-gate.com SQL Compare and SQL DataCompare.
www.apexsql.com SQLDiff will do it all in one.
"Paul" wrote:

> Hi just wondering if there is a tool that can be used to compare 2 databases
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G
> Software engineer.
|||I have used Red-Gate's Data Compare utility 5 times today for this very
purpose...
www.red-gate.com
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
> Hi just wondering if there is a tool that can be used to compare 2
> databases
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G
> Software engineer.
|||thanks for the information. Guess this feature is not built into Enterprise
manager or Query Analyser.
Paul G
Software engineer.
"Kevin3NF" wrote:

> I have used Red-Gate's Data Compare utility 5 times today for this very
> purpose...
> www.red-gate.com
>
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
>
>
|||We've developed Database Comparing Tool on .NET platform
www.databasecompare.com It is still beta version but you can download and
try for free. It allows comparing structure (schema) and data between 2
databases: http://www.databasecompare.com/download.html
Will be nice to have any feedback!
Thanks.
[vbcol=seagreen]
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
> Hi just wondering if there is a tool that can be used to compare 2
> databases
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G
|||ok thanks will give it a try!
Paul G
Software engineer.
"databasecompare" wrote:
[vbcol=seagreen]
> We've developed Database Comparing Tool on .NET platform
> www.databasecompare.com It is still beta version but you can download and
> try for free. It allows comparing structure (schema) and data between 2
> databases: http://www.databasecompare.com/download.html
> Will be nice to have any feedback!
> Thanks.
|||Not in the current version...no idea if it is a feature of 2005....works
well for a $200 product :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8A6FF067-90EC-4371-BFF8-7072DE5437FD@.microsoft.com...[vbcol=seagreen]
> thanks for the information. Guess this feature is not built into
> Enterprise
> manager or Query Analyser.
> --
> Paul G
> Software engineer.
>
> "Kevin3NF" wrote:

comparison of databases

Hi just wondering if there is a tool that can be used to compare 2 databases
on different servers to see if they are the same or have the same data?
Thanks.
--
Paul G
Software engineer.www.red-gate.com SQL Compare and SQL DataCompare.
www.apexsql.com SQLDiff will do it all in one.
"Paul" wrote:
> Hi just wondering if there is a tool that can be used to compare 2 databases
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G
> Software engineer.|||I have used Red-Gate's Data Compare utility 5 times today for this very
purpose...
www.red-gate.com
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
> Hi just wondering if there is a tool that can be used to compare 2
> databases
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G
> Software engineer.|||thanks for the information. Guess this feature is not built into Enterprise
manager or Query Analyser.
--
Paul G
Software engineer.
"Kevin3NF" wrote:
> I have used Red-Gate's Data Compare utility 5 times today for this very
> purpose...
> www.red-gate.com
>
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
> > Hi just wondering if there is a tool that can be used to compare 2
> > databases
> > on different servers to see if they are the same or have the same data?
> > Thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||We've developed Database Comparing Tool on .NET platform
www.databasecompare.com It is still beta version but you can download and
try for free. It allows comparing structure (schema) and data between 2
databases: http://www.databasecompare.com/download.html
Will be nice to have any feedback!
Thanks.
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
> Hi just wondering if there is a tool that can be used to compare 2
> databases
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G
> > Software engineer.
> >
> >
> >|||ok thanks will give it a try!
--
Paul G
Software engineer.
"databasecompare" wrote:
> We've developed Database Comparing Tool on .NET platform
> www.databasecompare.com It is still beta version but you can download and
> try for free. It allows comparing structure (schema) and data between 2
> databases: http://www.databasecompare.com/download.html
> Will be nice to have any feedback!
> Thanks.
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
> > Hi just wondering if there is a tool that can be used to compare 2
> > databases
> > on different servers to see if they are the same or have the same data?
> > Thanks.
> > --
> > Paul G
> > > Software engineer.
> > >
> > >
> > >|||Not in the current version...no idea if it is a feature of 2005....works
well for a $200 product :-)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8A6FF067-90EC-4371-BFF8-7072DE5437FD@.microsoft.com...
> thanks for the information. Guess this feature is not built into
> Enterprise
> manager or Query Analyser.
> --
> Paul G
> Software engineer.
>
> "Kevin3NF" wrote:
>> I have used Red-Gate's Data Compare utility 5 times today for this very
>> purpose...
>> www.red-gate.com
>>
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
>> www.experts-exchange.com - experts compete for points to answer your
>> questions
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
>> > Hi just wondering if there is a tool that can be used to compare 2
>> > databases
>> > on different servers to see if they are the same or have the same data?
>> > Thanks.
>> > --
>> > Paul G
>> > Software engineer.
>>

comparison of databases

Hi just wondering if there is a tool that can be used to compare 2 databases
on different servers to see if they are the same or have the same data?
Thanks.
--
Paul G
Software engineer.www.red-gate.com SQL Compare and SQL DataCompare.
www.apexsql.com SQLDiff will do it all in one.
"Paul" wrote:

> Hi just wondering if there is a tool that can be used to compare 2 databas
es
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G
> Software engineer.|||I have used Red-Gate's Data Compare utility 5 times today for this very
purpose...
www.red-gate.com
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
> Hi just wondering if there is a tool that can be used to compare 2
> databases
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G
> Software engineer.|||thanks for the information. Guess this feature is not built into Enterprise
manager or Query Analyser.
--
Paul G
Software engineer.
"Kevin3NF" wrote:

> I have used Red-Gate's Data Compare utility 5 times today for this very
> purpose...
> www.red-gate.com
>
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
>
>|||We've developed Database Comparing Tool on .NET platform
www.databasecompare.com It is still beta version but you can download and
try for free. It allows comparing structure (schema) and data between 2
databases: http://www.databasecompare.com/download.html
Will be nice to have any feedback!
Thanks.
[vbcol=seagreen]
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
> Hi just wondering if there is a tool that can be used to compare 2
> databases
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G|||ok thanks will give it a try!
--
Paul G
Software engineer.
"databasecompare" wrote:
[vbcol=seagreen]
> We've developed Database Comparing Tool on .NET platform
> www.databasecompare.com It is still beta version but you can download and
> try for free. It allows comparing structure (schema) and data between 2
> databases: http://www.databasecompare.com/download.html
> Will be nice to have any feedback!
> Thanks.
>|||Not in the current version...no idea if it is a feature of 2005....works
well for a $200 product :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8A6FF067-90EC-4371-BFF8-7072DE5437FD@.microsoft.com...[vbcol=seagreen]
> thanks for the information. Guess this feature is not built into
> Enterprise
> manager or Query Analyser.
> --
> Paul G
> Software engineer.
>
> "Kevin3NF" wrote:
>

comparison between columns

How to compare the values between columns? I know there's a function "Greatest" in Oracle, do MS SQL have function similar to that?

And I want to create a view from a table, and one column 'll be selected from the 1 of the 4 related columns in table, say, If ColA is not null then the value is from ColA, if not , the value of the column in the view 'll be from ColB. How can i do this? Do I have to use store procedure?
( I know that's the result of poor normalization, but ... I'm not allowed to change th schema)sql/server apparently does not have anything as useful as the GREATEST function

unless someone else has a better idea, you'll have to use the CASE structure to build the equivalent --

select
(case
when a>=b and a>=c and a>=d then a
when b>=a and b>=c and b>=d then b
when c>=a and c>=b and c>=d then c
when d>=a and d>=b and d>=c then d
else a
end) as greatest

as for your other question, that's easy --

select coalesce(colA, colB) as ifAnullthenB

rudy
http://rudy.ca/|||What happens if you have Nulls?

Code:
---------------------------------------
create table #Tmp(a int, b int, c int, d int)

insert into #Tmp values(1, Null, Null, Null)
insert into #Tmp values(Null, 2, Null, Null)
insert into #Tmp values(Null, Null, 3, Null)
insert into #Tmp values(Null, Null, Null, 4)

insert into #Tmp values(5, 4, 3, 2)
insert into #Tmp values(3, 6, 4, 5)
insert into #Tmp values(4, 5, 7, 6)
insert into #Tmp values(5, 6, 7, 8)

select case
when a >= b and a >= c and a >= d then a
when b >= a and b >= c and b >= d then b
when c >= a and c >= b and c >= d then c
when d >= a and d >= b and d >= c then d
else a
end as greatest
from #Tmp

select case
when (b >= a or a is null) and (b >= c or c is null) and (b >= d or d is null) then b
when (c >= a or a is null) and (c >= b or b is null) and (c >= d or d is null) then c
when (d >= a or a is null) and (d >= b or b is null) and (d >= c or c is null) then d
else a
end as greatest
from #Tmp
---------------------------------------

Results:
---------------------------------------
greatest
----
1
NULL
NULL
NULL
5
6
7
8

greatest
----
1
2
3
4
5
6
7
8
---------------------------------------

Which is correct?

Comparing two tables in two different databases

I want to compare the records in a table on my live database server with the same table that is my test database server. How can I do this?

Note that both databases are mirrors of each other but contain slightly different data and are on different servers.

There a couple of tools on the market like RedGate Data Compare.

The SQL Server 2005 program directory also contains a commandline utility called tablediff.exe which does just what you want.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Comparing two tables and spitting out an XLS

Hi,

I'm trying to figure out a way to compare two tables, table one has more
entries than table two, I want SQL to compare table one to table two and
spit out and XLS of the enries that exist in table one, but not in table
two.

So far I can't even get my query right...heh

select * from table1 a
left join tabl2 b on a.column=b.column
where a.column exists not b.column

am I missing an "in" in the select portion on my query?

thanks alot for any help.Your name (fake.email@.address.com) writes:

Quote:

Originally Posted by

I'm trying to figure out a way to compare two tables, table one has more
entries than table two, I want SQL to compare table one to table two and
spit out and XLS of the enries that exist in table one, but not in table
two.

SELECT a.*
FROM a
WHERE NOT EXISTS (SELECT *
FROM b
WHERE a.keycol = b.keycol)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Comparing two tables

IS there an easy way to compare two tables in sqlserver? I only need to display items that are different between table a and b.
thanks in advance for your help!One quick way is to use EM to script each table then compare both files.|||I was referring to the data in the tables. Sorry for the confusion.|||If you know perl, you should do it with a script like that:

#!C:/Perl/bin/perl.exe
# SYNTAXE : perl script.pl server db user pwd

use DBI;

my @.param = @.ARGV;
my $server = $param[0];
my $database = $param[1];
my $user = $param[2];
my $password = $param[3];

my $dsn = "Driver={SQL Server};Server=$server;Database=$database;Uid=$use r;Pwd=$password;" ;
my $dbh = DBI->connect("dbi:ADO:$dsn") or die "Impossible connection: $DBI::errstr";

my $sth1 = $dbh->prepare( q{
SELECT * FROM table1
}) or die "Can't prepare statement: $DBI::errstr";

my $sth2 = $dbh->prepare( q{
SELECT * FROM table2
}) or die "Can't prepare statement: $DBI::errstr";

my $rc1 = $sth1->execute
or die "Can't execute statement: $DBI::errstr";
my $rc2 = $sth1->execute
or die "Can't execute statement: $DBI::errstr";

while ( @.row1 = $sth1->fetchrow_array and @.row2 = $sth2->fetchrow_array )
{
# do your tests here
}

$rc = $dbh->disconnect;|||Sorry, I don't know pearl. Is there anything on sqlserver?|||Don't see what, except a sql script that you have to write (like the one in perl) :)|||IS there an easy way to compare two tables in sqlserver? I only need to display items that are different between table a and b.

thanks in advance for your help!What exactly do you mean by "items"? Do your tables have Primary Keys (if not, this problems gets incredibly ugly)? What exactly do you want displayed when a difference is found?

The best bet might be to compose a sample set of data that contains a pair of 5 row tables with one row "missing" in each of them, and the output that you'd like to get from comparing them.

-PatP|||You mean like

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1) PRIMARY KEY, Col2 char(1))
CREATE TABLE myTable00(Col1 int IDENTITY(1,1) PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c' UNION ALL
SELECT 'd'

INSERT INTO myTable00(Col2)
SELECT 'a' UNION ALL
SELECT 'x' UNION ALL
SELECT 'c' UNION ALL
SELECT 'z'
GO

SELECT a.Col1, a.Col2, b.Col2
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
WHERE a.Col2 <> b.Col2
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO|||Try this:
http://www.sqlservercentral.com/scripts/contributions/596.asp|||Ummm ... isn't it much easier than that? How about

SELECT value1, value2, value3, value4, value5
FROM table1
WHERE value1 NOT IN (SELECT value1 FROM table2)

Comparing two SSIS Packages?

I'd like to know how people out there are comparing ("diffing") their DTSX files.

Using XML file compare doesn't seem to work, because the package XML appears to be arbitrarily ordered and reordered by the designer in Visual Studio.

ApexSQL is supposedly planning to have a tool available in Q3, but even their product page does not seem too hopeful: "Compare and Document SSIS Packages (may be released with ApexSQL Doc and ApexSQL Diff in Q3)"

I remember seeing a CodePlex project that was hoping to address this and other issues, but it was in its infancy, and I can't seem to locate it today.

I'm thinking of starting to write my own package compare tool that will work with the SSIS .NET API, but this seems like it's going to be a ridiculous amount of work and an exercise in pain, and it seems unlikely that I will make the time to target anything more than the specific portions of the package model that I really need.

So if anyone out there has words of wisdom to share, I'd love to hear it. And if (dare I get my hopes up?) have a URL to share, I'll buy you a beer at TechEd in Orlando week after next.

Thanks in advance!

No words of wisdom to share, but I agree on the need for this kind of tool. We've been looking into rolling our own tools as well, but creating anything of general applicability would be a major project.|||

I don't know if there is a clever way of comparing packages. Perhaps you should open a suggestion in SQL Server connect site

http://connect.microsoft.com/site/sitehome.aspx?SiteID=68

[Microsoft follow-up]

|||

Thanks for the feedback, Rich and Rafael. I'll definitely post this as a suggestion in Connect, but...

Is no one out there (or more specifically, no one here on the SSIS forums) doing anything to compare or diff SSIS packages? I honestly wasn't expecting anyone to say "oh yeah, I use XXX tool, it does a great job" but I was expecting something...

To take this thread in a somewhat different direction, if I were to start a CodePlex project with the aim of developing a package diff utility based on the SSIS object model, would anyone else be interested in contributing? I'll bet I'm not the only SSIS ETL guy out there with a C# background...

|||

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

|||

jwelch wrote:

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

Thank you!

This was the CodePlex project I mentioned in my original post - I stumbled across it once, but could not find it again when I started this thread here.

|||

jwelch wrote:

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

So... what are you using to compare packages, today?

|||

I try not to

Usually I use a text difference tool and deal with the headaches of elements being in different order.

|||

jwelch wrote:

I try not to

Usually I use a text difference tool and deal with the headaches of elements being in different order.

Well, it's good to know that great minds do indeed think alike.

|||

Can I just add my voice to the need for such a tool. I still use DTS Compare for our 2000 installations and something similar for SSIS would be of great use.