Deployment scripts do not include all ALTER SCHEMA commands
bryangm
Posts: 2
I have a database that I am trying to clean up on SQL Server 2008 (not R2). Currently, all tables reside in the dbo schema. Some table names are singular, others are plural.
I created a new schema, crm. I moved all of the tables from dbo to crm and I renamed the singular table names to match the plural table names. When I perform the SQL Compare (version 10.4.8.87) between my development database and production, the script includes the following:
PRINT N'Creating schemata'
GO
CREATE SCHEMA [crm]
AUTHORIZATION [dbo]
GO
...
(removes foreign key constraints, notice it removes them from the plural tables in dbo schema)
PRINT N'Dropping foreign keys from [dbo].[CustomersCommittees]'
GO
ALTER TABLE [dbo].[CustomersCommittees] DROP CONSTRAINT [FK_CustCom_ComID]
ALTER TABLE [dbo].[CustomersCommittees] DROP CONSTRAINT [FK_CustCom_CustID]
GO
...
EXEC sp_rename N'[dbo].[Customer]',N'Customers',N'OBJECT'
ALTER SCHEMA [crm] TRANSFER [dbo].[Customers]
EXEC sp_rename N'[dbo].[CustomerAddress]',N'Addresses',N'OBJECT'
ALTER SCHEMA [crm] TRANSFER [dbo].[Addresses]
EXEC sp_rename N'[dbo].[Committee]',N'Committees',N'OBJECT'
ALTER SCHEMA [crm] TRANSFER [dbo].[Committees]
...
(adds foreign key contraints back, notice how it adds them to the plural tables in the new crm schema, but never included a statement to ALTER SCHEMA)
PRINT N'Adding foreign keys to [crm].[CustomersCommittees]'
GO
ALTER TABLE [crm].[CustomersCommittees] ADD CONSTRAINT [FK_CustCom_ComID] FOREIGN KEY ([CommitteeID]) REFERENCES [reference].[Committees] ([CommitteeID])
ALTER TABLE [crm].[CustomersCommittees] ADD CONSTRAINT [FK_CustCom_CustID] FOREIGN KEY ([CustomerID]) REFERENCES [crm].[Customers] ([CustomerID])
GO
...
As mentioned above, it does not include any of the ALTER SCHEMA ... TRANSFER ... commands for the tables that were already plural and do not need the sp_rename command to be executed.
Has anyone else seen this?
I created a new schema, crm. I moved all of the tables from dbo to crm and I renamed the singular table names to match the plural table names. When I perform the SQL Compare (version 10.4.8.87) between my development database and production, the script includes the following:
PRINT N'Creating schemata'
GO
CREATE SCHEMA [crm]
AUTHORIZATION [dbo]
GO
...
(removes foreign key constraints, notice it removes them from the plural tables in dbo schema)
PRINT N'Dropping foreign keys from [dbo].[CustomersCommittees]'
GO
ALTER TABLE [dbo].[CustomersCommittees] DROP CONSTRAINT [FK_CustCom_ComID]
ALTER TABLE [dbo].[CustomersCommittees] DROP CONSTRAINT [FK_CustCom_CustID]
GO
...
EXEC sp_rename N'[dbo].[Customer]',N'Customers',N'OBJECT'
ALTER SCHEMA [crm] TRANSFER [dbo].[Customers]
EXEC sp_rename N'[dbo].[CustomerAddress]',N'Addresses',N'OBJECT'
ALTER SCHEMA [crm] TRANSFER [dbo].[Addresses]
EXEC sp_rename N'[dbo].[Committee]',N'Committees',N'OBJECT'
ALTER SCHEMA [crm] TRANSFER [dbo].[Committees]
...
(adds foreign key contraints back, notice how it adds them to the plural tables in the new crm schema, but never included a statement to ALTER SCHEMA)
PRINT N'Adding foreign keys to [crm].[CustomersCommittees]'
GO
ALTER TABLE [crm].[CustomersCommittees] ADD CONSTRAINT [FK_CustCom_ComID] FOREIGN KEY ([CommitteeID]) REFERENCES [reference].[Committees] ([CommitteeID])
ALTER TABLE [crm].[CustomersCommittees] ADD CONSTRAINT [FK_CustCom_CustID] FOREIGN KEY ([CustomerID]) REFERENCES [crm].[Customers] ([CustomerID])
GO
...
As mentioned above, it does not include any of the ALTER SCHEMA ... TRANSFER ... commands for the tables that were already plural and do not need the sp_rename command to be executed.
Has anyone else seen this?
Comments