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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.