searching for a column name

Question

Post reply

searching for a column name

skp_01702

Old Hand

Points: 396
More actions
July 16, 2008 at 9:50 am

#122408

Hi,
I have a database containing hundreds of tables. I need to find out all the tables that contain the column "SSN".
Are there any built in stored procedures that help find out this information.
Appreciate your help.
Thanks,
Sai.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You can query the INFORMATION_SCHEMA.COLUMNS view like this:

[font="Courier New"]SELECT

TABLE_CATALOG,

TABLE_SCHEMA,

TABLE_NAME,

COLUMN_NAME

FROM

INFORMATION_SCHEMA.COLUMNS

WHERE

COLUMN_NAME = 'SSN'[/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

skp_01702 Old Hand Points: 396 More actions · Answer 2

skp_01702

Old Hand

Points: 396

July 16, 2008 at 11:19 am

#843962

Thank you very much. it worked 🙂

Sai.

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 3

you can use this also...

select name as TableName from sysobjects
where id In (select id from syscolumns where name = 'b')

Cheers!

Sandy.

--

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 4

If ever happens that you need to find a specific column name and what table is it from all the databases in the server you can use this..

CREATE TABLE FindColumn (DatabaseName varchar(50),TableSchema varchar(50),TableName varchar(50),ColumnName varchar(50))

INSERT INTO FindColumn

exec sp_msforeachdb ' use ?

SELECT ''?'' as ''?'',TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME = ''TableName''' -- Column Condition

select * from FindColumn order by databaseName,TableName

DROP TABLE FindColumn

"-=Still Learning=-"

Lester Policarpio

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Lester has a good solution. A key point is to remember to use the INFORMATION_SCHEMA views as they are an ANSI standard so should not go away in the future.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question