find a field in a database

hello,
i want to search all of the databases on a sql server for a specific field,
e.g. last name, or ssn, what would be the best way to do that?
thanks-- Note, those are doubled single quotes (') around <column name>
EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''<column name>'''
K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://www.truthsolutions.com/

> hello,
> i want to search all of the databases on a sql server for a specific
> field,
> e.g. last name, or ssn, what would be the best way to do that?
> thanks
>|||Let's try that again for those clients reading it as HTML...
-- Note, those are doubled single quotes (') around ** Column Name **
EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''** Column Name **'''
K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://www.truthsolutions.com/|||thanks, i appreciate it, i will try this out. i was expecting there was a
"right click" search trick.
"K. Brian Kelley" wrote:

> -- Note, those are doubled single quotes (') around <column name>
> EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NA
ME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''<column name>'''
> K. Brian Kelley, brian underscore kelley at sqlpass dot org
> http://www.truthsolutions.com/
>
>
>|||underprocessable|||Hi Brian,
i must be doing something wrong, the query runs, but returns blank rows.
Meaning there are many header rows but no data. I don't expect the db
context matters, but I tried master, model, msdb and some user dbs. same
result. the exact query i am running is.
EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''Subject'''
I also tried it with the < > around subject ''<Subject>''' to be thorough,
same result. I know that there is a column named Subject, I just drilled
into a table to find one for a test.
there are not messages returned either.
"K. Brian Kelley" wrote:

> -- Note, those are doubled single quotes (') around <column name>
> EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NA
ME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''<column name>'''
> K. Brian Kelley, brian underscore kelley at sqlpass dot org
> http://www.truthsolutions.com/
>
>
>|||Actually I see that if i set the context db to the db where i know the colum
n
exists, i then see the tables returned for that db...but it returns the same
tables 45 times, the same number of databases on this instance.
"jason" wrote:
[vbcol=seagreen]
> Hi Brian,
> i must be doing something wrong, the query runs, but returns blank rows.
> Meaning there are many header rows but no data. I don't expect the db
> context matters, but I tried master, model, msdb and some user dbs. same
> result. the exact query i am running is.
> EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NA
ME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''Subject'''
> I also tried it with the < > around subject ''<Subject>''' to be thorough,
> same result. I know that there is a column named Subject, I just drilled
> into a table to find one for a test.
> there are not messages returned either.
> "K. Brian Kelley" wrote:
>|||Hello Jason,
You may want to add "Use ?" before the select query:
EXEC sp_MSforeachDB 'use ?; SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA,
TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = "accountid"'
Hope this helps.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||thanks, this is exactly what i needed, i was trying to work the "use ?" into
it, but was going about a different way and it wasn't working.
"Peter Yang [MSFT]" wrote:

> Hello Jason,
> You may want to add "Use ?" before the select query:
> EXEC sp_MSforeachDB 'use ?; SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA,
> TABLE_NAME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = "accountid"'
> Hope this helps.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>