随笔分类 - SQL Server
摘要：1、 下载ODAC-21.7-Xcopy-64-bit，并解压到目录ODAC21.7 2、 D盘创建目录：Oracle21C 3、 CMD转向到目录ODAC21.7，输入【install.bat oledb c:\oracle odac】，安装服务 4、 系统环境变量中，为Path添加“D:\ Or
阅读全文
摘要：SELECT CASE WHEN col.colorder = 1 THEN obj.nameELSE ''END AS 表名,col.colorder AS 序号 ,col.name AS 列名 ,ISNULL(ep.[value], '') AS 列说明 ,t.name AS 数据类型 ,col
阅读全文
摘要：--快速查看表结构SELECT CASE WHEN col.colorder = 1 THEN obj.name ELSE '' END AS 表名, col.colorder AS 序号 , col.name AS 列名 , ISNULL(ep.[value], '') AS 列说明 , t.na
阅读全文
摘要：USE [master] GO ALTER DATABASE [数据库名称] SET RECOVERY SIMPLE WITH NO_WAIT GO ALTER DATABASE [数据库名称] SET RECOVERY SIMPLE --改变数据模式为简单模式 GO USE [数据库名称] 指定需
阅读全文
摘要：SELECT spid, blocked, DB_NAME(sp.dbid) AS DBName, program_name, waitresource, sp.waittime, sp.stmt_start, lastwaittype, sp.loginame, sp.Status, sp.hos
阅读全文
摘要：CREATE PROCEDURE [dbo].[BatchDeleteTableField] @ColumnName VARCHAR(50)ASBEGIN DECLARE @sql VARCHAR(2000) DECLARE @Name VARCHAR(50) DECLARE bDTF CURSOR
阅读全文
摘要：DECLARE deleteDBTableData CURSOR FOR (SELECT name FROM sysobjects WHERE xtype='U' AND (name LIKE 'XXXX%' OR name LIKE 'YYY%'))OPEN deleteDBTableDataDE
阅读全文
摘要：USE [master]GO/****** Object: LinkedServer [SQL01] Script Date: 2020/4/9 11:51:17 ******/EXEC master.dbo.sp_addlinkedserver @server = N'SQL01', @srvpr
阅读全文
摘要：SELECT B.NAME,A.ROW_COUNT FROM SYS.DM_DB_PARTITION_STATS A,SYS.OBJECTS BWHERE A.OBJECT_ID=B.OBJECT_ID AND A.INDEX_ID<=1AND B.TYPE='U'AND A.ROW_COUNT>8
阅读全文
摘要：DECLARE @ESQL VARCHAR(1000);DECLARE FCursor CURSOR --定义游标FOR (SELECT 'ALTER TABLE '+O.name+' DROP CONSTRAINT '+F.name+';' AS CommandSQL from SYS.FOREI
阅读全文
摘要：UPDATE 表 SET 栏位A = REPLACE(栏位A, CHAR(10), '') UPDATE表 SET 栏位A = REPLACE(栏位A, CHAR(13), '')
阅读全文
摘要：DECLARE killspid CURSOR FOR (SELECT CONVERT(VARCHAR(100), request_session_id) FROM sys.dm_tran_locks WHERE resource_type='OBJECT' )OPEN killspidDECLAR
阅读全文
摘要：BEGIN TRAN BEGIN TRY DELETE FROM dbo.表INSERT INTO dbo.表( Id, 字段....)SELECTId,字段... FROM dbo.表 COMMIT TRAN END TRY BEGIN CATCH ROLLBACK END CATCH
阅读全文
摘要：USE [master]GOALTER DATABASE AAAAAA SET RECOVERY SIMPLE WITH NO_WAITGOALTER DATABASE AAAAAA SET RECOVERY SIMPLE --简单模式GOUSE AAAAAAGODBCC SHRINKFILE (N
阅读全文
摘要：CREATE FUNCTION [dbo].[Split](@separator VARCHAR(64)=',',@string NVARCHAR(MAX)) RETURNS @ResultTab TABLE ( Id INT , Res NVARCHAR(500) ) AS BEGIN DECLA
阅读全文
摘要：CREATE FUNCTION [dbo].[GetReportDWCustomerOrder] ( @YearDate DATETIME, 参数条件..... @CategoryName NVARCHAR(500)) RETURNS @Crm_Pj_DWCustomerOrder TABLE (O
阅读全文
摘要：还是拿数据库AAAAAAAA为例子 CREATE PROC [dbo].[p_log_backupAAAAAAAA] @dbname sysname='', @bkflogname NVARCHAR(260)='', @bkpath NVARCHAR(260)='w:' AS DECLARE @sq
阅读全文
摘要：拿AAAAAAAA数据库为例子，除了汉字标注的替换真实的数据，其他都不动 CREATE proc [dbo].[p_log_backupAAAAAAAA] @dbname sysname='', @bkflogname nvarchar(260)='', @bkpath nvarchar(260)=
阅读全文
摘要：（一）SQL Server-AlwaysOn 技术：SQL Server AlwaysOn 即“全面的高可用性和灾难恢复解决方案”1、数据库级可用性-只读副本：SQL Server 2012-4个，SQL Server 2014-8个a、每个节点都安装了本地的 SQL Server，可以不使用共享存
阅读全文
摘要：DECLARE @Index INTSET @Index=100DECLARE UpdateCursor CURSOR FOR (SELECT DISTINCT AA.Id FROM dbo.表 AA WHERE 数据过滤条件 )OPEN UpdateCursorDECLARE @DetailId
阅读全文
