Duplicate Columns

Yesterday I needed to find several tables in a database that contained a certain column name. It is usually easy if you have only a few tables but this database has almost 2000 tables. I needed to use some T-SQL skills.
I consulted with the SQLHelp hash tag on twitter because it has been a while since I did this last and I knew someone there would remember how to do it quicker than I could. Robert Davis, MCM extraordinaire (blog |twitter) had the following line:

SELECT object_name(object_ID) FROM sys.columns WHERE NAME = ‘<column name>’ 

While Bill Fellows (blog|twitter) had a different approach:

SELECT TABLE_NAME FROMinformation_schema.COLUMNS ISC WHERE isc.COLUMN_NAME =‘<column name>’

Both worked very well and here they are here for you and for me when I need to remember how I did it.  Enjoy!

About these ads

About Ed Watson

Florida Gator and Certified SQL Server Enthusiast, Star Wars fanatic and is there anything else...oh yeah PS3! I am a geek and SQL Server DBA who dabbles with VMWare, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on July 31, 2012, in Scripting and tagged , , . Bookmark the permalink. 2 Comments.

  1. In theory, information_schema should be available across all your database platforms as it’s an ANSI standard so I always encourage people to explore the views. http://en.wikipedia.org/wiki/Information_schema

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,357 other followers

%d bloggers like this: