Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Posts
    1

    Unanswered: How to empty a schema

    Hi,

    I'm trying to empty a schema that has alot of constraints and foreign keys
    and of course tables and views
    im trying to build a generic code for doing it ...
    so i wrote stuff like:
    select ' alter view '||name||'drop primary key cascade;'
    from sysobjects
    where type='V'
    in order to get a list of the names and put it in the syntax in order just to copy the syntax and delete it.
    on the views it works ok and theres no problem
    but when im trying to do the same in order to delete all the constraints i cant
    im using Sybase 12.5
    if any one has an ide how to empty a schema my way or a diffrent way please write ...
    thank's

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Your probably better off getting a tool (for eg., dbartisan or powerdesigner) to do the reverse engineering for you. i.e. Generate all the SQL (drop / create). From there you can mix and match.

  3. #3
    Join Date
    Jan 2009
    Posts
    1
    Hi,

    I would suggest to use the below SP. It works fine in SQLSERVER. I suggest to make the necessary modifications and you can find this SP very much handy.

    -- Script to removed all the TABLE constraints


    Use DatabaseName --Temporary table to hold constraints info most of the time at a different location
    -- or database
    --This could be a temp table however set as static

    IF EXISTS (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')
    truncate table T_FK_Xref
    go
    --Create Table to store constraint information
    IF NOT EXISTS (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')
    Create table DatabaseName.dbo.T_FK_Xref (
    ID int identity (1,1),
    ConstraintName varchar (255),
    MasterTable varchar(255),
    MasterColumn varchar(255),
    ChildTable varchar(255),
    ChildColumn varchar(255),
    FKOrder int
    )
    go
    --Store Constraints
    insert into DatabaseName.dbo.T_FK_Xref(ConstraintName,MasterTa ble,MasterColumn,ChildTable,ChildColumn,FKOrder)
    SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable
    ,sc2.name MasterColumn
    ,object_name(fkeyid) ChildTable
    ,sc1.name ChildColumn
    ,cast (sf.keyno as int) FKOrder
    FROM sysforeignkeys sf
    INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid
    INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid
    ORDER BY rkeyid,fkeyid,keyno

    go

    use databaseName --Database to removed constraints
    go
    ---Ready to remove constraints

    declare @ConstraintName varchar (max) -- Name of the Constraint
    declare @ChildTable varchar (max) -- Name of Child Table
    declare @MasterTable varchar (max)--Name of Parent Table
    declare @ChildColumn varchar (max)--Column of Child Table FK
    declare @MasterColumn varchar (max)-- Parent Column PK
    declare @FKOrder smallint -- Fk order
    declare @sqlcmd varchar (max) --Dynamic Sql String


    -- Create cursor to get constraint Information
    declare drop_constraints cursor
    fast_forward
    for
    SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable
    ,sc2.name MasterColumn
    ,object_name(fkeyid) ChildTable
    ,sc1.name ChildColumn
    ,cast (sf.keyno as int) FKOrder
    FROM sysforeignkeys sf
    INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid
    INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid
    ORDER BY rkeyid,fkeyid,keyno

    open drop_constraints
    fetch next from drop_constraints
    into
    @ConstraintName
    ,@MasterTable
    ,@MasterColumn
    ,@ChildTable
    ,@ChildColumn
    ,@FKOrder
    while @@Fetch_status = 0
    begin

    -- Create Dynamic Sql to drop constraint

    select @sqlcmd = 'alter table '+@ChildTable+' drop constraint '+@ConstraintName--+' foreign key '+'('+@ChildColumn+')'+' references '+@MasterTable+' ('+@MasterColumn+')'+' on delete no action on update no action'
    If EXISTs (select object_name(constid) from sysforeignkeys where object_name(constid) = @ConstraintName)
    exec (@sqlcmd)
    fetch next from drop_constraints
    into
    @ConstraintName
    ,@MasterTable
    ,@MasterColumn
    ,@ChildTable
    ,@ChildColumn
    ,@FKOrder
    end
    close drop_constraints
    deallocate drop_constraints

    go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •