# Tuesday, November 24, 2009
« VS2010, ASP.NET 4.0 and Silverlight 4.0 ... | Main | Visual Studio 2010: Add an extra context... »
Getting the name of the table in which a column with specified name appears

Sometimes you need to find out in which tables a certain column name exists. For example when you want to find out where it’s being used as a foreign key. Here’s a handy script to use in T-SQL.

SELECT OBJECT_NAME(object_id), * FROM sys.columns WHERE name = 'columnname'
This makes use of the OBJECT_NAME function in T-SQL which according to the documentation: Returns the database object name for schema-scoped objects.

Grz, Kris.

T-SQL
Tuesday, November 24, 2009 9:07:02 AM (GMT Standard Time, UTC+00:00)  #    Disclaimer  |  Comments [2]  |  Related posts:
Using a comma delimited string with id's as input parameter for a SQL query
Tuesday, November 24, 2009 11:30:13 AM (GMT Standard Time, UTC+00:00)
Leuke post.

Ik had iets gelijkaardig :-)

SQL server --> http://krisnobels.blogspot.com/2008/02/handy-stuff-when-you-work-with-ms-sql.html

Oracle --> http://krisnobels.blogspot.com/2008/02/handy-stuff-when-you-work-with-oracle.html

enjoy

Kris
Kris Nobels
Tuesday, November 24, 2009 11:33:23 AM (GMT Standard Time, UTC+00:00)
Hey Kris,

I'd like to present the following alternative to your query:

select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'columnname'

This query uses one of the INFORMATION_SCHEMA views and Microsoft recommends using these above the sys tables/views (sys tables are not guaranteed to exist in next versions - not that this matters for on-the-fly queries of course).
As an added benefit you get more information straight from that one view, such as the schema to which the table belongs, and the output is more readable for regular users.

Regards, Valentino.
Valentino Vranken
Comments are closed.