none
How to Delete objects in a database, the object are: tables, user defined functions, views RRS feed

  • Question

  • I have written this code to drop off the objects in a database:

    IF Exists(SELECT

    [dbo].[table1

    ,[dbo].[storeProcedure]

    ,[dbo].[Ud_ThisUserDefined])

    BEGIN

    Drop off(

    [dbo].[table1

    ,[dbo].[storeProcedure]

    ,[dbo].[Ud_ThisUserDefined]

    END

    ELSE

    BEGIN

    SELECT 'can not be deleted'

    END

     --------------


    CloudsInSky

    Monday, December 18, 2017 3:22 AM

All replies

  • Depending on the type of the object you've to use corresponding DROP command

    for example for dropping a table you need to use 

    DROP TABLE <TableName>


    Procedure

    DROP PROC <ProcName>

    etc

    You can get the type information of the object from sys.objects catalog view

    Also remember to drop dependent objects like views, procedure, trigger etc before dropping the master table object


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Monday, December 18, 2017 4:53 AM
    • Proposed as answer by Harry Bal Monday, December 18, 2017 3:47 PM
    Monday, December 18, 2017 4:52 AM
  • IF OBJECT_ID('dbo.Product', 'U') IS NOT NULL
     DROP TABLE dbo.Product;

    ---SQL Server 2016 and onward

     DROP TABLE IF EXISTS dbo.Product

     DROP procedure IF EXISTS dbo.Product

     DROP function IF EXISTS dbo.Product


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Harry Bal Monday, December 18, 2017 3:48 PM
    Monday, December 18, 2017 6:46 AM
  • Visakh,

    What would be the syntax if I have several tables, several stored procedures and a couple of UDF?

    Should I use drop off for each of the objects or use a drop off with a pair of Parenthesis and all the items in parenthesis?

    Thanks,

    CloudySky


    CloudsInSky

    Wednesday, December 20, 2017 5:57 AM
  • Visakh,

    What would be the syntax if I have several tables, several stored procedures and a couple of UDF?

    Should I use drop off for each of the objects or use a drop off with a pair of Parenthesis and all the items in parenthesis?

    Thanks,

    CloudySky


    CloudsInSky

    You would need to DROP each object using separate DROP commands

    And it has to be in the reverse chronological order

    i.e dependent child objects like Stored Procedure, Trigger, Views etc first followed by the base tables they refer

    You can generate this easily using catalog views like sys.objects

    for example for dropping procedures you can use logic like

    SELECT 'DROP PROC ' + name + ';'
    FROM sys.objects
    WHERE type = 'P'
    AND OBJECTPROPERTY(object_id,'IsMSShipped') = 0
    Will generate the DROP script for all user stored procedures in the database


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, December 20, 2017 6:09 AM
  • Hello Visakh,

    If I have mytable1, mytable2, usp1, usp2 and ufn1, ufn2, how do you put these in this statement?

    SELECT 'DROP PROC ' + name + ';'
    FROM sys
    .objects
    WHERE type
    = 'P'
    AND OBJECTPROPERTY
    (object_id,'IsMSShipped') = 0

    and what is 'IsMSShipped'?

    Regards,

    CloudsInSky


    CloudsInSky

    Wednesday, December 20, 2017 7:58 PM
  • What is the version of your SQL server?

    A Fan of SSIS, SSRS and SSAS

    Wednesday, December 20, 2017 8:11 PM
  • 2012?

    Since you noted a fan of SSAS, what is that? I know it is sql Server analysis serveries, what does it do?

    Regards,

    Cloudsinsky


    CloudsInSky

    Thursday, December 21, 2017 3:37 AM
  • Hi cloudInSky,

    We can drop multiple SQL Server objects with a single Drop statement.

    For example:

    USE AdventureWorks
    GO
    DROP PROCEDURE USP1
    DROP PROCEDURE USP2
    DROP PROCEDURE USP3
    GO


    To

    USE AdventureWorks
    GO
    DROP PROCEDURE USP4,USP5,USP6
    GO

    By the way, SQL Server Analysis Services is an analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 26, 2017 2:39 AM
    Moderator
  • Hello Visakh,

    If I have mytable1, mytable2, usp1, usp2 and ufn1, ufn2, how do you put these in this statement?

    SELECT 'DROP PROC ' + name + ';'
    FROM sys
    .objects
    WHERE type
    = 'P'
    AND OBJECTPROPERTY
    (object_id,'IsMSShipped') = 0

    and what is 'IsMSShipped'?

    Regards,

    CloudsInSky


    CloudsInSky

    IsMSShipped is a flag which identifies between system and user defined objects. That filter is used to make sure only user defined objects are chosen not the ones provided by Microsoft by default

    Since in your case you have only handful of objects, you can even do this directly 

    like

    IF EXISTS (
    SELECT 1 
    FROM sys.objects
    WHERE type_desc LIKE '%FUNCTION'
    AND name = 'ufn1'
    )
    DROP FUNCTION dbo.ufn1
    
    
    IF EXISTS (
    SELECT 1 
    FROM sys.objects
    WHERE type_desc LIKE '%FUNCTION'
    AND name = 'ufn2'
    )
    DROP FUNCTION dbo.ufn2
    
    IF EXISTS (
    SELECT 1 
    FROM sys.objects
    WHERE type_desc = 'SQL_STORED_PROCEDURE'
    AND name = 'usp1'
    )
    DROP PROC dbo.usp1
    
    
    IF EXISTS (
    SELECT 1 
    FROM sys.objects
    WHERE type_desc  = 'SQL_STORED_PROCEDURE'
    AND name = 'usp2'
    )
    DROP PROC dbo.usp2
    
    IF EXISTS (
    SELECT 1 
    FROM sys.objects
    WHERE type_desc = 'USER_TABLE'
    AND name = 'mytable1'
    )
    DROP TABLE dbo.mytable1
    
    IF EXISTS (
    SELECT 1 
    FROM sys.objects
    WHERE type_desc = 'USER_TABLE'
    AND name = 'mytable2'
    )
    DROP TABLE dbo.mytable2
    I assume all your objects are in dbo schema



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Tuesday, December 26, 2017 4:51 AM