Ask Paula!

...bringing you notes from the field...
posts - 117 , comments - 87 , trackbacks - 0

My Links

News

Copyright © 2008-2014 Paula DiTallo

Tag Cloud

Article Categories

Archives

Post Categories

Image Galleries

.NET Development

Enterprise Integration

Entertainment - Games

Java Development

Mobile/PDA Development

Professional Affiliations

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

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 '

 

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

Feedback

Gravatar

# re: How do I get a listing of all DB instances and table names where a column name occurs in MS SQL Server?

kool script
thnks
8/9/2013 5:24 PM | cheri
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 
 

Powered by: