with ForeignKeys as ( select fkName = '[' + foreignKey.name + ']' ,schemaName = s.name ,onTable = onTable.name ,onTableFullName = '[' + s.name + '].[' + onTable.name + ']' from sysforeignkeys fk inner join sysobjects foreignKey on foreignKey.id = fk.constid inner join sysobjects onTable on fk.fkeyid = onTable.id inner join sysusers s on onTable.uid = s.uid where 1=1 and onTable.type = 'U' -- and onTable.is_ms_shipped = 0 and onTable.name not like 'sys%' ) /*-----------------------------------------------------------------------------------------------*/ ,ObjectList as ( select name = o.name ,ObjectFullName = '[' + s.name + '].[' + o.name + ']' ,ObjectType = o.type from sysobjects o inner join sysusers s on o.uid = s.uid where 1=1 and type in ('FN','IF','P','TF','U','V') --and is_ms_shipped = 0 and o.name not like 'sys%' ) /*-----------------------------------------------------------------------------------------------*/ select sequence = 1 ,name = fkName ,sql = 'IF OBJECT_ID(N''' + onTableFullName + ''') IS NOT NULL AND OBJECT_ID(N''' + fkName + ''') IS NOT NULL ALTER TABLE ' + onTableFullName + ' drop constraint ' + fkName from foreignkeys union select sequence = 2 ,name = ObjectFullName ,sql = 'IF OBJECT_ID(N''' + ObjectFullName + ''') IS NOT NULL DROP ' + case objectType when 'FN' then 'FUNCTION' when 'IF' then 'FUNCTION' when 'P' then 'PROCEDURE' when 'TF' then 'FUNCTION' when 'U' then 'TABLE' when 'V' then 'VIEW' end + ' ' + ObjectFullName from ObjectList union select sequence = 3 ,name = '' ,sql = 'select * from sysobjects' order by sequence ,name
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.