adding count of columns in 2 tables

The following code brings
Table1, Table2, <NoOfMachingColumns>
result set.
I would like to add 2 other column data
Table1, Table2, <NoOfMachingColumns>,Table1TotalNoOfColumns,Table2TotalNoOfColumns

Can we have more than one count in such a statement? How can it be done?

thanks
SELECT distinct
case when a.table_schema+'.'+a.table_name > b.table_schema+'.'+b.table_name 
then 
a.table_schema+'.'+a.table_name 
else
b.table_schema+'.'+b.table_name  end as FirstTable,
case when a.table_schema+'.'+a.table_name < b.table_schema+'.'+b.table_name 
then 
a.table_schema+'.'+a.table_name 
else
b.table_schema+'.'+b.table_name  end as SecondTable,
count(*) as NoOfMatchingColumns
FROM
information_schema.columns AS a
inner join 
information_schema.columns AS b
ON 
a.column_name=b.column_name
WHERE NOT
(
a.table_schema=b.table_schema
and 
a.table_name=b.table_name
)
GROUP BY
a.table_schema+'.'+a.table_name, 
b.table_schema+'.'+b.table_name
HAVING count(*) > 1
ORDER BY 3 DESC

Open in new window

LVL 6
anushahannaAsked:
Who is Participating?
 
kenwagersCommented:
Try this - minor change to the GROUP BY.  This runs on my data.
SELECT  -- distinct
case when a.table_schema+'.'+a.table_name > b.table_schema+'.'+b.table_name 
then 
a.table_schema+'.'+a.table_name 
else
b.table_schema+'.'+b.table_name  end as FirstTable,
case when a.table_schema+'.'+a.table_name < b.table_schema+'.'+b.table_name 
then 
a.table_schema+'.'+a.table_name 
else
b.table_schema+'.'+b.table_name  end as SecondTable,
count(*) as NoOfMatchingColumns, 
(select count(*) from information_schema.columns tc
 where tc.table_name = a.table_name ) as Table1TotalNoOfColumns,
(select count(*) from information_schema.columns tc
 where tc.table_name = b.table_name ) as Table2TotalNoOfColumns
FROM
information_schema.columns AS a
inner join 
information_schema.columns AS b
ON 
a.column_name=b.column_name
WHERE NOT
(
a.table_schema=b.table_schema
and 
a.table_name=b.table_name
)
GROUP BY
a.table_schema, a.table_name, 
b.table_schema, b.table_name
HAVING count(*) > 1
ORDER BY 3 DESC

Open in new window

0
 
kenwagersCommented:
Try this.
SELECT distinct
case when a.table_schema+'.'+a.table_name > b.table_schema+'.'+b.table_name 
then 
a.table_schema+'.'+a.table_name 
else
b.table_schema+'.'+b.table_name  end as FirstTable,
case when a.table_schema+'.'+a.table_name < b.table_schema+'.'+b.table_name 
then 
a.table_schema+'.'+a.table_name 
else
b.table_schema+'.'+b.table_name  end as SecondTable,
count(*) as NoOfMatchingColumns, 
(select count(*) from information_schema.table_columns tc
 where tc.table_name = a.table_name ) as Table1TotalNoOfColumns,
(select count(*) from information_schema.table_columns tc
 where tc.table_name = b.table_name ) as Table2TotalNoOfColumns
FROM
information_schema.columns AS a
inner join 
information_schema.columns AS b
ON 
a.column_name=b.column_name
WHERE NOT
(
a.table_schema=b.table_schema
and 
a.table_name=b.table_name
)
GROUP BY
a.table_schema+'.'+a.table_name, 
b.table_schema+'.'+b.table_name
HAVING count(*) > 1
ORDER BY 3 DESC

Open in new window

0
 
anushahannaAuthor Commented:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'information_schema.table_columns'.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
kenwagersCommented:
Sorry - it's 'information_schema.columns', not 'information_schema.table_columns'.

I updated the query.
SELECT distinct
case when a.table_schema+'.'+a.table_name > b.table_schema+'.'+b.table_name 
then 
a.table_schema+'.'+a.table_name 
else
b.table_schema+'.'+b.table_name  end as FirstTable,
case when a.table_schema+'.'+a.table_name < b.table_schema+'.'+b.table_name 
then 
a.table_schema+'.'+a.table_name 
else
b.table_schema+'.'+b.table_name  end as SecondTable,
count(*) as NoOfMatchingColumns, 
(select count(*) from information_schema.columns tc
 where tc.table_name = a.table_name ) as Table1TotalNoOfColumns,
(select count(*) from information_schema.columns tc
 where tc.table_name = b.table_name ) as Table2TotalNoOfColumns
FROM
information_schema.columns AS a
inner join 
information_schema.columns AS b
ON 
a.column_name=b.column_name
WHERE NOT
(
a.table_schema=b.table_schema
and 
a.table_name=b.table_name
)
GROUP BY
a.table_schema+'.'+a.table_name, 
b.table_schema+'.'+b.table_name
HAVING count(*) > 1
ORDER BY 3 DESC

Open in new window

0
 
anushahannaAuthor Commented:
I get the foll:
Column 'information_schema.columns.TABLE_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I add the above column to the group by, I get
The multi-part identifier "information_schema.columns.TABLE_NAME" could not be bound.
0
 
kenwagersCommented:
I have one additional refinement - if you change the query as below, it will ignore all views.  If you run as above, it will include views in the analysis.
SELECT  -- distinct
case when a.table_schema+'.'+a.table_name > b.table_schema+'.'+b.table_name 
then 
a.table_schema+'.'+a.table_name 
else
b.table_schema+'.'+b.table_name  end as FirstTable,
case when a.table_schema+'.'+a.table_name < b.table_schema+'.'+b.table_name 
then 
a.table_schema+'.'+a.table_name 
else
b.table_schema+'.'+b.table_name  end as SecondTable,
count(*) as NoOfMatchingColumns, 
(select count(*) from information_schema.columns tc
 where tc.table_name = a.table_name ) as Table1TotalNoOfColumns,
(select count(*) from information_schema.columns tc
 where tc.table_name = b.table_name ) as Table2TotalNoOfColumns
FROM
information_schema.columns AS a
inner join information_schema.columns AS b ON a.column_name=b.column_name
inner join information_schema.tables ta on ta.table_name = a.table_name
inner join information_schema.tables tb on tb.table_name = b.table_name
WHERE NOT
(
a.table_schema=b.table_schema
and 
a.table_name=b.table_name
)
and ta.table_type = 'BASE TABLE'
and tb.table_type = 'BASE TABLE'
GROUP BY
a.table_schema, a.table_name, 
b.table_schema, b.table_name
HAVING count(*) > 1
ORDER BY 3 DESC

Open in new window

0
 
anushahannaAuthor Commented:
Thanks very much, kenwagers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.