September 15, 2006 at 12:44 pm
Is there a way of comparing the tables in 2 data bases (their structure) ?
September 15, 2006 at 3:23 pm
Here's a start. You may need to jump databases to get both into one print out...
SELECT CONVERT( varchar(50), so.name) AS TableName,
CONVERT( varchar(25), sc.name) + '(' +
CASE
WHEN sc.name IN( 'nchar', 'char', 'nvarchar', 'varchar', 'ntext', 'text')
THEN CONVERT( varchar(5), sc.length)
WHEN sc.name IN( 'numeric', 'decimal')
THEN CONVERT( varchar(5), sc.prec) + ', ' + CONVERT( varchar(5), sc.scale)
ELSE CONVERT( varchar(5), sc.length)
END + ')' AS ColumnType,
CASE WHEN sc.isnullable = 1 THEN 'NULL' ELSE 'NOT NULL' END AS Nullable
FROM dbo.sysobjects so
INNER JOIN( SELECT id, name, xtype, length, prec, scale, isnullable
FROM dbo.syscolumns) sc ON( so.id = sc.id)
WHERE so.xtype = 'U'
AND( so.status >= 0)
ORDER BY so.name
I wasn't born stupid - I had to study.
September 15, 2006 at 4:12 pm
You can try this:
exec sp_columns TableName.
Thanks
Sreejith
September 15, 2006 at 5:24 pm
That's probably better for more information. You'll just have to make a #TempTable with the various TableName(s) in it...
I wasn't born stupid - I had to study.
September 15, 2006 at 6:28 pm
Give it a try
This script takes database names and table name as Input parameters and compares column names and data types if they are not matched it will display the unmatched columns
declare @db1 sysname,
@db2 sysname,
@tab_name sysname
set @db1='db1'
set @db2='db2'
set @tab_name='table_name'
select table_catalog,table_name,column_name, data_type,character_maximum_length,is_nullable,column_default,ordinal_position into #temp
from information_schema.columns
where table_catalog=@db1 and table_name=@tab_name
order by ordinal_position
Exec ('Use ' + @db2 +';' + ' select * from #temp a inner join information_schema.columns b on a.table_name=b.table_name and a.ordinal_position=b.ordinal_position and (a.column_name<>b.column_name or a.data_type<> b.data_type )')
drop table #temp
---- This can be refined a lot
September 16, 2006 at 4:45 am
Many thanks to everyone.
September 16, 2006 at 7:43 pm
in case you need to compare more then 2 and not just tables but all databases:
you would like to use 'SQL Compare' tool by
Red-Gate
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply