Geeks With Blogs

News Copyright © 2008-2013 Paula DiTallo
Ask Paula! ...bringing you notes from the field...

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 Practical Answers , SQL | Back to top


Comments on this post: How do I get a listing of all DB instances and table names where a column name occurs in MS SQL Server?

# re: How do I get a listing of all DB instances and table names where a column name occurs in MS SQL Server?
Requesting Gravatar...
kool script
thnks
Left by cheri on Aug 09, 2013 5:24 PM

Your comment:
 (will show your gravatar)
 


Copyright © Paula DiTallo | Powered by: GeeksWithBlogs.net | Join free