Add a column to a table unless it already exists


To add a column to a SQL Server database table, checking first that the column does not already exist:
if not exists (select * from syscolumns
  where id=object_id('<table_name>') and name='<column_name>')
    alter table <table_name> add <column_name> <column_definition>
where: <table_name> is the name of the table, <column_name> is the name of the column and <column_definition> is the definition of the column used when creating the column.
For example:
if not exists (select * from syscolumns
  where id=object_id('Employees') and name='MaidenName')
    alter table Employees add MaidenName varchar(64) NULL
go

Comments

Post a Comment

Popular posts from this blog

Who Deleted and Drop the record in table

CREATE DATABASE ReadingDBLog; GO -- Create tables. USE ReadingDBLog; GO CREATE TABLE [Location] (     [Sr.No] INT IDENTITY,     [Date] DATETIME DEFAULT GETDATE (),     [City] CHAR (25) DEFAULT 'Bangalore');           select * from [Location]       INSERT INTO Location DEFAULT VALUES ; GO 100 DELETE Location WHERE [Sr.No]=10 GO SELECT * FROM Location WHERE [Sr.No]=10 GO SELECT     [Transaction ID],     Operation,     Context,     AllocUnitName   FROM     fn_dblog(NULL, NULL) WHERE     Operation = 'LOP_DELETE_ROWS'       SELECT     Operation,     [Transaction ID],     [Begin Time],     [Transaction Name],     [Transaction SID] FROM     fn_dblog(NULL, NULL) WHERE     [Transaction ID] = '0000:000002ef' AND     [Operat...
Read more

Generate Password

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_GeneratePassword](@Length int, @PasswordTemp varchar(100) output) AS BEGIN DECLARE @RandomID varchar(32) DECLARE @counter smallint DECLARE @RandomNumber float DECLARE @RandomNumberInt tinyint DECLARE @CurrentCharacter varchar(1) DECLARE @ValidCharacters varchar(255) SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789' DECLARE @ValidCharactersLength int SET @ValidCharactersLength = len(@ValidCharacters) SET @CurrentCharacter = '' SET @RandomNumber = 0 SET @RandomNumberInt = 0 SET @RandomID = '' SET NOCOUNT ON SET @counter = 1 WHILE @counter < (@Length + 1) BEGIN         SET @RandomNumber = Rand()         SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))         SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1) ...
Read more

Key Board Shortcuts

Image
100 KEYBOARD SHORTCUTS CTRL + C (Copy) CTRL + X (Cut) CTRL + V (Paste) CTRL + Z (Undo) DELETE (Delete) SHIFT + DELETE (Delete the selected item permanently without placing the item in the Recycle Bin) CTRL while dragging an item (Copy the selected item) CTRL + SHIFT while dragging an item (Create a shortcut to the selected item) F2 KEY (Rename the selected item) CTRL + RIGHT ARROW (Move the insertion point to the beginning of the next word) CTRL + LEFT ARROW (Move the insertion point to the beginning of the previous word) CTRL + DOWN ARROW (Move the insertion point to the beginning of the next paragraph) CTRL + UP ARROW (Move the insertion point to the beginning of the previous paragraph) CTRL + SHIFT with any of the arrow keys (Highlight a block of text) SHIFT with any of the arrow keys (Select more than one item in a window or on the desktop, or select text in a document) CTRL + A (Select all) F3 KEY (Search for a file or a folder) ALT...
Read more