sqlsean (6/20/2013)
sorry mark let me correct myself your query works jus fine and yes indeed it is a remarkable job for the information that I have provided. since you have already provided a working query let not bother about sample data.however, when I try to get only records that has "commoncolumns" with value more than 1, then I get that conversion error.
how can I get through this? thank you again.
I only sort of looked at the post...given that we are dealing with common tables I agree that sample data would be rather silly in this case.
Can you simply add a having clause? This works for me at least.
SELECT t1.name AS list1,
t2.name AS list2,
count(distinct c2.name) AS commonColumns
FROM sys.tables t1
CROSS JOIN sys.tables t2
INNER JOIN sys.columns c1 ON c1.object_id = t1.object_id
LEFT OUTER JOIN sys.columns c2 ON c2.object_id = t2.object_id AND c1.name=c2.name
GROUP BY t1.name,t2.name
having count(distinct c2.name) > 1
ORDER BY t1.name,t2.name
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/