Example in TSQL of how to check if a column exists in a table, and if not add it.

SQL server

Here is an example of a code that checks whether a column of specific type exists in a table, if not, it adds this column to the table. In this example the column type is NVARCHAR, however, it can be anything else.

Notice, all type names exist in table sys.types


DECLARE @table_name nvarchar(MAX) = 'MyTable'
DECLARE @new_column_name nvarchar(MAX) = 'MyColumn'


IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = @new_column_name 
	AND system_type_id = (SELECT system_type_id FROM sys.types WHERE [name]='nvarchar' ) AND Object_ID = Object_ID(@table_name))
BEGIN
	DECLARE @sql nvarchar(MAX) = 'ALTER TABLE ' + @table_name + ' ADD ' + @new_column_name + ' nvarchar(max)'
	exec sp_executesql @sql
END


Post a Comment

Previous Post Next Post