IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'NumberOfChildRooms'
    WHERE SysObjects.Name = 'RoomTypes'
)
BEGIN


	DECLARE @Command VARCHAR(100)
		select @Command = 'ALTER TABLE RoomTypes drop constraint ' + d.name
	 from sys.tables t
	  join    sys.default_constraints d
	   on d.parent_object_id = t.object_id
	  join    sys.columns c
	   on c.object_id = t.object_id
		and c.column_id = d.parent_column_id
	 where t.name = 'RoomTypes'
	  and c.name = 'NumberOfChildRooms'

	  ExeC(@Command)

 
	 EXEC sp_rename 'RoomTypes.NumberOfChildRooms', 'NumberOfSubRooms', 'COLUMN'

	 ALTER TABLE [dbo].[ROOMTYPES] ADD  DEFAULT ((0)) FOR [NumberOfSubRooms]
END



 
	