1. 江湖人士首页
  2. IT江湖

SQL Server数据库开发过程中常用脚本整理

在开发环境中,我们可以随便对数据库进行折腾,一旦项目完毕即将上线时,我们有可能会对数据库进行些删除操作,如删除所有存储过程,所有视图,备份,还原等等,现将整理的脚本奉献如下:

–删除指定前缀的所有表,如Sys_User,Sys_Role

DECLARE @cmdText VARCHAR(8000)
SET @cmdText=”
SELECT @[email protected]+’drop table ‘+name+’; ‘
FROM (select [name] from [sysobjects] where [type] = ‘u’ AND NAME LIKE ‘Sys_%’) T
EXEC (@cmdText)

go

–删除所有存储过程

declare mycur cursor local for select [name] from dbo.sysobjects where xtype=’P’
declare @name varchar(100)
OPEN mycur
FETCH NEXT from mycur into @name
WHILE @@FETCH_STATUS = 0
BEGIN
exec(‘drop PROCEDURE ‘ + @name)
FETCH NEXT from mycur into @name
END
CLOSE mycur

go

–删除所有视图

declare mycur cursor local for select [name] from dbo.sysobjects where xtype=’V’
declare @name varchar(100)
OPEN mycur
FETCH NEXT from mycur into @name
WHILE @@FETCH_STATUS = 0
BEGIN
exec(‘drop VIEW ‘ + @name)
FETCH NEXT from mycur into @name
END
CLOSE mycur
go

–给表添加唯一约束,添加的列:Name,UnitID
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = ‘UQ_Product’)
BEGIN
ALTER TABLE Sys_Product DROP CONSTRAINT UQ_Product
END
go
Alter Table Sys_Product
Add Constraint UQ_Product
unique(Name,UnitID)
go

–通过脚本还原删除的数据

BACKUP LOG MEDIMS TO disk= N’d:\数据库名.log’ WITH NORECOVERY

RESTORE DATABASE MEDIMS FROM DISK = N’d:\数据库名.bak’ WITH NORECOVERY, REPLACE

RESTORE LOG 数据库名 FROM DISK = N’d:\数据库名.log’ WITH STOPAT = N’2013-12-02 23:16:50.550′ , RECOVERY

go

–现有表添加列
IF NOT exists(select * from syscolumns where id=object_id(‘dbo.Sys_User’) and name=’CreateTime’)
BEGIN
alter table Sys_User add CreateTime DATETIME not NULL DEFAULT ‘1900-01-01’
execute sp_addextendedproperty ‘MS_Description’, ‘创建时间’,’user’, ‘dbo’, ‘table’, ‘Sys_User’, ‘column’, ‘CreateTime’
END
go

–现有表删除列
IF exists(select * from syscolumns where id=object_id(‘dbo.Sys_User’) and name=’CreateTime’)
BEGIN
alter table Sys_User drop column CreateTime
END
GO

–现有表重命名列
IF exists(select * from syscolumns where id=object_id(‘dbo.Sys_User’) and name=’CreateTime’)
BEGIN
EXEC sp_rename ‘Sys_User.CreateTime’, ‘新列名’, ‘column’
END
GO

–创建存储过程
if exists(select 1 from sys.procedures where object_id=object_id(‘sp_Sys_UserAdd’))
begin
drop procedure sp_Sys_UserAdd
end
go

–更改列数据类型
ALTER TABLE Sys_User ALTER COLUMN Age int
go

–删除视图
IF EXISTS(SELECT 1 FROM sys.views WHERE name=’v_SB_User’)
DROP VIEW v_SB_User
GO

——————————————————————–
— 功能描述: 用户管理视图
— 创建作者: www.jhrs.com
— 创建时间: 2018-04-03 用户管理分页查询使用
— 修改描述: 2018-05-23 jhrs.com 增加用户角色数据查询
——————————————————————–
CREATE view [dbo].[v_SysUser] as
select * from Sys_User A,Sys_Role B where A.UserID=B.UserID
GO

 

–下面是根据表生成insert语句的存储过程。

–创建存储过程
if exists(select 1 from sys.procedures where object_id=object_id(‘proc_insert’))
begin
drop procedure proc_insert
end
go

— ======================================================
–根据表中数据生成insert语句的存储过程
–建立存储过程,执行proc_insert 表名
–感谢Sky_blue
–感谢szyicol
— ======================================================
CREATE proc [dbo].[proc_insert] (@tablename varchar(256))
as
begin
set nocount on
declare @sqlstr varchar(4000)
declare @sqlstr1 varchar(4000)
declare @sqlstr2 varchar(4000)
select @sqlstr=’select ”insert ‘[email protected]

select @sqlstr1=”
select @sqlstr2=’ (‘
select @sqlstr1= ‘ values ( ”+’
select @[email protected]+col+’+”,”+’ ,@[email protected]+ ‘[‘ + name + ‘]’ +’,’ from (select case
— when a.xtype =173 then ‘case when ‘+a.name+’ is null then ”NULL” else ‘+’convert(varchar(‘+convert(varchar(4),a.length*2+2)+’),’+a.name +’)’+’ end’
when a.xtype =104 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+’convert(varchar(1),[‘+a.name +’])’+’ end’
when a.xtype =175 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+””””’+’+’replace([‘+a.name+’],””””,””””””)’ + ‘+””””’+’ end’
when a.xtype =61 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+””””’+’+’convert(varchar(23),[‘+a.name +’],121)’+ ‘+””””’+’ end’
when a.xtype =106 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+’convert(varchar(‘+convert(varchar(4),a.xprec+2)+’),[‘+a.name +’])’+’ end’
when a.xtype =62 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+’convert(varchar(23),[‘+a.name +’],2)’+’ end’
when a.xtype =56 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+’convert(varchar(11),[‘+a.name +’])’+’ end’
when a.xtype =60 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+’convert(varchar(22),[‘+a.name +’])’+’ end’
when a.xtype =239 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+””””’+’+’replace([‘+a.name+’],””””,””””””)’ + ‘+””””’+’ end’
when a.xtype =108 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+’convert(varchar(‘+convert(varchar(4),a.xprec+2)+’),[‘+a.name +’])’+’ end’
when a.xtype =231 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+””””’+’+’replace([‘+a.name+’],””””,””””””)’ + ‘+””””’+’ end’
when a.xtype =59 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+’convert(varchar(23),[‘+a.name +’],2)’+’ end’
when a.xtype =58 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+””””’+’+’convert(varchar(23),[‘+a.name +’],121)’+ ‘+””””’+’ end’
when a.xtype =52 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+’convert(varchar(12),[‘+a.name +’])’+’ end’
when a.xtype =122 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+’convert(varchar(22),[‘+a.name +’])’+’ end’
when a.xtype =48 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+’convert(varchar(6),[‘+a.name +’])’+’ end’
— when a.xtype =165 then ‘case when ‘+a.name+’ is null then ”NULL” else ‘+’convert(varchar(‘+convert(varchar(4),a.length*2+2)+’),’+a.name +’)’+’ end’
when a.xtype =167 then ‘case when [‘+a.name+’] is null then ”NULL” else ‘+””””’+’+’replace([‘+a.name+’],””””,””””””)’ + ‘+””””’+’ end’
else ”’NULL”’
end as col,a.colid,a.name
from syscolumns a where a.id = object_id(@tablename) AND [Status]<>128 and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36
)t order by colid
select @[email protected]+left(@sqlstr2,len(@sqlstr2)-1)+’) ‘+left(@sqlstr1,len(@sqlstr1)-3)+’)” from ‘[email protected]
–print @sqlstr
exec( @sqlstr)
set nocount off
end

go

———————————-结束:根据表生成insert语句的存储过程———————————

 

Namesilo优惠:新用户省 $1 域名注册-优惠码:45D%UYTcxYuCloZ 国外最便宜域名!点击了解更多

特别优惠:免费赠送 $50 Vultr主机-限时优惠!英文站必备海外服务器!点击了解更多

VPS优惠:搬瓦工优惠码:BWH34QMFYT2R 最高省6.38%你懂的主机-一直优惠!点击了解更多

原创文章,作者:江小编,如若转载,请注明出处:https://jhrs.com/2018/17472.html

扫码关注【江湖人士】公众号,您会获得关于国外被动收入的最新资讯

WA付费会员QQ群:387027533,加这个群需要回答您的WA会员名,待核实后予以通过

普通QQ交流群:178758794,可分享交流建站的各类经验和知识

发表评论

登录后才能评论