|
|
Posted by Erland Sommarskog on 12/04/07 22:57
Amit (complete.amit@gmail.com) writes:
> I am able to retrive the uncommon columns from two similar tables, but
> the problem is i require a query, which can help me to find out which
> columns are added separately. Another query which can say which columns
> are deleted. And the last one can say which column has changed .
It is not clear if you are looking for metadata information or data. But
I assumed that you want to compare the metadata for two tables.
This may get you started:
select a.tblname, a.colname, a.typename, a.max_length,
b.tblname, b.colname, b.typename, b.max_length
from (SELECT tblname = o.name, colname = c.name,
typename = t.name, c.max_length
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.types t ON t.system_type_id = c.system_type_id
AND t.user_type_id <= 255
WHERE o.name = 'tbl1') AS a
full join
(SELECT tblname = o.name, colname = c.name,
typename = t.name, c.max_length
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.types t ON t.system_type_id = c.system_type_id
AND t.user_type_id <= 255
WHERE o.name = 'tbl2') AS b
ON a.colname = b.colname
AND a.typename = b.typename
AND coalesce(a.max_length, 0) = coalesce(b.max_length, 0)
WHERE a.colname IS NULL OR b.colname IS NULL
This query is for SQL 2005. If you are on SQL 2000, the query is similar,
but you need to use sysobjects, syscolumns and systypes, and somewhat
different colunm names. You could look that up in Books Online.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|