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推荐】荐替换为你的表字段）
删除指定前缀的所有表
如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常用脚本，江湖人士网站整理。