USE test
GO
--判断库是否存在
--status : 1 数据库完全关闭
-- 4 允许使用select into 快速大容量复制
-- 8 trunc.log on chkpt
-- 32 loading
-- 64 pre recovering
-- 128 revovering
-- 256 not revovered
-- 512 offline
-- 1024 read only
-- 2048 dbo use only
-- 4096 single user
-- 32768 emergency mode
-- 4194304 autoshrink
-- 1073741824 cleanly shutdown
IF EXISTS(
SELECT * FROM MASTER.dbo.sysdatabases s WHERE s.[name]='test' AND s.[status]<>512
)
PRINT 'Ok'
ELSE
PRINT 'No'
--判断表是否存在
--1、
IF EXISTS(
SELECT * FROM sysobjects s
WHERE s.id=OBJECT_ID(N'[dbo].[aa]') AND OBJECTPROPERTY(id,N'IsUserTable')=1
)
--DROP TABLE aa
PRINT 'Ok'
ELSE
PRINT 'No'
--2、
IF OBJECT_ID('aa') IS NOT NULL
PRINT 'Ok'
ELSE
PRINT 'No'
--3、
IF EXISTS(
SELECT 1 FROM information_schema.tables
WHERE table_type='base table' AND table_name='aa'
)
PRINT 'Ok'
ELSE
PRINT 'No'
--判断表列是否存在
IF EXISTS(
SELECT * FROM syscolumns s
WHERE s.[name]='id' AND s.id=OBJECT_ID('test.dbo.aa')
)
PRINT 'Ok'
ELSE
PRINT 'No'
--判断临时表是否存在
IF OBJECT_ID('tempdb.dbo.#test') IS NULL
PRINT 'Ok'
ELSE
PRINT 'No'
--判断存储过程是否存在
IF EXISTS(
SELECT * FROM sysobjects s
WHERE s.id=OBJECT_ID(N'[dbo].proc_getname')
AND OBJECTPROPERTY(id,N'IsProcedure')=1)
PRINT 'Ok'
ELSE
PRINT 'No'
--判断试图是否存在
IF EXISTS(
SELECT * FROM sysobjects s
WHERE s.id=OBJECT_ID(N'[dbo].view_buy')
AND OBJECTPROPERTY(id,N'IsView')=1)
PRINT 'Ok'
ELSE
PRINT 'No'
--判断触发器是否存在
IF EXISTS(
SELECT * FROM sysobjects s
WHERE s.id=OBJECT_ID(N'[dbo].trigger_datasale')
AND OBJECTPROPERTY(id,N'IsTrigger')=1)
PRINT 'Ok'
ELSE
PRINT 'No'
--判断函数是否存在
IF EXISTS (
SELECT * FROM sysobjects s
WHERE s.id=OBJECT_ID(N'f_NextNumber')
AND s.xtype IN (N'fn',N'if',N'tf')
)
PRINT 'Ok'
ELSE
PRINT 'No'
GO
--判断库是否存在
--status : 1 数据库完全关闭
-- 4 允许使用select into 快速大容量复制
-- 8 trunc.log on chkpt
-- 32 loading
-- 64 pre recovering
-- 128 revovering
-- 256 not revovered
-- 512 offline
-- 1024 read only
-- 2048 dbo use only
-- 4096 single user
-- 32768 emergency mode
-- 4194304 autoshrink
-- 1073741824 cleanly shutdown
IF EXISTS(
SELECT * FROM MASTER.dbo.sysdatabases s WHERE s.[name]='test' AND s.[status]<>512
)
PRINT 'Ok'
ELSE
PRINT 'No'
--判断表是否存在
--1、
IF EXISTS(
SELECT * FROM sysobjects s
WHERE s.id=OBJECT_ID(N'[dbo].[aa]') AND OBJECTPROPERTY(id,N'IsUserTable')=1
)
--DROP TABLE aa
PRINT 'Ok'
ELSE
PRINT 'No'
--2、
IF OBJECT_ID('aa') IS NOT NULL
PRINT 'Ok'
ELSE
PRINT 'No'
--3、
IF EXISTS(
SELECT 1 FROM information_schema.tables
WHERE table_type='base table' AND table_name='aa'
)
PRINT 'Ok'
ELSE
PRINT 'No'
--判断表列是否存在
IF EXISTS(
SELECT * FROM syscolumns s
WHERE s.[name]='id' AND s.id=OBJECT_ID('test.dbo.aa')
)
PRINT 'Ok'
ELSE
PRINT 'No'
--判断临时表是否存在
IF OBJECT_ID('tempdb.dbo.#test') IS NULL
PRINT 'Ok'
ELSE
PRINT 'No'
--判断存储过程是否存在
IF EXISTS(
SELECT * FROM sysobjects s
WHERE s.id=OBJECT_ID(N'[dbo].proc_getname')
AND OBJECTPROPERTY(id,N'IsProcedure')=1)
PRINT 'Ok'
ELSE
PRINT 'No'
--判断试图是否存在
IF EXISTS(
SELECT * FROM sysobjects s
WHERE s.id=OBJECT_ID(N'[dbo].view_buy')
AND OBJECTPROPERTY(id,N'IsView')=1)
PRINT 'Ok'
ELSE
PRINT 'No'
--判断触发器是否存在
IF EXISTS(
SELECT * FROM sysobjects s
WHERE s.id=OBJECT_ID(N'[dbo].trigger_datasale')
AND OBJECTPROPERTY(id,N'IsTrigger')=1)
PRINT 'Ok'
ELSE
PRINT 'No'
--判断函数是否存在
IF EXISTS (
SELECT * FROM sysobjects s
WHERE s.id=OBJECT_ID(N'f_NextNumber')
AND s.xtype IN (N'fn',N'if',N'tf')
)
PRINT 'Ok'
ELSE
PRINT 'No'