10+ SQL常用脚本整理之SQL Server数据库

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

SQL常用脚本整理之SQL Server数据库
SQL常用脚本整理之SQL Server数据库

SQL常用脚本

SQL常用脚本整理之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

创建视图

--------------------------------------------------------------------
-- 功能描述: 用户管理视图
-- 创建作者: https://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语句的存储过程---------------------------------

SQL常用脚本之分页存储过程

--创建存储过程
if exists(select 1 from sys.procedures where object_id=object_id('sp_GetPageList'))
begin
    drop procedure sp_GetPageList
end
go

CREATE  PROCEDURE [dbo].[sp_GetPageList]
(
    @TableName   nvarchar(max),         -- 表名
    @strGetFields varchar(1000) = '*',  -- 需要返回的列 
    @OrderField varchar(255)='',        -- 排序的字段名
    @PageSize   int = 10,               -- 页尺寸
    @PageIndex int = 1,                 -- 页码
    @strWhere varchar(1500) = '',       -- 查询条件 (注意: 不要加 where)
    @Counts    int = 0  output          --查询到的记录数
)
AS
declare @strSQL   nvarchar(4000)       -- 主语句
declare @totalRecord int               --查询到的记录数
declare @SqlCounts nvarchar(max)       --对总数查询进行SQL构造
--计算总记录数
begin
    if @strWhere !=''
set @SqlCounts = 'select @totalRecord=count(*)  from ' + @TableName + ' where '[email protected]
    else
set @SqlCounts = 'select @totalRecord=count(*)  from ' + @TableName + '' 
end 

exec sp_executesql @SqlCounts,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
set  @[email protected] 

BEGIN
IF (@strWhere='' or @strWhere IS NULL)
SET @strSQL = 'Select * FROM (select ' + @strGetFields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName
ELSE
SET @strSQL = 'Select * FROM (select ' + @strGetFields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName + ' where ' + @strWhere    
END
 --处理页数超出范围情况 
    IF @PageIndex<=0 
        SET @PageIndex = 1
      
     --处理开始点和结束点
    DECLARE @StartRecord INT 
    DECLARE @EndRecord int
    
    SET @StartRecord = (@pageIndex-1)*@PageSize + 1
    SET @EndRecord = @StartRecord + @PageSize - 1

     --继续合成sql语句
    SET @strSQL = @strSQL + ') as tempTable where rowId >=' + CONVERT(VARCHAR(50),@StartRecord) + ' and rowid<= ' + CONVERT(VARCHAR(50),@EndRecord)

    exec sp_executesql @strSQL
GO

以上就是一个SQL常用脚本中的分页存储过程,添加到数据库后,直接执行就可以了。

CTE表达式递归查询

WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)
AS
(
   SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree 
   FROM MyDepartment
   WHERE ParentID IS NULL
   UNION ALL
   SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1
   FROM MyDepartment
   JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
)
 
SELECT * FROM OrgTree ORDER BY Tree

上面的SQL常用脚本是SQL Server递归查询的解决方案之一,也算是非常实用的。

SQL常用脚本之写在最后

一些非常有用的SQL常用脚本收集起来之后,可以方便的拿出来改下就可以使用了,对于一些企业级系统,或许是会有帮助的,尤其是一些老项目,还是会有大量的SQL代码充斥项目中。

以上就是SQL常用脚本,江湖人士网站整理。

User Review
0 (0 votes)
本站最新优惠

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

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

VPS优惠:搬瓦工优惠码:BWH3OGRI2BMW 最高省5.83%打开外面世界的一款主机点击了解更多

Paxful上购买比特币

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

扫码注册Paxful账户开始交易比特币,全球300多种支付方式任意选,国内需使用外网工具打开

必备的解锁外网工具,畅访谷歌等国外网站,几包烟钱让你无须再找免费梯

发表评论

登录后才能评论

评论列表(2条)

  • 江小编
    江小编 2020-12-14 21:55

    已经补上了一个认为还比较好的,可以看看。

  • IT菜鸟
    IT菜鸟 2020-12-14 17:54

    似乎少了点分页存储过程,不过还好,网上到处都是。

RankMath 插件仅需$59,WordPress 最值得推荐的SEO插件,不限网站数量
见官网
直达官网