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!
Posted on July 31, 2012, in Scripting and tagged #SQLHelp, SSMS, T-SQL. Bookmark the permalink. 2 Comments.
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
Sent from my iPad