Friendly names

IF NOT EXISTS (SELECT 1 FROM syscolumns WHERE id=OBJECT_ID('Contents') AND name='FriendlyName')
BEGIN
 PRINT 'Adding columns FriendlyName and FriendlyNameType to table Contents.'
 ALTER TABLE Contents ADD FriendlyName varchar(150)
 ALTER TABLE Contents ADD FriendlyNameType smallint NOT NULL DEFAULT(0)
END
go

IF NOT EXISTS (SELECT 1 FROM syscolumns WHERE id=OBJECT_ID('ContentVersions') AND name='FriendlyName')
BEGIN
 PRINT 'Adding columns FriendlyName and FriendlyNameType to table ContentVersions.'
 ALTER TABLE ContentVersions ADD FriendlyName varchar(150)
 ALTER TABLE ContentVersions ADD FriendlyNameType smallint NOT NULL DEFAULT(0)
END
go


IF NOT EXISTS (SELECT 1 FROM PhysicalFields WHERE Name='FriendlyName') 
BEGIN
 PRINT 'Adding fields FriendlyName and FriendlyNameType to table PhysicalFields.'
 INSERT INTO PhysicalFields(Name,SystemField,RequiredField,FrontEndField) VALUES('FriendlyName',0,0,0)
 INSERT INTO PhysicalFields(Name,SystemField,RequiredField,FrontEndField) VALUES('FriendlyNameType',0,0,0)
END

IF NOT EXISTS(SELECT 1 FROM sysobjects WHERE id=OBJECT_ID('vwFriendlyNames') AND OBJECTPROPERTY(id,'ISVIEW')=1)
BEGIN
 PRINT 'Adding view vwFriendlyNames.'
 DECLARE @sql varchar(max)
 SET @sql=  'CREATE VIEW vwFriendlyNames WITH SCHEMABINDING'
    +' AS'
    +' SELECT Nr, FriendlyName, FriendlyNameType, FileType FROM dbo.Contents '
    +' WHERE FriendlyName IS NOT NULL'
 EXECUTE (@sql)

 -- the following index will enforce uniquness of non-NULL values 
 SET @sql=  'CREATE UNIQUE CLUSTERED INDEX UN_FRIENDLYNAME ON vwFriendlyNames (FriendlyName)'
 EXECUTE (@sql)
END
go 

EXEC pr_rebuildviews
go