Searching for a column name in a SQL database

Here are a couple of different ways to search for a column name within a SQL Server database:

– using information_schema views (preferred method)

SELECT sc.table_name
   FROM information_schema.columns sc
   INNER JOIN information_schema.tables st
     ON sc.table_name = st.table_name
   WHERE st.table_type = ‘base table’
   AND sc.column_name = ‘ColumnToFind’
   ORDER BY sc.table_name

– old school
SELECT name
   FROM sysobjects
   WHERE id IN
   (
      SELECT id
        FROM syscolumns
        WHERE name = ‘ColumnToFind’ )
        AND xtype = ‘U’
        ORDER BY name

This entry was posted on 9. January 2008 at 17:38 and is filed under T-SQL, SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or trackback from your own site.

| Print