comparer deux tables

Version imprimable

14/04/2009, 01h04
gentelmand

comparer deux tables
salut
je veux comparer la structure de deux table pour savoir s'il ont la même structure ou pas
j'ai essayé ceci mais ça ne me donne que les colonnes communes
Code:

1 2 3 select s1.name, s1.type, s2.name, s2.type from syscolumns s1, syscolumns s2 where s1.id = object_id(T1) and s2.id = object_id(T2) and s1.name=s2.name
merci

Si la comparaison ne porte que sur les colonnes :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT *, 'Table1' AS Source
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
   AND TABLE_NAME = 'Matable1'
EXCEPT
SELECT *, 'Table1' AS Source
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
   AND TABLE_NAME = 'Matable2'
UNION ALL
SELECT *, 'Table2' AS Source
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
   AND TABLE_NAME = 'Matable2'
EXCEPT
SELECT *, 'Table2' AS Source
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
   AND TABLE_NAME = 'Matable1'

A +

Autre possibilité plus "bourrin", mais qui teste aussi les types

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
ALTER proc [dbo].[sp_compareTbl] 
(@Tbl1 sysname,
@Tbl2 sysname)
as
begin
 
create table #comp(Analyse varchar(20),
                Tbl sysname,
                col sysname)
 
/* lignes identiques */
insert into #comp 
select 'Colonnes identiques', '*', s1.name
 from syscolumns s1 inner join syscolumns s2
on s1.name = s2.name and s1.id=object_id(@tbl1) and s2.id=object_id(@tbl2) and s1.type=s2.type
 
/* Types différents */
insert into #comp 
select 'Types différents', '*', s1.name from syscolumns s1 inner join syscolumns s2
on s1.name = s2.name and s1.id=object_id(@tbl1) and s2.id=object_id(@tbl2) and s1.type<>s2.type
 
 
/* Colonnes surnuméraires */
insert into #comp 
select 'Colonne surnuméraire', @Tbl1, name 
from syscolumns
where name not in (select name from syscolumns where id=object_id(@tbl2))
and id=object_id(@tbl1)
 
/* Colonnes manquantes */
insert into #comp 
select 'Colonne surnuméraire', @Tbl2, name 
from syscolumns
where name not in (select name from syscolumns where id=object_id(@tbl1))
and id=object_id(@tbl2)
 
select * from #comp
 
drop table #comp
end

... vous pouvez bien évidemment vous passer de la procédure stockée, de la table temporaire et des divers inserts (et les remplaçant par un UNION ALL unique), mais c'est moins lisible...

14/04/2009, 12h10
jero44

Dans le même ordre d'idées, il existe le projet OPEN DBDiff
http://opendbiff.codeplex.com/ qui permet de comparer 2 structures et de générer les scripts.

J'ai essayé ceci mais ça me donne toutes les colonnes des deux tables, je veux juste savoir si les deux tables ont les memes champs

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
 
SELECT *, 'Table1' AS Source
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
   AND TABLE_NAME = 'Matable1'
EXCEPT
SELECT *, 'Table1' AS Source
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
   AND TABLE_NAME = 'Matable2'
UNION ALL
SELECT *, 'Table2' AS Source
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
   AND TABLE_NAME = 'Matable2'
EXCEPT
SELECT *, 'Table2' AS Source
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
   AND TABLE_NAME = 'Matable1'

merci pour l'aide