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;

    

    /[i]-------2009年的时间导入 【800万条】---- [/i]/

    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

    /[i]-------2010年的时间导入  【500万】---- [/i]/

    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

    /[i]-------2011年的时间导入  【1000万】---- [/i]/

    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