How to check if a field already exists in a table
-
Hi, I need to check if a field exists in a certain table (in an SQL database), and if not then add that field to the table. For Instance I have Table "A" that should contain field "a". So I want to check if field a is already defined for the table, and if not then add it. How can I d o this? Is there an SQL statement to check if a field exists? Thanks
-
Hi, I need to check if a field exists in a certain table (in an SQL database), and if not then add that field to the table. For Instance I have Table "A" that should contain field "a". So I want to check if field a is already defined for the table, and if not then add it. How can I d o this? Is there an SQL statement to check if a field exists? Thanks
SWDevil wrote:
Is there an SQL statement to check if a field exists?
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'MyColumnName') BEGIN ALTER TABLE .... END
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... * Reading: Developer Day 5 Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
SWDevil wrote:
Is there an SQL statement to check if a field exists?
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'MyColumnName') BEGIN ALTER TABLE .... END
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... * Reading: Developer Day 5 Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
If I need to add more than one fields can I write than one ALTER statements: IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'MyColumnName') BEGIN ALTER TABLE .... ALTER TABLE ... ALTER TABLE ... ALTER TABLE ... END
-
If I need to add more than one fields can I write than one ALTER statements: IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'MyColumnName') BEGIN ALTER TABLE .... ALTER TABLE ... ALTER TABLE ... ALTER TABLE ... END
You can write as many ALTER TABLE statements as you like. Or you can put it all in one ALTER TABLE statement. It is up to you.
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... * Reading: Developer Day 5 Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website