SQL:判断某些元素是否存在及创建的SQL语句

时间：2023-03-08 17:48:22

--判断某个存储过程是否存在
if exists (select * from sysobjects where id = object_id(N'[p_CreateTable]') and 
        OBJECTPROPERTY(id, N'IsProcedure') = 1)  
  drop procedure [p_CreateTable] 
--create proc p_CreateTable 
--as
--create table tUser(cid nvarchar(50),name nvarchar(50),age int,dept nvarchar(50))

--EXEC('p_CreateTable')

--判断数据库dbA是否存在
--create database dbA
if exists (select * from sys.databases where name = 'dbA')  
  drop database [dbA]

--判断表tableA是否存在
--CREATE TABLE tableA(id NVARCHAR(50),name NVARCHAR(50))
if exists (select * from sysobjects where id = object_id(N'[tableA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)  
  drop table [tableA]   

--判断存储过程p_CreateTable是否存在
--create proc p_CreateTable as
--create table user(cid nvarchar(50),name nvarchar(50),age int,dept nvarchar(50))
--EXEC('p_CreateTable')
if exists (select * from sysobjects where id = object_id(N'[p_CreateTable]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)  
  drop procedure [p_CreateTable] 

--判断视图v_tableAB是否存在
--CREATE VIEW v_tableAB AS
--SELECT a.id,a.NAME FROM tableA a

IF EXISTS (SELECT * FROM sys.views WHERE NAME='v_tableAB')
    DROP VIEW v_tableAB --PRINT '存在'

--判断表tableA中的列column1是否存在
--CREATE TABLE tableA(id NVARCHAR(50),NAME NVARCHAR(50))--测试数据
if exists(select * from syscolumns where id=object_id('tableA') and name='column1')  
  alter table tableA drop column column1 

--判断表tableA是否存在索引Index1
create index Index1 on tableA(id,name)--(创建索引)在ID NAME 两个字段上创建非聚集索引

if exists(select * from sysindexes where id=object_id('tableA') and name='index1') 
--if exists(select name from sys.indexes where name = N'Index1') 
DROP INDEX Index1 on tableA --删除索引  

select *from sys.indexes where name = 'Index1'--查看索引

--创建临时表(仅缓存在执行SQL的时候)
DECLARE @tableA TABLE(id NVARCHAR(50),NAME NVARCHAR(50))
INSERT INTO @tableA(id,name)VALUES ('aaa','bbb')
SELECT * FROM @tableA

相关文章