%
main()
Sub main()
%>
<% = AppTitle %>
<%
Dim cmds
Dim Query, Content, Content1
Query = "SELECT parent_sysobjects.name AS parent, sysobjects.name, sysobjects.xtype, sysusers.name as owner"
Query = Query & " FROM sysobjects"
Query = Query & " LEFT OUTER JOIN sysusers ON sysobjects.uid = sysusers.uid"
Query = Query & " LEFT OUTER JOIN sysobjects parent_sysobjects ON sysobjects.parent_obj = parent_sysobjects.id"
Query = Query & " WHERE (sysobjects.name LIKE N'DF_T%' OR sysobjects.name LIKE N'FK_T%' OR sysobjects.name LIKE N'GTR_%' OR sysobjects.name LIKE N'S%' OR sysobjects.name LIKE N'V%')"
Query = Query & " AND (sysobjects.name NOT LIKE N'sys%')"
Query = Query & " AND (sysobjects.xtype = N'D' OR sysobjects.xtype = N'F' OR sysobjects.xtype = N'PK' OR sysobjects.xtype = N'TR' OR sysobjects.xtype = N'P' OR sysobjects.xtype = N'V')"
Query = Query & " ORDER BY sysobjects.name"
Set db = Server.CreateObject("ADODB.Connection")
db.Open SQLConn
Set Content = OpenQuery(Query)
Do While Not(Content.BOF Or Content.EOF)
Dim name, parent, cmd, xtype, owner
parent = Content.Fields("parent")
name = Content.Fields("name")
xtype = Content.Fields("xtype")
owner = Content.Fields("owner")
cmd = ""
If xtype = "P " Then
cmd = cmd & "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" & name & "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" & "
" & vbCrLf
cmd = cmd & "drop procedure " & owner & "." & name & "
" & vbCrLf
ElseIf xtype = "PK" Then
cmd = cmd & "if exists (SELECT name FROM sysobjects WHERE (name = N'" & name & "') AND (xtype = 'PK'))" & "
" & vbCrLf
cmd = cmd & "ALTER TABLE " & owner & "." & parent & " DROP CONSTRAINT " & name & "
" & vbCrLf
ElseIf xtype = "F " Then
cmd = cmd & "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" & name & "]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)" & "
" & vbCrLf
cmd = cmd & "ALTER TABLE " & owner & "." & parent & " DROP CONSTRAINT " & name & "
" & vbCrLf
ElseIf xtype = "V " Then
cmd = cmd & "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" & name & "]') and OBJECTPROPERTY(id, N'IsView') = 1)" & "
" & vbCrLf
cmd = cmd & "DROP VIEW " & name & "
" & vbCrLf
ElseIf xtype = "TR" Then
cmd = cmd & "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" & name & "]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)" & "
" & vbCrLf
cmd = cmd & "drop trigger [dbo].[" & name & "]" & "
" & vbCrLf
Else
cmd = cmd & "ALTER TABLE " & owner & "." & parent & " DROP CONSTRAINT " & name & "
" & vbCrLf
End If
If Request("w") = "1" Then
Set Content1 = OpenQuery(cmd)
End If
Response.Write(cmd)
Response.Write("GO
" & vbCrLf)
Response.Write("
" & vbCrLf)
Content.MoveNext
Loop
%>
<%
End Sub
%>