Two Sql script for counting tables and using snapshot

 

 

------Script 1 :count all tables in one Database

SET   NOCOUNT   ON  

  DECLARE   @db   VARCHAR(20)  

  SET   @db=db_name()  

  GO  

   

  CREATE   TABLE   #tblSpace  

  (  

 数据表名称  varchar(250)   null,  

 记录笔数  int   null,  

 保留空间  varchar(15)   null,  

 数据使用空间  varchar(15)   null,  

 索引使用空间  varchar(15)   null,  

 未使用空间  varchar(15)   null  

  )  

  DECLARE   @tblname   varchar(250)  

  DECLARE   curTbls   CURSOR   FOR  

  SELECT   TABLE_NAME   FROM   Information_Schema.TABLES  

  WHERE   TABLE_TYPE='BASE TABLE'  

   

  OPEN   CurTbls  

  FETCH   NEXT   FROM   curTbls   INTO   @tblName  

   

  WHILE   @@FETCH_STATUS=0  

  BEGIN  

  INSERT   #tblSpace   EXEC   sp_spaceused   @tblName  

  FETCH   NEXT   FROM   curTbls   INTO   @tblName  

  END  

  CLOSE   CurTbls  

  DEALLOCATE   curTbls  

   

  SELECT   *   FROM   #tblSpace   ORDER   BY   记录笔数  DESC  

   

  DROP   TABLE   #tblSpace  

 

 

 

--script 2 create/restore/delete snapshot for on Database in Ent edition

 

For execution_arc_ARCTest 数据库

 

--创建

CREATE DATABASE execution_arc_ARCTest_snapshot ON

( NAME = execution_arc_Data, FILENAME =

'D:"MesDBArcTest"execution_arc_ARCTest_snapshot.ss' )

AS SNAPSHOT OF execution_arc_ARCTest;

GO

 

--还原

USE master;

RESTORE DATABASE execution_arc_ARCTest from

DATABASE_SNAPSHOT = 'execution_arc_ARCTest_snapshot';

GO

 

--删除

USE [master]

GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'execution_arc_ARCTest_snapshot')

DROP DATABASE [execution_arc_ARCTest_snapshot]

 

 

 

 

For execution_ARCTest 数据库

 

--创建

CREATE DATABASE execution_ARCTest_snapshot ON

( NAME = execution_Data, FILENAME = 'D:"MesDBArcTest"execution_Data.ss'),

(NAME = exeCus_Data, FILENAME = 'D:"MesDBArcTest"exeCus_Data.ss' ),

(NAME = exeMat_Data, FILENAME = 'D:"MesDBArcTest"exeMat_Data.ss' ),

(NAME = exeQua_Data, FILENAME = 'D:"MesDBArcTest"exeQua_Data.ss' ),

(NAME = exeDat_Data, FILENAME = 'D:"MesDBArcTest"exeDat_Data.ss' )

AS SNAPSHOT OF execution_ARCTest;

GO

 

--还原

USE master;

RESTORE DATABASE execution_ARCTest from

DATABASE_SNAPSHOT = 'execution_ARCTest_snapshot';

GO

 

--删除

USE [master]

GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'execution_ARCTest_snapshot')

DROP DATABASE [execution_ARCTest_snapshot]