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
>|||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_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
> >
>
>|||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_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
> >
>
>|||Actually I see that if i set the context db to the db where i know the column
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:
> 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_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
> > >
> >
> >
> >|||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/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/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/subscriptions/support/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
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/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/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>