SQL Server 建库 建表 添加列 添加,删除主键 SQL脚本 (create,alter,exists ,sysobjects,syscolumns)基础篇

--创建数据库
if exists (select * From master.dbo.sysdatabases where name='DirectionDemo')
 drop database DirectionDemo

create database DirectionDemo
on
(  
name = 'DirectionDemo',
filename = 'd:\DirectionDemo.mdf',
size = 10,
maxsize = 50,
filegrowth = 5 
)
log on
( 
name = 'DirectionDemo_log',
filename = 'd:\DirectionDemo_log.ldf',
size = 5MB,
maxsize = 25MB,
filegrowth = 5MB 
)
go

use DirectionDemo
go
--判断表是否存在 如果存在则删除
if exists(select * from sysobjects where  id =object_id('DirectionDemo.dbo.users'))
 drop table users 
--经常看到这样判断表是否存在 其实以下写法并不准确 无论何时都会删除,删除不存在表时就会报错 ,其实只要把count() 函数去掉即可
--if exists (select count(*) from sysobjects where name ='DirectionDemo.dbo.users')
 --drop table users 

--创建users表
create table users
(
userID int identity,
userName varchar(50),
userAge int ,
userAddress varchar(50)
)
print 'users 表创建成功'
go

--添加测试数据
insert into users values('张三',14,'河北承德')
insert into users values('李四',18,'河北廊坊')
insert into users values('王五',12,'河北石家庄')
insert into users values('李佳',19,'河北秦皇岛')
insert into users values('欧阳美惠',25,'河北承德')

--查询某表是否存在 应用object_id()函数
select * from syscolumns where name='userID' or name ='userAge'--由此可以看出userID 和userAge两列的ID相同
select object_id('DirectionDemo.dbo.users')as Talbe_ID
--object_id('数据库名.操作者.表名')  可查询此列在users表的ID编号 并且这个表的ID被列引用 
select id as  users表ID, name as users表列名,* from syscolumns where id =(select object_id('DirectionDemo.dbo.users')as Talbe_ID)--查询users表中所有的列

--判断列是否存在 删除列\添加列
if exists(select * from dbo.syscolumns where name='telephone' and id=object_id('DirectionDemo.dbo.users'))
 alter table users drop  column telephone --删除列
else
 alter table users add  telephone varchar(50)--添加列

--修改列的数据类型 
if exists(select * from dbo.syscolumns where name ='telephone' and id=object_id('DirectionDemo.dbo.users'))
    alter table users alter column telephone int--从varchar 修改为int 

--如果修改列名 那么drop 列名 alter一个新列名即可

 

--添加主键
alter table users add  primary key (userID)
--删除主键
declare @PK varchar(100)
select @PK=name from sysobjects where parent_obj=object_id('DirectionDemo.dbo.users') and xtype='PK'
if @PK is not null
 exec ('alter table users drop ' + @PK)

select * from sysobjects where parent_obj=object_id('DirectionDemo.dbo.users') and xtype='PK'--parent_obj是users表的ID