displaying tablenames based on the column name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

displaying tablenames based on the column name

Author

Topic

G.K.K
Starting Member

3 Posts

Posted - 2008-06-05 : 07:04:28

hi,

i have the following scenario,

100 tables in the database,i have to display the table names if the column name given by the user is repeated in the different tables...

e.g.: Let us say,the column name ID IS REPEATED in the 70 tables out of 1000 tables.Then i have to display all the 70 table names..

how to write sql query for it?

i am greateful if any one help me in solving this scenario..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-05 : 07:12:41

[code]select table_name, column_name from
information_schema.columns
where column_name in (select column_name from information_schema.columns group by column_name having count(distinct table_name) > 1)
and column_name = 'ID'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

G.K.K
Starting Member

3 Posts

Posted - 2008-06-06 : 09:26:05

hai harsh,

Showing the following error after trying the query that you posted...

Invalid object name 'information_schema.columns'.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-06 : 09:29:01

Are you using MS SQL Server?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-06 : 09:39:51

Your server is case sensitive. Capitalise the object name

SELECT TABLE_NAME, COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS GROUP BY COLUMN_NAME HAVING COUNT(DISTINCT TABLE_NAME) > 1)
AND COLUMN_NAME = 'ID'

Madhivanan

Failing to plan is Planning to fail

G.K.K
Starting Member

3 Posts

Posted - 2008-06-06 : 09:40:56

Hi harsh,

sorry harsh. It is working..
thank you for your help....

the basic thing i have done wrong when i was trying with my own query is , using small letters for information_schema.columns. but after giving in captial letters,it is working.....

my query is also working...
yours too working...

thanks...

i am learning sql server newly..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-06 : 09:55:03

quote:
Originally posted by madhivanan

Your server is case sensitive. Capitalise the object name

SELECT TABLE_NAME, COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS GROUP BY COLUMN_NAME HAVING COUNT(DISTINCT TABLE_NAME) > 1)
AND COLUMN_NAME = 'ID'

Madhivanan

Failing to plan is Planning to fail

How did you guess that?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-06 : 09:56:05

quote:
Originally posted by harsh_athalye

quote:
Originally posted by madhivanan

Your server is case sensitive. Capitalise the object name

SELECT TABLE_NAME, COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS GROUP BY COLUMN_NAME HAVING COUNT(DISTINCT TABLE_NAME) > 1)
AND COLUMN_NAME = 'ID'

Madhivanan

Failing to plan is Planning to fail

How did you guess that?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Experience

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 09:57:47

quote:
Originally posted by madhivanan

quote:
Originally posted by harsh_athalye

quote:
Originally posted by madhivanan

Your server is case sensitive. Capitalise the object name

SELECT TABLE_NAME, COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS GROUP BY COLUMN_NAME HAVING COUNT(DISTINCT TABLE_NAME) > 1)
AND COLUMN_NAME = 'ID'

Madhivanan

Failing to plan is Planning to fail

How did you guess that?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Experience

Madhivanan

Failing to plan is Planning to fail

Has this something to do with collation settings of server?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-06 : 11:26:20

Yes

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources