Adding a column to a table in SQL Server is done using the ALTER TABLE tablename ADD command. When adding columns you can specify all the same settings available when creating a table. In the example below, we will create a small sample table, then add columns using the ALTER TABLE command. Multiple columns can be specificied by using the syntax below. First issue an alter table command then add the column name, data type, nullable, and default value. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..#employees’) IS NOT NULL BEGIN DROP TABLE #employees END CREATE TABLE #employees ( EmployeeID int IDENTITY (1,1) CONSTRAINT PK_employee NOT NULL PRIMARY KEY CLUSTERED ,FirstName VARCHAR(50) ) GO INSERT INTO #employees (FirstName) VALUES (‘John’); ALTER TABLE #employees ADD LastName varchar(50) NULL ,SSN varchar(9) NULL CONSTRAINT ssn_unique UNIQUE ,IsTerminated bit NOT NULL DEFAULT 0 ,DateAdded datetime NOT NULL DEFAULT GETDATE() ,DateModified DATETIME NULL DEFAULT GETDATE() ,Comments varchar(255) SPARSE NULL — SQL Server 2008 sparse column SELECT * FROM #employees [/cc] Also note, when you add columns with a default value, the value will only get populated when you specify the column as NOT NULL. Otherwise, NULL values will populate the column initially. However once the column is created, subsequent insert statements will populate the default value. In the above example, DateAdded is initially populated whereas DateModified is only populated after a row is inserted.
Continue reading ...
System generated constraints take on a naming convention of their own. Unfortunately the naming convention in production is rarely the same name in the uncontrolled environments. Using this script, you can dynamically drop all system generated constraints. It doesn’t go as far are re-creating them, however it’s a start. Just change the values of the @TableSchema and TableName variables below: [cc lang=”sql”] DECLARE @TableName varchar(100) DECLARE @TableSchema varchar(100) DECLARE @CountConst int DECLARE @default sysname DECLARE @SQLDropMe varchar(max) DECLARE @ColumnNames varchar(max) SET @TableSchema = ‘dbo’ SET @TableName = ’employees’ ——————————————— Store Existing Column Names SET @ColumnNames = SUBSTRING((SELECT ‘,’ + r.COLUMN_NAME FROM( SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @TableSchema AND TABLE_NAME = @TableName ) r FOR XML PATH(”) ), 2, 8000) ——————————————— /Store Existing Column Names ——————————————— Insert contents into temp table EXEC (‘SELECT * INTO ‘ + @TableSchema + ‘.tmp01_’ + @TableName + ‘ FROM ‘ + @TableSchema + ‘.’ + @TableName) ——————————————— /Insert contents into temp table ——————————————— Drop all the constraints DECLARE @TableConstraints TABLE ( ID int IDENTITY(1,1) ,DefaultConst sysname ) INSERT INTO @TableConstraints ( DefaultConst ) SELECT object_name(default_object_id) FROM sys.columns WHERE object_id = object_id(@TableSchema + ‘.’ + @TableName) AND object_name(default_object_id) IS NOT NULL SET @CountConst = (SELECT MAX(ID) FROM @TableConstraints) WHILE @CountConst > 0 BEGIN SET @Default = (SELECT DefaultConst FROM @TableConstraints WHERE ID = @CountConst) SET @SQLDropMe = ‘ALTER TABLE ‘ + @TableSchema + ‘.’ + @TableName + ‘ DROP CONSTRAINT ‘ + @default SELECT @SQLDropMe –EXEC (@SQLDropMe) SET @CountConst = @CountConst – 1 END […]
Continue reading ...
