Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Schema-Owned Tables and Generated DROP Scripts (SQL Spackl Expand / Collapse
Author
Message
Posted Wednesday, July 31, 2013 10:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:34 AM
Points: 7,132, Visits: 6,293
Comments posted to this topic are about the item Schema-Owned Tables and Generated DROP Scripts (SQL Spackl

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1479782
Posted Thursday, August 1, 2013 12:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 24, 2014 3:27 AM
Points: 60, Visits: 208
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
Post #1479796
Posted Thursday, August 1, 2013 5:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:34 AM
Points: 7,132, Visits: 6,293
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. @=)


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1479889
Posted Thursday, August 1, 2013 7:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1479947
Posted Thursday, August 1, 2013 10:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
+1. I also try to add these spackles to my SSMS templates.....
Post #1480036
Posted Friday, August 2, 2013 7:46 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
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 at GMail
Post #1480398
Posted Friday, August 2, 2013 9:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:34 AM
Points: 7,132, Visits: 6,293
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"?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1480476
Posted Saturday, August 3, 2013 10:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:34 AM
Points: 7,132, Visits: 6,293
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1480701
Posted Saturday, August 3, 2013 2:13 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
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 at GMail
Post #1480721
Posted Monday, August 5, 2013 4:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:34 AM
Points: 7,132, Visits: 6,293
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.


Thank you for the compliment, Kevin. I feel like I've hit a professional milestone when an MVP saves off one of my scripts.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1480869
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse