Determining the column name using this sp_MSforeachtable

• Maria Carmen Vilbar

  Ten Centuries

  Points: 1037

  More actions

  April 24, 2006 at 11:58 pm

  #168655

  Hi,

  How will I use the sp_MSforeachtable in determing the name of column for each table in a certain database?

  

  Thanks.

  Maricar

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  April 25, 2006 at 11:02 am

  #634119

  This query should get you started:

  SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME

    FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN

         INFORMATION_SCHEMA.TABLES t ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME 

   WHERE t.TABLE_TYPE = 'Base Table'

   ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.ORDINAL_POSITION

  You can probably get what you're looking for without digging in to undocumented system stored procedures.

  -Eddie

  Eddie Wuerch
  MCM: SQL