how to make a col in a table after checking its existence, in one stmt
-
hi,
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='abc'
AND COLUMN_NAME IN ('r1'))
BEGIN
EXEC ('ALTER TABLE abc ADD [r1] VARCHAR(2500) NULL')
END
in stmt first, i have checked its existence using "if not exist" then i used "alter table" command to make the col.
What i am searching is, if we have anything in alter table command which checks if columns does not exist, then it should create.
That is a concise way of creating a col.
yours sincerely
- This topic was modified 1 year, 9 months ago by rajemessage 14195.
-
No, there is no syntax to
ALTERa table andADDcolumn only if it doesn't exist, and (presumably)ALTERthe column if it does like, for example,CREATE OR ALTERfor a stored procedure.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Using your code as a model, you can make it a bit shorter...
IF COL_LENGTH('dbo.abc', 'r1') IS NULL
EXEC ('ALTER TABLE abc ADD [r1] VARCHAR(2500) NULL')
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
i think information schema is better because col_length is a function call and finally it would go to sys.objects or all_objects or information schema it self.
- rajemessage 14195 wrote:
i think information schema is better because col_length is a function call and finally it would go to sys.objects or all_objects or information schema it self.
This is what I get using SET STATISTICS TIME,IO ON to measure the COL_LENGTH() method...
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.This is what I get using the INFORMATION_SCHEMA.COLUMNS method...
SQL Server parse and compile time:
CPU time = 8 ms, elapsed time = 8 ms.
Table 'syscolpars'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.So, I'm thinking you're wrong and have it perfectly backwards. 😀 But, it's your choice. 😉
Remember that the functions that are native to T-SQL are not the same as User Defined Functions. Remember also that the things in the INFORMATION_SCHEMA schema are actually views.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
-
-
I can barely read those images, so I'm not sure what it's trying to show anyone. Can you elaborate?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
The time you're seeing in that image is the time it took to actually make the column change. Try making the actual column change with the INFORMATION_SCHEMA method and see how much worse it is.
My measurements weren't including such a column change because the actual column change isn't what I was trying to measure. I was only measuring your claim that an INFORMATION_SCHEMA lookup is better than the COLUMN_LENGTH() function.
Also, you posted a huge amount of white-space in your graphics and that's why no one can read them.
For everyone else, here's what they look like...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
informaton schema was taking 0 cpu and 0 time.
- rajemessage 14195 wrote:
informaton schema was taking 0 cpu and 0 time.
Uh, huh... It did in my example, as well. You should also do more than one test to see why the COLUMN_LENGTH this took more than zer0 milli-seconds. You should also pay attention to the reads I cited. They're not free and should always be considered along with CPU time and duration.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply