CODEWITHSUNDEEP
sql
MusicLovingIndianGirl
MusicLovingIndianGirl

Reputation: 5947

How do I find whether a column exists is any other table

I would like to know how we can find a particular column exists in other tables in SQL. I found the existence in Stored Procedures using the following query

SELECT *,ROUTINE_NAME, ROUTINE_DEFINITION,ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%MyColumnName%'

How do I check the same for existence in tables?

Upvotes: 0

Views: 82

Answers (2)

BYRAKUR SURESH BABU
BYRAKUR SURESH BABU

Reputation: 289

if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='' and ') begin print 'Column you have specified is exists' end else begin print 'Column does not exists' end

"http://confitech.blogspot.in/2014/05/how-to-find-whether-specific-column.html"

Upvotes: -1

Preet Sangha
Preet Sangha

Reputation: 65476

The information schema views are really useful. I use them for SQL generation all the time.

This query looks at the COLUMNS view and it automatically contains the TABLE and SCHEMA for easy access.

SELECT 
   TABLE_SCHEMA,
   TABLE_NAME,

FROM
   INFORMATION_SCHEMA.COLUMNS
WHERE
   COLUMN_NAME LIKE '%MyColumnName%'

Of course if you're precise then you won't use LIKE rather a COLUMN_NAME = 'MyColumnName' maybe much better.

Upvotes: 2

Related Questions