This is really beginning to frustrate me.
IF OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuAuditDetails', N'U') IS NOT NULL
AND EXISTS ( SELECT TOP 1
'X'
FROM
sys.columns
WHERE
name = N'Threshold2011Or2013Stage1'
AND [object_id] = OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuAuditDetails') )
BEGIN;
/* Rename two columns */
EXECUTE sys.sp_rename
'dbo.mtzcus_2014_CoreMenuAuditDetails.Threshold2011Or2013Stage1'
,'dbo.mtzcus_2014_CoreMenuAuditDetails.Threshold2014Stage1';
EXECUTE sys.sp_rename
'dbo.mtzcus_2014_CoreMenuAuditDetails.MetThreshold2011Or2013Stage1'
,'dbo.mtzcus_2014_CoreMenuAuditDetails.MetThreshold2014Stage1';
END;
ELSE
IF OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuAuditDetails', N'U') IS NULL
BEGIN;
EXECUTE(N'
CREATE TABLE dbo.mtzcus_2014_CoreMenuAuditDetails
(
AuditID UNIQUEIDENTIFIER NOT NULL
,MeasureName VARCHAR(100)
,CriteriaCertificationNumber VARCHAR(50)
,ObjectiveNumber VARCHAR(3)
,ObjectiveName VARCHAR(100)
,Denominator DECIMAL
,Numerator DECIMAL
,Percentage DECIMAL(5, 2)
,Threshold2014Stage2 DECIMAL(3, 0)
,MetThreshold2014Stage2 CHAR(1)
,Threshold2014Stage1 DECIMAL(3, 0)
,MetThreshold2014Stage1 CHAR(1)
,ExclusionDescription VARCHAR(100)
,MetExclusion CHAR(1)
,SelfAttestation CHAR(1)
,CONSTRAINT mtzcus_2014_CoreMenuAuditDetailsFK FOREIGN KEY ( AuditID ) REFERENCES dbo.mtzcus_2014_CoreMenuAuditRun ( AuditID )
);');
When the table does NOT exist, the last section of code "executes" (I put a SELECT 'X' before the dynamic statement, which is displayed) but the table is NOT created (I immediately run SELECT TOP 1 * FROM dbo.mtzcus_2014_CoreMenuAuditDetails and get error "Invalid object name 'dbo.mtzcus_2014_CoreMenuAuditDetails'."
Am I missing something obvious here, or maybe something not so obvious?
Thanks,
Jeff