我想我是海 冬天的大海 心情随风轻摆

USE tempdbGOIF OBJECT_ID('t') IS NOT NULL DROP TABLE tGOCREATE TABLE t( id INT, [name] NVARCHAR(10) )GOSET NOCOUNT ONINSERT INTO t VALUES(1,'a')INSERT INTO t VALUES(1,'a')INSERT INTO t VALUES(2,'b')INSERT INTO t VALUES(2,'b')INSERT INTO t V.

IF OBJECT_ID('[Fun_GetPinYin]') IS NOT NULLDROP FUNCTION [Fun_GetPinYin]GO-- =============================================-- Author: yenange-- Create date: 2020-09-01-- Description: 获取拼音-- =============================================CREATE FUNCT.

上面的出错， 用 SQL Server 的人基本上都遇到过。如果某个表的字段少还好， 有几十甚至更多字段就抓瞎了，如何快速判断哪个字段超长呢？先创建一个存储过程：IF OBJECT_ID('Proc_Util_GetLenOverflowColumns') IS NOT NULL DROP PROC Proc_Util_GetLenOverflowColumnsGO-- =============================================-- Author: y..

sp_OACreate 与 xp_cmdshell 都是比较危险的存储过程，一般情况下应该禁用。------------------ 关闭 Ole Automation Procedures -----------------------------sp_configure 'show advanced options', 1;GORECONFIGURE WITH OVERRIDE;

IF OBJECT_ID('dbo.Proc_DBA_ExportExcel') IS NOT NULL DROP PROC dbo.Proc_DBA_ExportExcel GOCREATE PROC dbo.Proc_DBA_ExportExcel @sqlstr varchar(8000),--查询语句,如果查询语句中使用了order by ,请加上top 100 perce

有的作业非常大， 可能运行时间超长，从而影响白天正常生产， 因此非常有必要加一个作业来主动停止大作业。IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].Proc_DBA_StopJob') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].P

我们平常得到的脚本往往含有GO， 这是无法用动态语句直接执行的， 但有时GO不能直接去掉（比如create view之类）， 如何处理？1. 需要按行分割表值函数：Fun_SplitByLine2. 增加一个存储即可：IF OBJECT_ID('dbo.Proc_DBA_ExecBySplitGo') IS NOT NULL DROP PROC dbo.Proc_DBA_ExecBy

IF OBJECT_ID('dbo.Fun_GetTableRows') IS NOT NULL DROP FUNCTION dbo.Fun_GetTableRowsGO-- =============================================-- Author: yenange-- Create date: 2018-01-23-- Description:

DECLARE @t TABLE( [Database Name] NVARCHAR(100) ,[Log Size (MB)] DECIMAL(15,2) ,[Log Space Used (%)] DECIMAL(15,2) ,[Status] INT)INSERT INTO @tEXEC ('DBCC SQLPERF(LOGSPACE)')SELECT * FROM @t

SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName] ,i.[index_id] AS [IndexID] ,i.[name] AS [IndexName] ,i.[type_desc] AS [IndexType] ,i.[data_space_id] AS [DatabaseSpaceID] ,f.[name

IF OBJECT_ID('dbo.[Fun_SplitByLen]') IS NOT NULL DROP FUNCTION dbo.[Fun_SplitByLen]GO-- =============================================-- Author: yenange-- Create date: 2018-01-19-- Description:

ALTER PROCEDURE [dbo].[AutoInsert] @tablename SYSNAME, @filter NVARCHAR(500), @Order NVARCHAR(500)AS DECLARE @column VARCHAR(MAX) DECLARE @columndata VARCHAR(MAX) DECLARE @sql VARCHAR(MAX

;WITH cte AS (SELECT tbl.*, CAST( CASE WHEN tbl.is_ms_shipped = 1 THEN 1 WHEN ( SELECT major_id FR...

--测试库 test--测试表 t--测试表主键 id ( PK_t )--文件组有 primary,data--希望将 t 的文件组由 primary 改成 dataUSE testGOALTER TABLE t DROP CONSTRAINT PK_tGOALTER TABLE t ADD CONSTRAINT PK_t PRIMARY KEY CLUSTERED (ID)

--总的思路就是限制对方只能执行这个存储过程，其它的操作都做不了--以 test 为例，你自己要将 test 全部替换成你自己的库------ 这一部分是在测试库加测试表和测试的存储过程 Begin ---------USE testGOIF OBJECT_ID('theTable') IS NOT NULL DROP TABLE theTableGOCREATE TABLE the

--查询哪些表没有聚集索引SELECT * FROM ( SELECT t.name AS tableName,(SELECT TOP 1 name FROM sys.indexes AS i WHERE i.type_desc='CLUSTERED' AND t.[object_id]=i.[object_id]) AS clusteredIndexName FROM sys.table

SQLServer的维护计划是强大的，特别是体现在备份、删除过期文件这个方面。可以创建文件夹、备份多个库、删除过期文件……不过配置时还是有点烦人，要选择很多东西。那用语句如何实现呢？--作业步骤1. 备份所有指定数据库DECLARE @dbs TABLE( rowNum INT IDENTITY(1, 1) PRIMARY KEY, dbName NVARCHAR

今天碰到一个数据库， 居然没有扩展名， 也是醉了， 连附加都没办法用图形界面……还是得想办法将其改为正规的扩展名才好。USE [master]GO--1. 创建测试库CREATE DATABASE [abc] CONTAINMENT = NONE ON PRIMARY ( NAME = N'abc', FILENAME = N'D:\database\abc' , SIZE

--方法一：直接附加，产生新的日志文件USE [master]GOCREATE DATABASE [AdventureWorks2014]ON (FILENAME = N'D:\database\2014\AdventureWorks2014_Data.mdf')FOR ATTACH_REBUILD_LOGGO/*文件激活失败。物理文件名称'D:\database\2014\Ad

SELECT t.name AS tableName, i.name AS pkName, STUFF((SELECT ',' + c.name FROM sys.sysindexkeys AS s2 INNER JOIN sys.[columns] AS c ON c.c

--更改表名: old_tabName 改为 new_tabNamesp_rename 'old_tabName','new_tabName'--更改列名: old_colName 改为 new_colNamesp_rename 'tabName.old_colName','new_colName','COLUMN'--更改索引名: old_indName 改为 new_indNam

SELECT TOP 10 OBJECT_NAME(qt.objectid, qt.dbId) AS procName, DB_NAME(qt.dbId) AS [db_name], qt.text AS SQL_Full, SUBSTRING( ...

原文： 点击打开链接Sql Server 日志 和 代理错误日一般在实例重启后自动切换,如果实例久未重启,将可能积累太多的日志,不方便查看.  查看错误日志大小： [sql] view plain copy--查看日志大小   EXEC xp_enumerrorlogs   --sqlserver错误日志   EXEC xp_enumerrorlogs 1 --...

SELECT b.[session_id] ,b.[open_transaction_count] ,b.[total_elapsed_time] ,a.[name] AS 'transaction_name' ,b.[command] ,a.[transaction_begin_time] ,b.[blocki

----------------------- 创建监控表 ----------------------------------创建存储过程(普通SQL也可以，但要改程序了) 记录表IF OBJECT_ID('[dbo].[DBA_proc_log]') IS NOT NULL DROP TABLE [dbo].[DBA_proc_log]GOCREATE TABLE [dbo].[DB

--准备测试表及测试数据USE tempdbGOIF OBJECT_ID('task') IS NOT NULL DROP TABLE taskGOCREATE TABLE task ( taskId INT IDENTITY(1,1) PRIMARY KEY ,taskName NVARCHAR(50) NOT NULL ,d DATETIME NOT NULL DEFAULT(...

USE tempdbGOIF OBJECT_ID('userInfo') IS NOT NULL DROP TABLE userInfoGOcreate table userInfo( userId int identity(1,1) primary key, userName nvarchar(20) not null, --姓名 必填 birthday da

IF OBJECT_ID('dbo.Fun_GetMonthDays') IS NOT NULL DROP FUNCTION dbo.Fun_GetMonthDaysGO-- =============================================-- Author: yenange-- Create date: 2018-07-03-- Description: 获...

USE newDB --新库名, 自己改GODECLARE @sourceDbName NVARCHAR(200),@targetName NVARCHAR(200)SET @sourceDbName='oldDbName' --旧库名, 自己改SET @targetName=DB_NAME()SET NOCOUNT ONDECLARE @t TABLE ( rowNu...

SELECT *FROM sys.tables AS t JOIN sys.indexes AS i ON t.[object_id] = i.[object_id] JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_idWHERE t.[object_id] IN ( ...

USE tempdbGOIF OBJECT_ID('t') IS NOT NULL DROP TABLE tGOCREATE TABLE t( struct VARCHAR(50) )GOINSERT INTO tSELECT '1.1'UNION ALL SELECT '1.2.1'UNION ALL SELECT '1.10.1' UNION ALL SELECT '...

USE tempdbGOIF OBJECT_ID('t') IS NOT NULL DROP TABLE tGOCREATE TABLE t( planId BIGINT PRIMARY KEY, dbIds VARCHAR(500))GO---- 插入测试数据 ----------- 要求：去除dbIds中的 12456 INSERT INTO t VALUES (1,'...

参考： msdn DECLARE @s VARCHAR(50)SET @s=FORMAT(GETDATE(),'yyyyMMddHHmmss')SET @s='1|'+@s;SELECT @s AS [原串],HashBytes('MD5',@s) AS ...

Create FUNCTION [dbo].[parseJSON]( @JSON NVARCHAR(MAX)) RETURNS @hierarchy TABLE ( element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and th...

IF OBJECT_ID('dbo.Fun_GetNumList') IS NOT NULL DROP FUNCTION dbo.Fun_GetNumListGO-- =============================================-- Author: yenange-- Create date: 2018-11-26-- Description: 构建序...

USE tempdbGOIF OBJECT_ID('dbo.Fun_GetStrInBracket') IS NOT NULL DROP FUNCTION dbo.Fun_GetStrInBracketGO-- =============================================-- Author: yenange-- Create date: 2018-...

----下面的查询返回过去256分钟CPU使用的历史状况，1分钟一个间隔：DECLARE @ts_now BIGINT = ( SELECT cpu_ticks / (cpu_ticks / ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK) ); SELECT TOP(256) ...

IF OBJECT_ID('dbo.Fun_CutControlChars') IS NOT NULL DROP FUNCTION dbo.Fun_CutControlCharsGO-- =============================================-- Author: yenange-- Create date: 2018-06-07-- Descri...

IF OBJECT_ID('dbo.Fun_String2ASCII') IS NOT NULL DROP FUNCTION dbo.Fun_String2ASCIIGO-- =============================================-- Author: yenange-- Create date: 2018-06-07-- Description: ...

DECLARE @path NVARCHAR(255)DECLARE @t TABLE (File_Exists INT,File_is_a_Directory INT,Parent_Directory_Exists INT)SET @path='d:\data.txt' --路径自己改SET NOCOUNT ONINSERT INTO @tEXEC XP_FILEEXIST @path