Schema-Owned Tables and Generated DROP Scripts (SQL Spackl

  • 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/[/url]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.

  • 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

  • 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/[/url]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.

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • +1. I also try to add these spackles to my SSMS templates.....

  • 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

  • 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/[/url]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.

  • 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/[/url]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.

  • 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

  • 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/[/url]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 (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