SQL常用判断检测语句代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SQL常用判断检测语句
作者:Dezai.CN / 发布于2011/9/16/ 754
<div><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">---------------------------------------------------------- </span><span style="color: rgb(0,128,128)">
--</span><span style="color: rgb(0,128,128)"> 判断数据库是否存在 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">IF</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">EXISTS</span><span style="color: rgb(0,0,0)"> (
</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">FROM</span><span style="color: rgb(0,0,0)"> master..sysdatabases
</span><span style="color: rgb(0,0,255)">WHERE</span><span style="color: rgb(0,0,0)"> name </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> N</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">DBName</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
)
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">not exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">---------------------------------------------------------- </span><span style="color: rgb(0,128,128)">
--</span><span style="color: rgb(0,128,128)"> 判断要创建的表名是否存在 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">IF</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">EXISTS</span><span style="color: rgb(0,0,0)"> (
</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">FROM</span><span style="color: rgb(0,0,0)"> dbo.sysobjects
</span><span style="color: rgb(0,0,255)">WHERE</span><span style="color: rgb(0,0,0)"> id </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">object_id</span><span style="color: rgb(0,0,0)">(N</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">[dbo].[TableName]</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">)
</span><span style="color: rgb(128,128,128)">AND</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">OBJECTPROPERTY</span><span style="color: rgb(0,0,0)">(id, N</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">IsUserTable</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">
)
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">not exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">---------------------------------------------------------- </span><span style="color: rgb(0,128,128)">
--</span><span style="color: rgb(0,128,128)"> 判断要创建临时表是否存在 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">IF</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">Object_Id</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">Tempdb.dbo.#TempDBName</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) </span><span style="color: rgb(0,0,255)">IS</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">NOT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">NULL</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">not exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">---------------------------------------------------------- </span><span style="color: rgb(0,128,128)">
--</span><span style="color: rgb(0,128,128)"> 判断要创建的存储过程名是否存在 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">IF</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">EXISTS</span><span style="color: rgb(0,0,0)"> (
</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">FROM</span><span style="color: rgb(0,0,0)"> dbo.sysobjects
</span><span style="color: rgb(0,0,255)">WHERE</span><span style="color: rgb(0,0,0)"> id </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">object_id</span><span style="color: rgb(0,0,0)">(N</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">[dbo].[SPName]</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">)
</span><span style="color: rgb(128,128,128)">AND</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">OBJECTPROPERTY</span><span style="color: rgb(0,0,0)">(id, N</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">IsProcedure</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">
)
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">not exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">---------------------------------------------------------- </span><span style="color: rgb(0,128,128)">
--</span><span style="color: rgb(0,128,128)"> 判断要创建的视图名是否存在 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">IF</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">EXISTS</span><span style="color: rgb(0,0,0)"> (
</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">FROM</span><span style="color: rgb(0,0,0)"> dbo.sysobjects
</span><span style="color: rgb(0,0,255)">WHERE</span><span style="color: rgb(0,0,0)"> id </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">object_id</span><span style="color: rgb(0,0,0)">(N</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">[dbo].[ViewName]</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">)
</span><span style="color: rgb(128,128,128)">AND</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">OBJECTPROPERTY</span><span style="color: rgb(0,0,0)">(id, N</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">IsView</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">
)
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">not exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">---------------------------------------------------------- </span><span style="color: rgb(0,128,128)">
--</span><span style="color: rgb(0,128,128)"> 判断要创建的函数名是否存在 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">IF</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">EXISTS</span><span style="color: rgb(0,0,0)"> (
</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">FROM</span><span style="color: rgb(0,0,0)"> dbo.sysobjects
</span><span style="color: rgb(0,0,255)">WHERE</span><span style="color: rgb(0,0,0)"> id </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">object_id</span><span style="color: rgb(0,0,0)">(N</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">[dbo].[FunName]</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">)
</span><span style="color: rgb(128,128,128)">AND</span><span style="color: rgb(0,0,0)"> xtype </span><span style="color: rgb(128,128,128)">IN</span><span style="color: rgb(0,0,0)"> (N</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">FN</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">, N</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">IF</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">, N</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">TF</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">)
)
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">not exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">---------------------------------------------------------- </span><span style="color: rgb(0,128,128)">
--</span><span style="color: rgb(0,128,128)"> 判断要创建的列名是否存在 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">IF</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">EXISTS</span><span style="color: rgb(0,0,0)"> (
</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> o.name </span><span style="color: rgb(0,0,255)">AS</span><span style="color: rgb(0,0,0)"> TableName,
c.name </span><span style="color: rgb(0,0,255)">AS</span><span style="color: rgb(0,0,0)"> ColumnName
</span><span style="color: rgb(0,0,255)">FROM</span><span style="color: rgb(0,0,0)"> sysobjects o </span><span style="color: rgb(0,0,255)">INNER</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(128,128,128)">JOIN</span><span style="color: rgb(0,0,0)"> syscolumns c
</span><span style="color: rgb(0,0,255)">ON</span><span style="color: rgb(0,0,0)"> o.id </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> c.id
</span><span style="color: rgb(0,0,255)">WHERE</span><span style="color: rgb(0,0,0)"> o.name </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">TableName</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(128,128,128)">AND</span><span style="color: rgb(0,0,0)"> c.name </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">ColumnName</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
)
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">not exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">---------------------------------------------------------- </span><span style="color: rgb(0,128,128)">
--</span><span style="color: rgb(0,128,128)"> 或者</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">IF</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">EXISTS</span><span style="color: rgb(0,0,0)"> (
</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">FROM</span><span style="color: rgb(0,0,0)"> sysobjects
</span><span style="color: rgb(0,0,255)">WHERE</span><span style="color: rgb(0,0,0)"> id </span><span style="color: rgb(128,128,128)">IN</span><span style="color: rgb(0,0,0)"> (</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> id
</span><span style="color: rgb(0,0,255)">FROM</span><span style="color: rgb(0,0,0)"> syscolumns
</span><span style="color: rgb(0,0,255)">WHERE</span><span style="color: rgb(0,0,0)"> name </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">ColumnName</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">)
</span><span style="color: rgb(128,128,128)">AND</span><span style="color: rgb(0,0,0)"> name </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">TableName</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
)
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">not exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">---------------------------------------------------------- </span><span style="color: rgb(0,128,128)">
--</span><span style="color: rgb(0,128,128)"> 或者</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">IF</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">col_length</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">TableName</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">ColumnName</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) </span><span style="color: rgb(0,0,255)">IS</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">NULL</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">not exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">PRINT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">exists</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span></div>
试试其它关键字
　常用判断检测语句　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)