-- Create versioning table and view if OBJECT_ID('dbo.DatabaseVersion') is null begin create table dbo.DatabaseVersion (ComponentName varchar(32) not null ,VersionNumber varchar(20) not null ,IsInstalledFully bit not null ,constraint DatabaseVersionPK primary key clustered (ComponentName)) end go if OBJECT_ID('dbo.vDatabaseVersion') is null begin exec ('create view dbo.vDatabaseVersion as select ComponentName ,VersionNumber ,case when IsInstalledFully=0 then ''WARNING: Component not fully installed'' else ''Installation Complete'' end as InstallStatus from dbo.DatabaseVersion ') end go -- Register a version merge into dbo.DatabaseVersion as tgt using ( select 'AdventureWorks2008R2' as ComponentName ,'4.2.001 (2011-10-08)' as VersionNumber ,0 as IsInstalledFully ) as src (ComponentName,VersionNumber,IsInstalledFully) on tgt.ComponentName=src.ComponentName when matched then update set tgt.VersionNumber=src.VersionNumber ,tgt.IsInstalledFully=src.IsInstalledFully when not matched by target then insert (ComponentName ,VersionNumber ,IsInstalledFully) values (src.ComponentName ,src.VersionNumber ,src.IsInstalledFully ); -- Check the installed version select case when InstallStatus <> 'Installation Complete' or VersionNumber <> '4.2.001 (2011-10-08)' then 'Error' else 'Success' end as InstallCheck ,InstallStatus ,VersionNumber ,ComponentName from dbo.vDatabaseVersion where ComponentName='AdventureWorks2008R2'; -- Update the installation merge into dbo.DatabaseVersion as tgt using ( select 'AdventureWorks2008R2' as ComponentName ,'4.2.001 (2011-10-08)' as VersionNumber ,1 as IsInstalledFully ) as src (ComponentName,VersionNumber,IsInstalledFully) on tgt.ComponentName=src.ComponentName when matched then update set tgt.VersionNumber=src.VersionNumber ,tgt.IsInstalledFully=src.IsInstalledFully when not matched by target then insert (ComponentName ,VersionNumber ,IsInstalledFully) values (src.ComponentName ,src.VersionNumber ,src.IsInstalledFully ); -- Check the install again select case when InstallStatus <> 'Installation Complete' or VersionNumber <> '4.2.001 (2011-10-08)' then 'Error' else 'Success' end as InstallCheck ,InstallStatus ,VersionNumber ,ComponentName from dbo.vDatabaseVersion where ComponentName='AdventureWorks2008R2'; -- create a view on a table as an abstraction layer if OBJECT_ID('Person.vPerson') is null begin exec ('create view Person.vPerson as SELECT [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[EmailPromotion] ,[AdditionalContactInfo] ,[Demographics] ,[rowguid] ,[ModifiedDate] from [Person].[Person]'); end go -- Add a column to a now-decoupled table if not exists ( select * from sys.columns where object_id=object_id('Person.Person') and name='IsFemale' ) begin alter table Person.Person ADD IsFemale bit null end go -- Now alter the view, when the application is ready for it. if OBJECT_ID('Person.vPerson') is null begin exec ('alter view Person.vPerson as SELECT [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[EmailPromotion] ,[AdditionalContactInfo] ,[Demographics] ,[rowguid] ,[ModifiedDate] ,[IsFemale] from [Person].[Person]'); end go -- Add a column if it doesn't exist. Comment if it does. if not exists ( select * from sys.columns where object_id=object_id('Person.Person') and name='IsFemale' ) begin alter table Person.Person ADD IsFemale bit null end else begin print 'Person.Person table already has an IsFemale column. Skipping' end go -- Add an index if it doesn't exist. Comment if it does. if not exists ( select * from sys.indexes where object_id=object_id('Production.TransactionHistory') and name='IX_TransactionHistory_TransactionDate' ) begin create index IX_TransactionHistory_TransactionDate on Production.TransactionHistory (TransactionDate) include (Quantity,ActualCost) end else begin print 'Index IX_TransactionHistory_TransactionDate on Production.TransactionHistory already exists. Skipping' end go --Now rename a table out of the way. if OBJECT_ID('dbo.AWBuildVersion') is not null and OBJECT_ID('dbo.DropMe_AwBuildVersion') is null begin exec sp_rename 'AWBuildVersion','DropMe_AwBuildVersion' end go -- should throw an error select * from dbo.AWBuildVersion --Now, rollback our change if OBJECT_ID('dbo.AWBuildVersion') is null and OBJECT_ID('dbo.DropMe_AwBuildVersion') is not null begin exec sp_rename 'DropMe_AwBuildVersion','AWBuildVersion' end go -- should succeed select * from dbo.AWBuildVersion -- Do a quick existence check. Useful whenever a deployment is made. declare @ObjectXML xml set @ObjectXML=N' ' select case when x.ShouldExist=0 and o.object_id is null then 'Victory' when o.type_desc=x.type_desc then 'Victory' else 'Defeat' end as Status ,x.name as ObjectName ,o.name as ExistingObjectName ,x.ShouldExist ,case when o.object_id is null then 1 else 0 end as DoesExist ,x.type_desc as ObjectType ,o.type_desc as ExistingObjectType from ( select o.i.value('@type_desc','nvarchar(128)') as type_desc ,o.i.value('@name','nvarchar(128)') as name ,o.i.value('@should-be-there','bit') as ShouldExist ,OBJECT_ID(o.i.value('@name','nvarchar(128)')) as object_id from @ObjectXML.nodes('objects/object') o(i) ) as x left outer join sys.objects o on x.object_id=o.object_id