March 27, 2008 at 3:18 pm
I have a script in which I want to check if the column of a table exists. If it does then do some kind of conversion and remove the column. Problem is, the Parser thinks its invalid SQL when the column does not exist.
IF EXISTS (SELECT 1 FROM sys.columns WHERE [object_id] = object_id('dbo.mytable') AND [name] = 'mycolumn')
BEGIN
INSERT INTO newtable ([ID],oldcolumn) SELECT [ID], mycolumn FROM mytable
....
END
This will bomb if mycolumn doesn't exist, yet who cares because it won't run if it doesn't. The only solution I found is dynamic SQL which I hate dynamic SQL.
March 27, 2008 at 5:38 pm
You will not be able to get away with this one, using your current methodology. You will have to use dynamic sql, unless anyone else has other options.
IF EXISTS (SELECT 1 FROM sys.columns WHERE [object_id] = object_id('dbo.mytable') AND [name] = 'mycolumn')
BEGIN
execute sp_executesql N'INSERT INTO newtable ([ID],oldcolumn) SELECT [ID], mycolumn FROM mytable'
END
March 27, 2008 at 5:43 pm
I forgot to mention that you can also query the information schemas to get quick access to detailed table and column information.
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
March 27, 2008 at 7:29 pm
I thought that was old school and in 2005 you are supposed to use the system views?
I'm bummed about the dynamic SQL though, dang it.
March 27, 2008 at 8:11 pm
I thought that was old school and in 2005
This is somewhat true, system views should be used in most cases. The old school method to query tables you are speaking of is actually querying system tables. System tables have been depreciated in SQL 2005. System tables like syscolumns etc. Information schema has not been depreciated. I sometimes find it a little easier to work with than messing with object ids.
Bottom line is either method will suite your need, I was just throwing this method out there. I find that information schema is a little easier 🙂
if exists(select 1 from information_schema.columns where table_name ='' and column_name ='')
Edit: typing is horrible today
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply