SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Schema-Owned Tables and Generated DROP Scripts (SQL Spackl


Schema-Owned Tables and Generated DROP Scripts (SQL Spackl

Author
Message
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14723 Visits: 9000
Comments posted to this topic are about the item Schema-Owned Tables and Generated DROP Scripts (SQL Spackl

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
dmitry.maletin 89875
dmitry.maletin 89875
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 400
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
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14723 Visits: 9000
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86810 Visits: 41103
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sneumersky
sneumersky
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2494 Visits: 487
+1. I also try to add these spackles to my SSMS templates.....
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12560 Visits: 8557
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
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14723 Visits: 9000
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14723 Visits: 9000
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12560 Visits: 8557
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
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14723 Visits: 9000
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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search