I've been there! You are at a client site and you know you need certain data elements but aren't certain how many databases on a given server ...or ... which tables the data elements you are interested in might appear.

Here's a single statement when executed from any SMS query connection that will get you there:

EXEC sp_msforeachdb '
DECLARE @pattern nvarchar(100)
SET @pattern = ''%elementName%''
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 '

 

posted on Wednesday, October 26, 2011 3:46 PM
Filed Under [ Practical Answers SQL ]

Comments

Gravatar
# re: How do I get a listing of all DB instances and table names where a column name occurs in MS SQL Server?
posted by cheri
on 8/9/2013 5:24 PM
kool script
thnks

Post A Comment
Title:
Name:
Email:
Comment:
Verification: