10+ SQL Server commonly used scripts

10+ SQL Server commonly used scripts, In the development environment, we can perform arbitrary operations on the database. Once the project is completed and is about to go online, we may perform some deletion operations on the database, such as deleting all stored procedures, all views, backups, restores, etc.

SQL Server commonly used scripts

The SQL Server database organized by SQL commonly used scripts . In the development environment, we can toss the database at will. Once the project is completed and will go online, we may perform some deletion operations on the database, such as deleting all stored procedures , all views, and backups. , Restore, etc., the compiled scripts are now as follows:

Delete all tables with the specified prefix

Such as 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

10+ SQL Server commonly used scripts

Delete all stored procedures

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

10+ SQL Server commonly used scripts

Delete all stored procedures

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

10+ SQL Server commonly used scripts

Delete all views

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

10+ SQL Server commonly used scripts

Add unique constraints to multiple columns in the table

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

10+ SQL Server commonly used scripts

Restore deleted data via script

BACKUP LOG MEDIMS TO disk = N'd:\database name.log' WITH NORECOVERY
RESTORE DATABASE MEDIMS FROM DISK = N'd:\database name.bak' WITH NORECOVERY, REPLACE
RESTORE LOG database name FROM DISK = N'd:\database name.log' WITH STOPAT = N'2013-12-02 23:16:50.550', RECOVERY
go

10+ SQL Server commonly used scripts

Add column to existing table

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', 'create time','user', 'dbo', 'table', 'Sys_User', 'column', 'CreateTime'
END
go

10+ SQL Server commonly used scripts

Delete column from existing table

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','Create Time','user','dbo','table','Sys_User','column','CreateTime'
END
go

10+ SQL Server commonly used scripts

Rename column of existing table

IF exists(select * from syscolumns where id=object_id('dbo.Sys_User') and name='CreateTime')
BEGIN
EXEC sp_rename'Sys_User.CreateTime','New column name','column'
END
GO

10+ SQL Server commonly used scripts

Create a stored procedure

if exists(select 1 from sys.procedures where object_id=object_id('sp_Sys_UserAdd'))
begin
drop procedure sp_Sys_UserAdd
end
go

10+ SQL Server commonly used scripts

Change column data type

ALTER TABLE Sys_User ALTER COLUMN Age int
go

10+ SQL Server commonly used scripts

Delete view

IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_SB_User')
DROP VIEW v_SB_User
GO

Create view

-------------------------------------------------- ------------------
- Function description: User management view
- Author: https://jhrs.com
- Creation time: 2018-04-03 User management paging query use
- Modification description: 2018-05-23 jhrs.com adds user role data query
-------------------------------------------------- ------------------
CREATE view [dbo].[v_SysUser]
as
select * from Sys_User A,Sys_Role B where A.UserID=B.UserID
GO

10+ SQL Server commonly used scripts

According to the table to generate the stored procedure of the insert statement

-Create a stored procedure
if exists(select 1 from sys.procedures where object_id=object_id('proc_insert'))
begin
drop procedure proc_insert
end
go

- =============================================== ======
--According to the data in the table to generate the stored procedure of the insert statement
-Create a stored procedure, execute proc_insert table name
--Thanks Sky_blue
--Thanks to 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

----------------------------------End: According to the table to generate the stored procedure of the insert statement ------ ---------------------------

Paging stored procedures of SQL commonly used scripts

-Create a stored procedure
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), - table name
    @strGetFields varchar(1000) ='*', - the column to be returned
    @OrderField varchar(255)='', - the name of the sorted field
    @PageSize int = 10, - page size
    @PageIndex int = 1, - page number
    @strWhere varchar(1500) ='', - query conditions (note: do not add where)
    @Counts int = 0 output - the number of records queried
)
AS
declare @strSQL nvarchar(4000) - main statement
declare @totalRecord int --The number of records queried
declare @SqlCounts nvarchar(max) - SQL construction for total query
--Calculate the total number of records
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--calculate the total number of records
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
 --Handle the situation that the number of pages is out of range
    IF @PageIndex<=0
        SET @PageIndex = 1
      
     --Processing start point and end point
    DECLARE @StartRecord INT
    DECLARE @EndRecord int
    
    SET @StartRecord = (@pageIndex-1)*@PageSize + 1
    SET @EndRecord = @StartRecord + @PageSize-1

     -Continue to synthesize SQL statements
    SET @strSQL = @strSQL +') as tempTable where rowId >=' + CONVERT(VARCHAR(50),@StartRecord) + 'and rowid<=' + CONVERT(VARCHAR(50),@EndRecord)

    exec sp_executesql @strSQL
GO

The above is a paging stored procedure in a common SQL script. After adding it to the database, it can be executed directly.

CTE expression recursive query

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

The above SQL commonly used script is one of the SQL Server recursive query solutions, and it is also very practical.

SQL commonly used scripts are written at the end

After collecting some very useful SQL commonly used scripts, you can easily take them out and modify them before you can use them. For some enterprise-level systems, it may be helpful, especially for some old projects, which will still be flooded with a lot of SQL code. Project.

The above is the SQL commonly used scripts, organized by the quack people website.