Accessing Tables Collection in SQL

[jfrost10]

Hey guys,

I need to be able to cycle through the Tables collection within a stored procedure, and if a column is found in the table I need some code executed.

I'm trying to find anything on the MS help or msdn, but so far no luck. Can anyone help me out?

Thanks,
Jack

 

[tlbroadbent]

You can use the Information_schema views in SQL 7 and 2000.

Select
Table_Schema As Table_Owner,
Table_Name
From Information_Schema.Tables

Select
Table_Name,
Column_Name
From Information_Schema.Columns

You can also query the system tables.

Find user table names.

Select Name From sysobjects where type='u'

Find column names in table 'sales'.

Select Name As ColName
From syscolumns
Where Object_name(id)='sales' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.