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.
Table Of Contents
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.