Sql中判断"数据库"、"表"、"临时表"、"存储过程"和列"是否存在

时间：2010-11-23 来源：瑞君

代码 --判断数据库是否存在
IF EXISTS (SELECT * FROM master..sysdatabases WHERE NAME = 'UAP5MC')
    PRINT 'exists '
else
    PRINT 'not exists'

--判断要创建表名是否存在 [两种方法]
--方法一:
IF EXISTS (select * From sysobjects Where Name ='tblstmTaskList' And Type In ('S','U'))
PRINT 'exists'
GO
--方法二:
if   exists   (select * from  sysobjects  where  id=object_id(N'[dbo].tblstmTaskList') and OBJECTPROPERTY(id, N'IsUserTable')=1)
PRINT 'exists'

--判断要创建临时表是否存在
IF  Object_Id( 'UAP5MC.dbo.#Test')   Is   Not   NULL--#Test 为临时表名
BEGIN
  print   '存在 '
END
ELSE
BEGIN
  print   '不存在 '
END

--判断要创建的存储过程名是否存在 [两种方法]
--方法一:
IF EXISTS (Select * From sysObjects Where Name ='SPService_Synctblcmbase_Server_SoftWare' And Type In ('S','P'))
PRINT 'exists'
--方法二:
if   exists(select   1   from   sysobjects   where   id=object_id('P_GetNewSeqVal_SeqT_FV')   and  xtype='P')
begin
drop PROCEDURE P_GetNewSeqVal_SeqT_FV;
end

--判断列名是否存在 [两种方法]
IF  EXISTS (SELECT   O.NAME AS 表名,C.NAME AS 列名 FROM SYSOBJECTS O
INNER JOIN SYSCOLUMNS C ON O.ID=C.ID  WHERE O.NAME= 'tblstmTaskList' AND C.NAME   = 'Title')
PRINT 'EXISTS'
ELSE
PRINT 'NOT EXISTS'
--第二种：
IF COL_LENGTH( 'tblstmTaskList','Title') IS NULL
    PRINT 'not exists'
ELSE
PRINT 'exists'