July 31, 2013 at 10:58 pm
Comments posted to this topic are about the item Schema-Owned Tables and Generated DROP Scripts (SQL Spackl
August 1, 2013 at 12:22 am
There is a good rule - always use the fully qualified name. Especially when databases have a lot of schema and without any guarantee don't to have the similar object name in different schema.
And just a little add
against
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'DF_Product_Cost' AND type = 'D') and etc.
you can use
IF OBJECT_ID(N'[test].[DF_Product_Cost]',N'D') IS NOT NULL
August 1, 2013 at 5:40 am
dmitry.maletin 89875 (8/1/2013)
There is a good rule - always use the fully qualified name.
Yes, it is a good rule. It's a shame Microsoft doesn't abide by it. @=)
August 1, 2013 at 7:38 am
Really good "Spackle", Brandie. Our shop enforces the 2 part naming convention for everything and we do 100% peer reviews before anything leaves Dev so we know to check for this even on auto-generated code but I know a lot of shops that don't. Since you'd expect that MS would generate bullet-proof code, this takes a lot of people by surprise because they don't check it before they promote it.
MS also realizes that not all of the code the system generates is necessarily bullet-proof. That's why they issue a warning to check the scripts they gen for table modifications done in the design window of SSMS.
Thanks for picking up the putty-knife on a good subject for the "Spackle" series!
--Jeff Moden
Change is inevitable... Change for the better is not.
August 1, 2013 at 10:55 am
+1. I also try to add these spackles to my SSMS templates.....
August 2, 2013 at 7:46 am
REALLY crammed with work this morning so didn't have time to set up a test or investigate, but is it possible to have 2+ tables with the same name belonging to different schemas each with a constraint named the same?
Test.MyTable(MyDefaultConstraint)
QA.MyTable(MyDefaultConstraint)
Dev.MyTable(MyDefaultConstraint)
And if that is possible (which one would think it could be if all 3 parts are considered in "uniqueness" of a constraint object) I think all we would have a problem with some if not all of the article code.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 2, 2013 at 9:44 am
TheSQLGuru (8/2/2013)
REALLY crammed with work this morning so didn't have time to set up a test or investigate, but is it possible to have 2+ tables with the same name belonging to different schemas each with a constraint named the same?
I'll test as soon as I've finished with my month end emergencies.
And if that is possible (which one would think it could be if all 3 parts are considered in "uniqueness" of a constraint object) I think all we would have a problem with some if not all of the article code.
Are you saying my code won't work at all in these circumstances? Or do you mean something else by "some if not all of the article code"?
August 3, 2013 at 10:08 am
Kevin,
Your comment led to some interesting conclusions. Here's what I tested in SQL 2k8 R2 and SQL 2k5.
/** Setup for multiple schemas (Test and QC) **/
/****** This is a new table. Drop the table if it already exists. ******/
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[Test].[Product]') AND type in (N'U'))
DROP TABLE [Test].[Product]
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[QC].[Product]') AND type in (N'U'))
DROP TABLE [QC].[Product]
GO
/****** This is a new schema. Drop the schema if it already exists. ******/
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Test')
DROP SCHEMA [Test];
GO
CREATE SCHEMA Test AUTHORIZATION dbo;
GO
CREATE TABLE Test.Product (
ProductID INT IDENTITY(1,1) NOT NULL,
ProductName VARCHAR(30) NOT NULL,
ProductType VARCHAR(50) NULL);
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'QC')
DROP SCHEMA [QC];
GO
CREATE SCHEMA QC AUTHORIZATION dbo;
GO
CREATE TABLE QC.Product (
ProductID INT IDENTITY(1,1) NOT NULL,
ProductName VARCHAR(30) NOT NULL,
ProductType VARCHAR(50) NULL);
GO
ALTER TABLE Test.Product
ADD Cost MONEY NOT NULL CONSTRAINT DF_Product_Cost DEFAULT 0.00;
GO
ALTER TABLE QC.Product
ADD Cost MONEY NOT NULL CONSTRAINT DF_Product_Cost DEFAULT 0.00;
GO
SELECT * FROM Sys.objects
WHERE TYPE = 'D'
ORDER BY Name;
I tested all three methods. Starting with Method 2, which adds the schema into the OBJECT_ID() function. It worked fine in both environments, no issues.
--Method 2 : OBJECT_ID() schema add
/* Verify the constraints still exist */
SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[QC].[DF_Product_Cost]')
AND type = 'D';
SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Test].[DF_Product_Cost]')
AND type = 'D';
/* DROP the constraints */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Test].[DF_Product_Cost]')
AND type = 'D')
BEGIN
ALTER TABLE [Test].[Product] DROP CONSTRAINT [DF_Product_Cost]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[QC].[DF_Product_Cost]')
AND type = 'D')
BEGIN
ALTER TABLE [QC].[Product] DROP CONSTRAINT [DF_Product_Cost]
END
GO
/* Verify if the constraints exist or not */
SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[QC].[DF_Product_Cost]')
AND type = 'D';
SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Test].[DF_Product_Cost]')
AND type = 'D';
/* DROP the columns as the final test */
ALTER TABLE Test.Product
DROP COLUMN Cost;
GO
ALTER TABLE QC.Product
DROP COLUMN Cost;
GO
Method 1 and 3 had some issues, but Method 3 (my code) was easily fixed by adding one column into the WHERE clause (the Table_Schema column) as below:
--Method 3, using Information_Schema tables
/* Add back the columns and constraints */
ALTER TABLE Test.Product
ADD Cost MONEY NOT NULL CONSTRAINT DF_Product_Cost DEFAULT 0.00;
GO
ALTER TABLE QC.Product
ADD Cost MONEY NOT NULL CONSTRAINT DF_Product_Cost DEFAULT 0.00;
GO
/* Verify the constraints still exist */
SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[QC].[DF_Product_Cost]')
AND type = 'D';
SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Test].[DF_Product_Cost]')
AND type = 'D';
/* DROP the constraints */
IF (SELECT Column_Name FROM Information_Schema.Columns
WHERE Table_Name = 'Product' AND Column_Name = 'Cost'
AND Column_Default IS NOT NULL AND TABLE_SCHEMA = 'Test') IS NOT NULL
BEGIN
ALTER TABLE Test.Product DROP CONSTRAINT [DF_Product_Cost];
END;
GO
IF (SELECT Column_Name FROM Information_Schema.Columns
WHERE Table_Name = 'Product' AND Column_Name = 'Cost'
AND Column_Default IS NOT NULL AND TABLE_SCHEMA = 'QC') IS NOT NULL
BEGIN
ALTER TABLE QC.Product DROP CONSTRAINT [DF_Product_Cost];
END;
GO
/* Verify if the constraints exist or not */
SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[QC].[DF_Product_Cost]')
AND type = 'D';
SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Test].[DF_Product_Cost]')
AND type = 'D';
/* DROP the columns as the final test */
ALTER TABLE Test.Product
DROP COLUMN Cost;
GO
ALTER TABLE QC.Product
DROP COLUMN Cost;
GO
Now to go onto Method 1, both the following bits of code failed spectacularly. That's to say SQL Server said "Command run successfully" but the DROP COLUMN statement failed because the constraints still existed.
/* Add back the columns and constraints */
ALTER TABLE Test.Product
ADD Cost MONEY NOT NULL CONSTRAINT DF_Product_Cost DEFAULT 0.00;
GO
ALTER TABLE QC.Product
ADD Cost MONEY NOT NULL CONSTRAINT DF_Product_Cost DEFAULT 0.00;
GO
/* Verify the constraints still exist */
SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[QC].[DF_Product_Cost]')
AND type = 'D';
SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Test].[DF_Product_Cost]')
AND type = 'D';
/* Trying DROP with just name */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'[DF_Product_Cost]'
AND type = 'D')
BEGIN
ALTER TABLE [Test].[Product] DROP CONSTRAINT [DF_Product_Cost]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'[DF_Product_Cost]'
AND type = 'D')
BEGIN
ALTER TABLE [QC].[Product] DROP CONSTRAINT [DF_Product_Cost]
END
GO
/* Trying DROP with Schema in the IF EXISTS subquery */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = '[Test].[DF_Product_Cost]'
AND type = 'D')
BEGIN
ALTER TABLE [Test].[Product] DROP CONSTRAINT [DF_Product_Cost]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'[QC].[DF_Product_Cost]'
AND type = 'D')
BEGIN
ALTER TABLE [QC].[Product] DROP CONSTRAINT [DF_Product_Cost]
END
GO
So I got to playing with the information in the sysobjects table and found in 2k8R2, the UID which points to the schema and in 2k5, the schema_id. At this point, figuring which tables need to be joined to in order to make Method 1 work become somewhat work-intensive.
So my conclusion is that Method 2 will always work regardless of how many schemas are in the database with the same name, Method 3 requires the minor tweak of adding the schema name to the WHERE clause, and Method 1 is probably best left to situations where a name is only used once (regardless of schema) unless the DBA or Dev enjoys adding lots and lots of extra work to configuring their DROP statements.
I didn't write up the Method 1 solutions for SQL 2k8R2 and SQL 2k5 because I'm pressed for time on other projects. But if someone really wants them, let me know and I'll write them up as soon as I've finished my high priority projects.
August 3, 2013 at 2:13 pm
Very nice testing and investigative work Brandie! I thought there was more to this than initially (un)covered! I have saved this one off for future use.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 5, 2013 at 4:51 am
TheSQLGuru (8/3/2013)
Very nice testing and investigative work Brandie! I thought there was more to this than initially (un)covered! I have saved this one off for future use.
:blush: Thank you for the compliment, Kevin. I feel like I've hit a professional milestone when an MVP saves off one of my scripts.
August 5, 2013 at 7:35 am
Brandie Tarvin (8/5/2013)
TheSQLGuru (8/3/2013)
Very nice testing and investigative work Brandie! I thought there was more to this than initially (un)covered! I have saved this one off for future use.:blush: Thank you for the compliment, Kevin. I feel like I've hit a professional milestone when an MVP saves off one of my scripts.
My turn to blush! 🙂 I will point out two things though: a) I am not currently an MVP and b) MVPs are not necessarily technically competent - just avid about the SQL Community. I would like to think that I still have both though. Honestly, I am certain that you do as well. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply