Need catalog/object search for a column name in any database instance on server
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%'
select
table_name, column_name
from information_schema.columns
where
column_name like '%something%'
Could you tell us what exactly you are trying to achieve? Not sure what are you looking for.
Try this query:
sp_msforeachdb 'select
table_name, column_name
from information_schema.columns
where
column_name like ''%something%'' '
sp_msforeachdb 'select
table_name, column_name
from information_schema.columns
where
column_name like ''%something%'' '
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.col
BEGIN
SELECT ''?''
SELECT table_catalog,table_name, column_name
FROM [?].information_schema.col
WHERE column_name LIKE @pattern
END '
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.col
BEGIN
SELECT ''?''
SELECT table_catalog,table_name, column_name
FROM [?].information_schema.col
WHERE column_name LIKE @pattern
END '
If you want the db name in the result set, you can do this:
SELECT ''?'', table_catalog,table_name, column_name
FROM [?].information_schema.col
WHERE column_name LIKE @pattern
SELECT ''?'', table_catalog,table_name, column_name
FROM [?].information_schema.col
WHERE column_name LIKE @pattern
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.