Sql Help

[Bambam_Bholey]

Hello vayyas...
I am trying to compare table structure, columns their data type in 4 different schemas. Basic ga trying to verify whether all tables and their columns including data type are same or not. Google lo tools dorikayi kane avi install cheyyakoodadu antunnadu maavadu. So trying with sql. please

[AakPakKarivePak]

LTT

[CheGuevara]

desc table_name iste aa table structure vastadi.............then compare with eevry other schema in the same way.......this is in oracle

[gundugadu]

[quote name='CheGuevara' timestamp='1378997643' post='1304238959']
desc table_name iste aa table structure vastadi.............then compare with eevry other schema in the same way.......this is in oracle
[/quote]agreed

[ChampakDas]

information schema toh you can compare each and every column data type...you cna get the datatype and lenght etc etc from this select * from information_schema.columns where table_name = 'nee tablename'
while loop lo petti go through each and every column...god bless bambam..[img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img]

[Bambam_Bholey]

Nenu SQL Server lo working baa..I know SP_Help table name will give table structure but i am trying to minimize the manual comparision...
Idi try chesaa.. but not sure..is this right or ...
SELECT * FROM T1 WHERE NOT EXISTS
((SELECT * FROM T2 WHERE T2.ID = T1.ID)union
(SELECT * FROM T3 WHERE T3.ID = T1.ID) union
(SELECT * FROM T4 WHERE T4.ID = T1.ID))

[raja8112]

This is for comparing two tabled datatypes...
[font=arial, sans-serif][size=3]
select c1.table_name,c1.COLUMN_NAME,c1.DATA_TYPE,c2.table_name,c2.DATA_TYPE,c1.CHARACTER_OCTET_LENGTH,c2.CHARACTER_OCTET_LENGTH,[/size][/font][font=arial, sans-serif][size=3]
c2.COLUMN_NAME [/size][/font][font=arial, sans-serif][size=3]
from [INFORMATION_SCHEMA].[COLUMNS] c1[/size][/font][font=arial, sans-serif][size=3]
inner join [INFORMATION_SCHEMA].[COLUMNS] c2 on c1.COLUMN_NAME=c2.COLUMN_NAME[/size][/font][font=arial, sans-serif][size=3]
where c1.TABLE_NAME='tbl_import_abc'[/size][/font][font=arial, sans-serif][size=3]
and c2.TABLE_NAME='tbl_import_def'[/size][/font][font=arial, sans-serif][size=3]
and c1.CHARACTER_OCTET_LENGTH <>c2.CHARACTER_OCTET_LENGTH[/size][/font]

[maverick19]

Are you trying to compare table structures across databases or schemas within a database?

if within a database, you can try this sql..change the query according to your needs


select a.owner,a.table_name,a.column_name,a.data_type,b.owner,b.table_name,b.column_name,b.data_type from dba_tab_columns a,
dba_tab_columns b
where a.owner=b.owner
and a.table_name=b.table_name
order by 1,2,3

[mustang302]

[quote name='CheGuevara' timestamp='1378997643' post='1304238959']
desc table_name iste aa table structure vastadi.............then compare with eevry other schema in the same way.......this is in oracle
[/quote]