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

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

江小编 • • IT江湖

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

本文阅读导航 展开
SQL常用脚本整理之SQL Server数据库
SQL常用脚本整理之SQL Server数据库

SQL常用脚本

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

下面是2021年8月7日更新的，也是工作中遇到要使用的一些脚本。

表字段添加说明

为了更好的团队交流，新人更好的理解表的设计理念，表字段添加上说明非常方便，同时也为代码生成工具直接引用字段说明生成实体类提供了方便，这样可以直接生成实体类的注释；还可以根据表字段的说明生成一个数据库的文档，对于做企业级项目时是有用的，因为客户或者招标时会用得上。

下面的逻辑是判断该字段有无说明，有时先删除，然后再写入新的说明；不然当已经有说明时，直接使用sp_dropextendedproperty存储过程为字段添加描述会报错。当然也可以调用更新存储过程

IF EXISTS (
	SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('jhrs.com')  -- jhrs.com为表名，使用时自己替换
		AND [name] = N'MS_Description' 
		AND [minor_id] = (
			SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = '国外VPS推荐字段'       -- 列名（字段名称，使用时将【国外VPS推荐】荐替换为你的表字段）
				AND [object_id] = OBJECT_ID('NoticeMessage_Group'))
)
begin
	-- 判断当前表的字段
	EXEC sys.sp_dropextendedproperty @name=N'MS_Description',
		@level0type=N'SCHEMA', 
		@level0name=N'dbo', 
		@level1type=N'table', 
		@level1name=N'jhrs.com',        -- 表名，使用时将【jhrs.com】替换为你的表名
		@level2type=N'COLUMN', 
		@level2name=N'国外VPS推荐字段'  -- 列名（字段名称，使用时将【国外VPS推荐】荐替换为你的表字段）
end
	--重新为字段写入说明描述
    EXEC sys.sp_addextendedproperty 
		@name = N'MS_Description', 
		@value = N'这是字段描述，国外VPS推荐请访问：https://jhrs.com/2021/39271.html', 
		@level0type = N'SCHEMA', 
		@level0name = N'dbo', 
		@level1type = N'TABLE', 
		@level1name = N'jhrs.com',      -- 表名，使用时将【jhrs.com】替换为你的表名
		@level2type = N'COLUMN', 
		@level2name=N'国外VPS推荐字段'  -- 列名（字段名称，使用时将【国外VPS推荐】荐替换为你的表字段）
go

-- 下面是更新代码，必须当字段有说明时才能使用，不然也报错。建议使用上面的代码保平安。

EXECUTE sp_updateextendedproperty N'MS_Description', 
	'这是新的字段说明，国外VPS推荐请访问：https://jhrs.com/2021/39271.html',  -- 新的说明（字段描述）
	N'user', 
	N'dbo', 
	N'table', 
	N'jhrs.com',   -- 表名，使用时将【jhrs.com】替换为你的表名
	N'column', 
	N'GroupName'   -- 列名（字段名称，使用时将【国外VPS推荐】荐替换为你的表字段）
这是新的字段说明，国外VPS推荐请访问：https://jhrs.com/2021/39271.html
SQL常用脚本整理 – 这是新的字段说明，国外VPS推荐请访问：https://jhrs.com/2021/39271.html

删除指定前缀的所有表

如Sys_User，Sys_Role

DECLARE @cmdText VARCHAR(8000)
SET @cmdText=''
SELECT @cmdText=@cmdText+'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 '+@tablename
select @sqlstr1=''
select @sqlstr2=' ('
select @sqlstr1= ' values ( ''+'
select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+ '[' + 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 @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename
--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 '+@strWhere
    else
set @SqlCounts = 'select @totalRecord=count(*)  from ' + @TableName + '' 
end 

exec sp_executesql @SqlCounts,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
set  @Counts=@totalRecord 

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常用脚本，江湖人士网站整理。

加入电报群

【江湖人士】(jhrs.com)原创文章，作者：江小编，如若转载，请注明出处：https://jhrs.com/2018/17472.html

扫码加入电报群，让你获得国外网赚一手信息。

文章标题：10+ SQL常用脚本整理之SQL Server数据库

赞 (2)
江小编的头像江小编
0 2
上一篇 2018-04-03 20:38
下一篇 2018-04-04 15:33

热门推荐

2 Comments

  1. 的头像 江小编 2020-12-14

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

    回复
  2. 的头像 IT菜鸟 2020-12-14

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

    回复

Leave a Reply

Sending

首页
专题
WA
网赚
国外VPS推荐：Hostinger VPS最具性价比，内存大、高速SSD、完整 root、DDoS 保护，超稳定
低至 $4.99 / 月
直达官网