sql server 删除所有表、视图、存储过程
如果由于外键约束删除
table
失败,则先删除所有约束:
--/第1步**********删除所有表的外键约束*************************/
DECLARE
c1
cursor
for
select
'alter table ['
+ object_name(parent_obj) +
'] drop constraint ['
+
name
+
']; '
from
sysobjects
where
xtype =
'F'
open
c1
declare
@c1
varchar
(8000)
fetch
next
from
c1
into
@c1
while(@@fetch_status=0)
begin
exec
(@c1)
fetch
next
from
c1
into
@c1
end
close
c1
deallocate
c1
--/第2步**********删除所有表*************************/
use 数据库名(是要删除表的所在的那个数据库的名称)
GO
declare
@sql
varchar
(8000)
while (
select
count
(*)
from
sysobjects
where
type=
'U'
)>0
begin
SELECT
@sql=
'drop table '
+
name
FROM
sysobjects
WHERE
(type =
'U'
)
ORDER
BY
'drop table '
+
name
exec
(@sql)
end
--删除所有的存储过程同理可得,但不需要走第一步,只需将第2步的代码的where type='U' 改成 where type='P',drop table 改成 drop Procedure。
删除表
use 数据库名 declare mycur cursor local for select [ name ] from dbo.sysobjects where xtype= 'U' declare @ name varchar (100) OPEN mycur FETCH NEXT from mycur into @ name WHILE @@FETCH_STATUS = 0 BEGIN exec ( 'drop table ' + @ name ) FETCH NEXT from mycur into @ name END CLOSE mycur |
删除视图
use 数据库名 declare mycur cursor local for select [ name ] from dbo.sysobjects where xtype= 'V' declare @ name varchar (100) OPEN mycur FETCH NEXT from mycur into @ name WHILE @@FETCH_STATUS = 0 BEGIN exec ( 'drop VIEW ' + @ name ) FETCH NEXT from mycur into @ name END CLOSE mycur |
删除存储过程
use eldbs declare mycur cursor local for select [ name ] from dbo.sysobjects where xtype= 'P' declare @ name varchar (100) OPEN mycur FETCH NEXT from mycur into @ name WHILE @@FETCH_STATUS = 0 BEGIN exec ( 'drop PROCEDURE ' + @ name ) FETCH NEXT from mycur into @ name END CLOSE mycur |