?
Solved

Need catalog/object search for a column name in any database instance on server

Posted on 2011-10-25
6
Medium Priority
?
234 Views
Last Modified: 2012-06-27
Does anyone know how to elevate this to an entire sql server search?

select
 table_name, column_name
 from information_schema.columns
 where
   column_name like '%something%'
0
Comment
Question by:Paula DiTallo
6 Comments
 
LVL 5

Expert Comment

by:Brian Chan
ID: 37028263
Could you tell us what exactly you are trying to achieve? Not sure what are you looking for.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 37028393
Try this query:

sp_msforeachdb 'select
 table_name, column_name
 from information_schema.columns
 where
   column_name like ''%something%'' '
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 37031058
EXEC sp_msforeachdb '
DECLARE @pattern nvarchar(100)
SET @pattern = '%something%'
IF EXISTS(SELECT 1 FROM [?].information_schema.columns WHERE column_name LIKE @pattern)
BEGIN
    SELECT ''?''
    SELECT table_name, column_name
    FROM [?].information_schema.columns
    WHERE column_name LIKE @pattern
END
'
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:Paula DiTallo
ID: 37031147
Brilliantly done!

Here is a variation for those seeking the db instance name on the result set:

EXEC sp_msforeachdb '
DECLARE @pattern nvarchar(100)
SET @pattern = ''%something%''
IF EXISTS(SELECT 1 FROM [?].information_schema.columns WHERE column_name LIKE @pattern)
BEGIN
    SELECT ''?''
    SELECT table_catalog,table_name, column_name
    FROM [?].information_schema.columns
    WHERE column_name LIKE @pattern
END '
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37031166
If you want the db name in the result set, you can do this:

    SELECT ''?'', table_catalog,table_name, column_name
    FROM [?].information_schema.columns
    WHERE column_name LIKE @pattern
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37031172
Actually I've got a stored proc I put in master -- so that it can be used from any db -- that's much more powerful than this basic code.  I can post it if you want it.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question