SQLSERVER常用指令碼整理

標籤：des   blog   io   os   使用   ar   strong   for   檔案   

資料庫儲存空間查詢（資料庫的大小及資料庫中各個表的資料量和每行記錄大小）

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 @[email protected] + 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 @[email protected] + 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 ‘[email protected]_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 ‘[email protected]_Name+‘ SET RECOVERY ‘[email protected]_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 @[email protected]+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 @[email protected]+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 @[email protected]+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

 

SQLSERVER常用指令碼整理