SQLSERVER常用脚本整理
数据库存储空间查询（数据库的大小及数据库中各个表的数据量和每行记录大小）
IF NOT EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = Object_id(N'[dbo].[tablespaceinfo]')
AND Objectproperty(id, N'IsUserTable') = 1)
CREATE TABLE tablespaceinfo --创建结果存储表
(
nameinfo VARCHAR(50),
rowsinfo INT,
reserved VARCHAR(20),
datainfo VARCHAR(20),
index_size VARCHAR(20),
unused VARCHAR(20)
)
DELETE FROM tablespaceinfo --清空数据表
DECLARE @tablename VARCHAR(255) --表名称
DECLARE @cmdsql VARCHAR(500)
DECLARE Info_cursor CURSOR FOR
SELECT o.name
FROM dbo.sysobjects o
WHERE Objectproperty(o.id, N'IsTable') = 1
AND o.name NOT LIKE N'#%%'
ORDER BY o.name
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = Object_id(@tablename)
AND Objectproperty(id, N'IsUserTable') = 1)
EXECUTE Sp_executesql
N'insert into tablespaceinfo exec sp_spaceused @tbname',
N'@tbname varchar(255)',
@tbname = @tablename
FETCH NEXT FROM Info_cursor INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
--itlearner注：显示数据库信息
Sp_spaceused @updateusage = 'TRUE'
--itlearner注：显示表信息
SELECT *
FROM tablespaceinfo
ORDER BY Cast(LEFT(Ltrim(Rtrim(reserved)), Len(Ltrim(Rtrim(reserved))) - 2) AS INT) DESC
数据库中各个表的数据量和每行记录所占用空间
CREATE TABLE #tablespaceinfo
(
nameinfo VARCHAR(500),
rowsinfo BIGINT,
reserved VARCHAR(20),
datainfo VARCHAR(20),
index_size VARCHAR(20),
unused VARCHAR(20)
)
DECLARE @tablename VARCHAR(255);
DECLARE Info_cursor CURSOR FOR
SELECT '[' + [name] + ']'
FROM sys.tables
WHERE type = 'U';
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tablespaceinfo
EXEC Sp_spaceused @tablename
FETCH NEXT FROM Info_cursor INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
--创建临时表
CREATE TABLE [#tmptb]
(
TableName VARCHAR(50),
DataInfo BIGINT,
RowsInfo BIGINT,
Spaceperrow AS ( CASE RowsInfo
WHEN 0 THEN 0
ELSE DataInfo / RowsInfo
END ) PERSISTED
)
--插入数据到临时表
INSERT INTO [#tmptb]
([TableName],
[DataInfo],
[RowsInfo])
SELECT [nameinfo],
Cast(Replace([datainfo], 'KB', '') AS BIGINT) AS 'datainfo',
[rowsinfo]
FROM #tablespaceinfo
ORDER BY Cast(Replace(reserved, 'KB', '') AS INT) DESC
--汇总记录
SELECT [tbspinfo].*,
[tmptb].[Spaceperrow] AS '每行记录大概占用空间（KB）'
FROM [#tablespaceinfo] AS tbspinfo,
[#tmptb] AS tmptb
WHERE [tbspinfo].[nameinfo] = [tmptb].[TableName]
ORDER BY Cast(Replace([tbspinfo].[reserved], 'KB', '') AS INT) DESC
DROP TABLE [#tablespaceinfo]
DROP TABLE [#tmptb]
数据库死锁与阻塞语句查询
/*
功能说明: 数据库死锁与阻塞语句查询
修改说明：Modfiy BY LY 2013-11-22
*/
DECLARE @spid INT
DECLARE @blk INT
DECLARE @count INT
DECLARE @index INT
DECLARE @lock TINYINT
SET @lock=0
CREATE TABLE #temp_who_lock
(
id INT IDENTITY(1, 1),
spid INT,
blk INT
)
--if @@error<>0 return @@error
INSERT INTO #temp_who_lock
(spid,
blk)
SELECT 0,
blocked
FROM (SELECT *
FROM master..sysprocesses
WHERE blocked > 0)a
WHERE NOT EXISTS(SELECT *
FROM master..sysprocesses
WHERE a.blocked = spid
AND blocked > 0)
UNION
SELECT spid,
blocked
FROM master..sysprocesses
WHERE blocked > 0
--if @@error<>0 return @@error
SELECT @count = Count(*),
@index = 1
FROM #temp_who_lock
--select @count,@index
--if @@error<>0 return @@error
IF @count = 0
BEGIN
SELECT '没有阻塞和死锁信息'
--return 0
END
WHILE @index <= @count
BEGIN
IF EXISTS(SELECT 1
FROM #temp_who_lock a
WHERE id > @index
AND EXISTS(SELECT 1
FROM #temp_who_lock
WHERE id <= @index
AND a.blk = spid))
BEGIN
SET @lock=1
SELECT @spid = spid,
@blk = blk
FROM #temp_who_lock
WHERE id = @index
SELECT '引起数据库死锁的是: ' + Cast(@spid AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' ;
SELECT @spid,
@blk
DBCC inputbuffer(@spid)
DBCC inputbuffer(@blk)
END
SET @index=@index + 1
END
IF @lock = 0
BEGIN
SET @index=1
WHILE @index <= @count
BEGIN
SELECT @spid = spid,
@blk = blk
FROM #temp_who_lock
WHERE id = @index
IF @spid = 0
SELECT '引起阻塞的是:' + Cast(@blk AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
ELSE
SELECT '进程号SPID：' + Cast(@spid AS VARCHAR(10)) + '被' + '进程号SPID：' + Cast(@blk AS VARCHAR(10)) + '阻塞,其当前进程执行的SQL语法如下'
PRINT ( LTRIM(@spid) + ''+ LTRIM(@blk));
if(@spid <> 0)
BEGIN
DBCC inputbuffer(@spid) --
END
DBCC inputbuffer(@blk) --引起阻塞语句
SET @index=@index + 1
END
END
DROP TABLE #temp_who_lock
--return 0
--KILL 54
收缩当前的数据库，并清除对应的日志文件
/*
功能说明：收缩当前的数据库，并清除对应的日志文件
逻辑说明：首先收缩数据库，其次设置数据库为简单模式，然后在截取日志文件，
设置它为固定大小，最后设置数据库为它当前的模式
SHRINKFILE 可以指定收缩日志的大小
TRUNCATEONLY 只适用于数据文件的收缩。
清除日志说明:当系统的日志文件异常增大或者备份LOG时间太长可能影响生产的情况下使用。
修改说明：Create by LY on 2011-12-06
*/
DECLARE @DB_Name VARCHAR(50), --当前数据库名称
@DBLog_Name VARCHAR(50), --日志文件
@DBRecovery_Model VARCHAR(50); --恢复模型
SELECT @DBLog_Name = s.name
FROM sys.master_files AS s
WHERE ( s.type = 1
AND s.database_id = DB_ID())
SELECT @DB_Name = DB_NAME();
SELECT @DBRecovery_Model = recovery_model_desc
FROM master.sys.databases
WHERE name = DB_NAME();
DBCC SHRINKDATABASE(@DB_Name); -----收缩主数据库
EXEC('ALTER DATABASE '+@DB_Name+' SET RECOVERY SIMPLE;'); --Truncate the log by changing the database recovery model to SIMPLE.
DBCC SHRINKFILE (@DBLog_Name); -- Shrink the truncated log file to 50 MB.
EXEC('ALTER DATABASE '+@DB_Name+' SET RECOVERY '+@DBRecovery_Model+';'); --Reset the database recovery model.
GO
/*
功能说明：查看数据库的恢复模型
修改说明：Create by LY on 2011-12-06
*/
--SELECT name,recovery_model_desc
--FROM master.sys.databases
--select * from sysfiles
一个完整的表分区案例
USE CubeDemo
GO
/*
功能说明： 创建表分区测试表
修改说明： Create by LY on 2011-09-11
*/
IF EXISTS (SELECT 1
FROM SYSOBJECTS
WHERE id = OBJECT_ID('Fact_SaleCar')
AND type = 'U')
BEGIN
DROP TABLE Fact_SaleCar
END
GO
CREATE TABLE [dbo].Fact_SaleCar
(
SaleCarId VARCHAR(20) NOT NULL,
SaleName VARCHAR(50) NULL,
CheckOutDate DATETIME NULL,
Attribute1 VARCHAR(50) NULL,
Attribute2 VARCHAR(50) NULL,
Attribute3 VARCHAR(50) NULL,
Attribute4 VARCHAR(50) NULL,
Attribute5 VARCHAR(50) NULL,
Attribute6 VARCHAR(50) NULL,
Attribute7 VARCHAR(50) NULL,
Attribute8 VARCHAR(50) NULL,
Attribute9 VARCHAR(50) NULL,
Attribute10 VARCHAR(50) NULL,
Attribute11 VARCHAR(50) NULL,
Attribute12 VARCHAR(50) NULL,
CONSTRAINT PK_Fact_SaleCar PRIMARY key (SaleCarId)
);
GO
/*
功能说明： 用循环加入测试数据
修改说明： Create by LY on 2011-09-11
*/
BEGIN
BEGIN TRAN ---开始事务
DECLARE @NUM INT;
SET @NUM=1;
/*-------2009年的时间导入 【800万条】---- */
WHILE @NUM <= 8000000
BEGIN
INSERT INTO dbo.Fact_SaleCar
SELECT RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+RTRIM(@NUM),'商店'+RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+RTRIM(@NUM),DATEADD(YEAR,-2,GETDATE()),
RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+'01',RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+'02',
RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+'03',RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+'04',
RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+'05',RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+'06',
RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+'07',RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+'08',
RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+'09',RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+'10',
RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+'11',RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+'12';
SET @NUM=@NUM+1;
IF @@error<>0
BEGIN
ROLLBACK TRAN
RETURN;
END
END;
SET @NUM=1
/*-------2010年的时间导入 【500万】---- */
WHILE @NUM <= 5000000
BEGIN
INSERT INTO dbo.Fact_SaleCar
SELECT RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+RTRIM(@NUM),'商店'+RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+RTRIM(@NUM),DATEADD(YEAR,-1,GETDATE()),
RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+'01',RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+'02',
RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+'03',RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+'04',
RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+'05',RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+'06',
RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+'07',RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+'08',
RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+'09',RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+'10',
RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+'11',RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+'12';
SET @NUM=@NUM+1;
IF @@error<>0
BEGIN
ROLLBACK TRAN
RETURN;
END
END;
SET @NUM=1
/*-------2011年的时间导入 【1000万】---- */
WHILE @NUM <= 10000000
BEGIN
INSERT INTO dbo.Fact_SaleCar
SELECT RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+RTRIM(@NUM),'商店'+RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+RTRIM(@NUM),DATEADD(YEAR,0,GETDATE()),
RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+'01',RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+'02',
RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+'03',RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+'04',
RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+'05',RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+'06',
RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+'07',RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+'08',
RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+'09',RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+'10',
RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+'11',RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+'12';
SET @NUM=@NUM+1;
IF @@error<>0
BEGIN
ROLLBACK TRAN
RETURN;
END
END;
COMMIT TRAN
END;
--SELECT * FROM Fact_SaleCar
/*
功能说明：创建分区表
*/
USE PFCube
GO
/*
功能说明:将普通表转换成分区表 .
介绍: 在以上代码中，我们可以看出，这个表拥有一般普通表的特性——有主键，同时这个主键还是聚集索引。
前面说过，分区表是以某个字段为分区条件，所以，除了这个字段以外的其他字段，是不能创建聚集
索引的。因此，要想将普通表转换成分区表，就必须要先删除聚集索引，然后再创建一个新的聚集索
引，在该聚集索引中使用分区方案。
可惜的是，在SQL Server中，如果一个字段既是主键又是聚集索引时，并不能仅仅删除聚集索引。因此，
我们只能将整个主键删除，然后重新创建一个主键，只是在创建主键时，不将其设为聚集索引，如以下
代码所示：
*/
/*
功能说明:创建文件组
*/
ALTER DATABASE PFCube
ADD FILEGROUP [FG_PFCube_01]
ALTER DATABASE PFCube
ADD FILEGROUP [FG_PFCube_02]
ALTER DATABASE PFCube
ADD FILEGROUP [FG_PFCube_03]
GO
/*
*/
/*
功能说明:创建文件
*/
ALTER DATABASE PFCube
ADD FILE
(
NAME = N'FG_PFCube_01_data',
FILENAME = N'D:\DB\PT\FG_PFCube_01_data.ndf',
SIZE = 30MB,
FILEGROWTH = 10%
)
TO FILEGROUP [FG_PFCube_01];
ALTER DATABASE PFCube
ADD FILE
(
NAME=N'FG_PFCube_02_date',
FILENAME=N'D:\DB\PT\FG_PFCube_02_data.ndf',
SIZE=30MB,
FILEGROWTH=10%
)
TO FILEGROUP [FG_PFCube_02];
ALTER DATABASE PFCube
ADD FILE
(
NAME=N'FG_PFCube_03_date',
FILENAME=N'D:\DB\PT\FG_PFCube_03_data.ndf',
SIZE=30MB,
FILEGROWTH=10%
)
TO FILEGROUP [FG_PFCube_03];
GO
/*
功能说明:创建分区函数
*/
CREATE PARTITION FUNCTION
PT_Fun_Fact_SaleCar_CheckOutDate(DATETIME) AS
RANGE LEFT
FOR VALUES('2010-1-1','2011-1-1')
GO
/*
功能说明:创建分区方案
*/
CREATE PARTITION SCHEME
PT_Sch_Fact_SaleCar_CheckOutDate AS
PARTITION PT_Fun_Fact_SaleCar_CheckOutDate
TO([FG_PFCube_01],[FG_PFCube_02],[FG_PFCube_03])
GO
/*-------------------创建分区表------------------------
功能说明:将普通表转换成分区表
首先:删掉主键,创建主键，但不设为聚集索引
-------------------------------------------------------*/
ALTER TABLE Fact_SaleCar DROP constraint PK_Fact_SaleCar
ALTER TABLE Fact_SaleCar ADD CONSTRAINT PK_Fact_SaleCar PRIMARY KEY NONCLUSTERED
(
SaleCarId ASC
) ON [PRIMARY]
GO
/*
功能说明: 在重新非聚集主键之后，就可以为表创建一个新的聚集索引，
并且在这个聚集索引中使用分区方案，如以下代码所示：
其次:创建一个新的聚集索引，在该聚集索引中使用分区方案
*/
CREATE CLUSTERED INDEX CT_Fact_SaleCar ON Fact_SaleCar(CheckOutDate)
ON PT_Sch_Fact_SaleCar_CheckOutDate(CheckOutDate)
GO
/*
功能说明:写查询，根据分区来查效果果然快多了。好处。。
*/
SELECT * FROM Fact_SaleCar
WHERE
YEAR(CheckOutDate)=2010
--$PARTITION.PT_Fun_Fact_SaleCar_CheckOutDate(CheckOutDate) = 2
