April 20, 2007 at 8:02 am
How can I find the existence of a Field in a Table?
I can traverse the sysobjects finding the existence of Table, with SELECT name FROM sysobjects WHERE (xtype = 'U').
Now that I have the Table Name, how do I get the Fields names?
April 20, 2007 at 8:07 am
Use the id for the table and select out of the syscolumns table for that ID.
select
a.name as tablename, b.name as columname from sysobjects a
inner
join syscolumns b
on
a.id = b.id
April 20, 2007 at 6:03 pm
If you use the system tables, then the SQL will NOT be usable with SQL Server 2005.
Instead use the ISO metadata view named INFORMATION_SCHEMA.COLUMNS, which works for 2000 and 2005.
SQL = Scarcely Qualifies as a Language
April 24, 2007 at 6:36 am
Try this:
Substitute the column name you are looking for in the LIKE clause below. If you want to see all the columns in a table comment out the LIKE clause and uncomment the AND just above the LIKE clause.
SELECT
TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
, ORDINAL_POSITION
, COLUMN_DEFAULT
, IS_NULLABLE
, DATA_TYPE
FROM
INFORMATION_SCHEMA.Columns
WHERE
TABLE_SCHEMA = 'dbo'
--AND TABLE_NAME = '<table name>'
AND COLUMN_NAME LIKE '%<column name>%'
ORDER BY
COLUMN_NAME
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply