text: /*
  Author: Jana Sattainathan
  Original link: https://sqljana.wordpress.com/2017/03/31/sql-server-find-tables-with-similar-table-structure
  */
  
  WITH ColCountsByTable
  AS
  (
        SELECT
              c.TABLE_CATALOG,
              c.TABLE_SCHEMA,
              c.TABLE_NAME,
              COUNT(1) AS Column_Count
        FROM INFORMATION_SCHEMA.COLUMNS c
        /*
        --Plug in the schema and table name to get similar views/tables just for one or more tables
        WHERE
              c.TABLE_SCHEMA = 'TransactionPreProcessing'
              AND c.TABLE_NAME IN ('PreStagingTransaction')
        */
        GROUP BY
              c.TABLE_CATALOG,
              c.TABLE_SCHEMA,
              c.TABLE_NAME
  )
  SELECT
        100 * COUNT(c2.COLUMN_NAME) /*Matching_Column_Count*/ / MIN(ColCountsByTable.Column_Count) /*Column_Count*/ AS Match_Percent,
        c.TABLE_CATALOG,
        c.TABLE_SCHEMA,
        c.TABLE_NAME,
        DENSE_RANK() OVER(ORDER BY c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME) Table_Rank,
        MIN(ColCountsByTable.Column_Count) AS Column_Count,
        c2.TABLE_CATALOG AS Matching_Catalog,
        c2.TABLE_SCHEMA AS Matching_Schema,
        c2.TABLE_NAME AS Matching_Table,
        COUNT(c2.COLUMN_NAME) AS Matching_Column_Count
  FROM INFORMATION_SCHEMA.TABLES t
        INNER JOIN INFORMATION_SCHEMA.COLUMNS c
              ON t.TABLE_CATALOG = c.TABLE_CATALOG
                    AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
                    AND t.TABLE_NAME = c.TABLE_NAME
        INNER JOIN ColCountsByTable
              ON t.TABLE_CATALOG = ColCountsByTable.TABLE_CATALOG
                    AND t.TABLE_SCHEMA = ColCountsByTable.TABLE_SCHEMA
                    AND t.TABLE_NAME = ColCountsByTable.TABLE_NAME
        LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS c2
              ON t.TABLE_NAME != c2.TABLE_NAME
                    AND c.COLUMN_NAME = c2.COLUMN_NAME
  GROUP BY
        c.TABLE_CATALOG,
        c.TABLE_SCHEMA,
        c.TABLE_NAME,
        c2.TABLE_CATALOG,
        c2.TABLE_SCHEMA,
        c2.TABLE_NAME
  --Use the below HAVING clause if you want to restrict results to only matches above a certain percent
  --HAVING
  --    /*Match_Percent*/
  --    (100 * COUNT(c2.COLUMN_NAME) /*Matching_Column_Count*/) / MIN(ColCountsByTable.Column_Count) /*Column_Count*/
  --          >= 50       --Require atleast 50% of the columns to match
  ORDER BY
        Match_Percent DESC