--Get matched column names between two tables in the same database

CREATE PROCEDURE GetMatchedColumns_TwoTable

@Table1 Varchar (50), @Table2 Varchar(50)

AS

BEGIN

SET NOCOUNT ON

SELECT * FROM

(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME=@Table1) t1

INNER JOIN

(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME=@Table2) t2

ON t1.TABLE_NAME !=t2.TABLE_NAME

AND t1.COLUMN_NAME=t2.COLUMN_NAME

AND t1.DATA_TYPE=t2.DATA_TYPE

END

--Get matched columns between one table and all other tables in the same database

CREATE PROCEDURE GetMatchedColumns_AllTable

@Table1 Varchar(50)

AS

BEGIN

SET NOCOUNT ON

SELECT * FROM

(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME=@Table1) t1

CROSS APPLY

(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS t2

WHERE t1.TABLE_NAME !=t2.TABLE_NAME

AND t1.COLUMN_NAME=t2.COLUMN_NAME

AND t1.DATA_TYPE=t2.DATA_TYPE) t3

END