SQL 删除所有自定义视图/表脚本_删除视图脚本-CSDN博客

本文链接：https://blog.csdn.net/csdn_yrc/article/details/124691468

本文介绍如何使用SQL脚本删除视图（类型为'v'）和表（类型为'u'），同时注意了删除user-defined functions时的差异，特别是存在非自定义的function在类型'fn'中。建议在操作前做好备份和对比，以防误删。文中还提到了其他类型的数据库对象，如存储过程、触发器和约束等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

删除视图，类型为'v'

-- 删除所有自定义视图
Declare @SQLText Varchar(max),@viewNames varchar(max)
set @viewNames=''
select @viewNames=@viewNames+A.Name+',' from sysobjects A WHERE (xtype = 'v')
--select @viewNames,len(@viewNames)
if @viewNames <> ''
set @SQLText='drop view '+left(@viewNames,len(@viewNames)-1)
Exec(@SQLText)

删除表类似，类型为'u'

-- 删除所有用户自定义表
Declare @SQLText Varchar(max),@tableNames varchar(max)
set @tableNames=''
select @tableNames=@tableNames+A.Name+',' from sysobjects A WHERE (xtype = 'u')
--select @tableNames,len(@tableNames)
if @tableNames<> ''
set @SQLText='drop table'+left(@tableNames,len(@tableNames)-1)
Exec(@SQLText)

删除user-defined funnctions，这边得注意仔细对比，在实践过程中发现有点的function类型为IF，有些不是自定义的也会出现在类型fn里，需要特别注意，做好备份与对比

-- 删除所有用户自定义函数
Declare @SQLText Varchar(max),@functionNames varchar(max)
set @functionNames=''
select @functionNames=@functionNames+b.Name+',' FROM sysobjects b
where xtype IN ('fn','tf','IF') and b.name<>'fn_diagramobjects'
--select @functionNames,len(@functionNames)
if @functionNames<> ''
set @SQLText='drop FUNCTION '+left(@functionNames,len(@functionNames)-1)
Exec(@SQLText)

补充一下xtype类型对照表，要删除其他的可以参考上面的做相应调整

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL stored procedure
PC = Assembly (CLR) stored procedure
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure