SQL Server Common script collation

Database storage query (the size of the database and the amount of data in each table in the database and the size of each row of records)

IF not EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[    Tablespaceinfo] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) CREATE TABLE Tablespaceinfo--Create a result store table ( Nameinfo varchar (+), rowsinfo INT, reserved varchar (datainfo), index_size varchar (20) , unused varchar) DELETE from Tablespaceinfo--empty data table declare @tablename VARCHAR (255)--table name declare @cmdsql varch      AR (+) 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 to @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 Tablespacein FO exec sp_spaceused @tbname ', N ' @tbnamevarchar (255) ', @tbname = @tablename FETCH NEXT from Info_cursor to @tablename END CLOSE info_cursor deallocate I Nfo_cursor Go--itlearner Note: Display database information sp_spaceused @updateusage = ' TRUE '--itlearner Note: Show table information select * FROM Tablespaceinfo  ORDER by Cast (left (Ltrim (Rtrim (reserved)), Len (Ltrim (Rtrim (reserved)))-2 as INT) DESC

 
The amount of data for each table in the database and the space occupied by each row of records

CREATE TABLE #tablespaceinfo (nameinfo varchar, rowsinfo BIGINT, reserved varchar), Datainfo varchar ( ), index_size varchar (unused), 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 to @tablename while @ @FETCH_STATUS = 0 BEGIN INSERT into #tablespaceinfo EXEC sp_spaceused @tablename FETCH NEXT from Info_cursor to @tablename END CLOSE info_cursor deallocate info_cursor- -Create a temporary table created table [#tmptb] (TableName VARCHAR, Datainfo BIGINT, Rowsinfo BIGINT, Spaceperrow as (case Rowsinf o when 0 then 0 ELSE datainfo/rowsinfo END) PERSISTED)--insert data into temp table insert into [#tmptb] ([T Ablename], [Datainfo], [rowsinfo]) SELECT [Nameinfo], Cast (Replace ([Datainfo], ' KB ', ') as BIGINT) A S ' Datainfo ', [Rowsinfo] from #tablespaceinfo ORDER by Cast (Replace (Reserved, 'KB ', ') as INT) desc--summary record SELECT [Tbspinfo].*, [TMPTB]. [Spaceperrow] The as ' per-row record probably occupies space (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]

 

database deadlock and blocking statement query

/* Function Description: Database deadlock and blocking statement query modification description: 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 (S Elect * 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 ' No blocking and deadlock information '- -return 0 END while @index <= @count BEGIN IF EXISTS (SELECT 1 from #temp_who_lock a WHE RE 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 Sele         CT ' cause database deadlock is: ' + Cast (@spid as VARCHAR (10)) + ' process number, which executes the SQL syntax as follows '; SELECT @spid, @blk DBCC INPUTBUFFER (@spid) DBCC INPUTBUFFER (@blk) END SET @[email&nbs                P;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 ' The blocking is: ' + cast (@blk as varchar (10) + ' process number, which executes the SQL syntax as follows ' ELSE SELECT ' process number spid: ' + cast (@spid as varchar (10) + ' + ' process number spid: ' + Cast (@blk as VARCHAR (10) + ' block, its current process execution of SqL syntax as follows ' PRINT (LTRIM (@spid) + ' + LTRIM (@blk)); if (@spid <> 0) BEGIN DBCC INPUTBUFFER (@spid)--END DBCC INPUTBUFFER (@blk)-- Cause blocking Statement SET @[email protected] + 1 end end DROP TABLE #temp_who_lock--return 0--kill 54

  shrinks the current database and clears the corresponding log file

/* Function Description: Shrink the current database, and clear the corresponding log file logical description: First shrink the database, followed by setting the database to Simple mode, and then in the Intercept log file, set it to a fixed size, and finally set the database for its current mode shrinkfile can specify the The size of the Truncateonly log is only suitable for shrinking data files. Clear Log Description: When the System log file abnormally increases or the backup log time is too long may affect the production of the case to use.         Description: Create by LY on 2011-12-06 */DECLARE @DB_Name varchar (50),--current database name @DBLog_Name varchar (50),   --Log file @DBRecovery_Model VARCHAR (50);  --Recovery Model 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); -----Shrink the master database 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 a MB. EXEC (' ALTER DATABASE ' [email protected]_name+ ' SET RECOVERY ' [email protected]_model+ '; '); --reset the database recovery model. GO/* Feature Description: View database Recovery Model modification Description: Create by LY on 2011-12-06 */--select Name,recovery_model_desc--from master.sys.databases-- SELECT * FROM Sysfiles

  a complete table partitioning case

Use Cubedemo GO/* Feature description: CREATE TABLE partition Test table Modify Description: Created 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) NOT NULL, salename varchar () NULL, Check Outdate DATETIME NULL, Attribute1 varchar () NULL, Attribute2 varchar (50 ) NULL, Attribute3 varchar () NULL, Attribute4 varchar () NULL, Attribute          5 varchar () NULL, ATTRIBUTE6 varchar (+) NULL, Attribute7 varchar (50)           NULL, Attribute8 varchar () NULL, ATTRIBUTE9 varchar () NULL, ATTRIBUTE10          varchar () NULL, ATTRIBUTE11 varchar (50) NULL, Attribute12 varchar NULL, CONSTRAINT Pk_Fact_salecar PRIMARY Key (Salecarid)); GO/* Function Description: Add the test data to the loop to modify the description: Create by-LY on 2011-09-11 * * BEGIN BEGIN TRAN---Start transaction declare @NUM INT; SET @NUM = 1; /*-------2009 years to import "8 million"----*/while @NUM <= 8000000 BEGIN INSERT into dbo. Fact_salecar SELECT RTRIM (Year (DATEADD (year,-2,getdate))) +rtrim (@NUM), ' Store ' +rtrim (Year (DATEADD (Year,-2,getdate ( ))) +rtrim (@NUM), DATEADD (Year,-2,getdate ()), RTRIM (Year (DATEADD (year,-2,getdate))) + ' ', RTRIM (year (DATEADD (YE Ar,-2,getdate ())) + ' RTRIM ', RTRIM (year (DATEADD (Year,-2,getdate ())) + ' The ', ' (DATEADD (Year,-2,getdate ())) + ' "RTRIM" (Year (DATEADD (year,-2,getdate)) + ' on ', RTRIM (year (DATEADD (year,-2,getdate))) + ', Rtri M (Year (DATEADD (Year,-2,getdate ())) + ' ", RTRIM (Year (DATEADD (year,-2,getdate))) + ' ', RTRIM (Year (DATEADD (year, -2,getdate ())) + ' Year,-2,getdate ', RTRIM (year (DATEADD (())) + ' Ten ', 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-year time to import "5 million"----*/while @NUM <= 5000000 BEGIN INSERT into dbo. Fact_salecar SELECT RTRIM (Year (DATEADD (year,-1,getdate))) +rtrim (@NUM), ' Store ' +rtrim (Year (DATEADD (Year,-1,getdate ( ))) +rtrim (@NUM), DATEADD (Year,-1,getdate ()), RTRIM (Year (DATEADD (year,-1,getdate))) + ' ', RTRIM (year (DATEADD (YE Ar,-1,getdate ())) + ' RTRIM ', RTRIM (year (DATEADD (Year,-1,getdate ())) + ' The ', ' (DATEADD (Year,-1,getdate ())) + ' "RTRIM" (Year (DATEADD (year,-1,getdate)) + ' on ', RTRIM (year (DATEADD (year,-1,getdate))) + ', Rtri M (Year (DATEADD (Year,-1,getdate ())) + ' ", RTRIM (Year (DATEADD (year,-1,getdate))) + ' ', RTRIM (Year (DATEADD (year, -1,getdate ())) + ' Year,-1,getdate ', RTRIM (year (DATEADD (())) + ' Ten ', RTRIM (year (DATEADD (Year,-1,getdate ())) + ' 11 ' , RTRIM (DATEADD (Year,-1,GETDATE ())) + ' 12 ';     SET @[email protected]+1;      IF @ @error <>0 BEGIN ROLLBACK TRAN RETURN;  End END; SET @NUM =1/*-------2011-year time to import "10 million"----*/while @NUM <= 10000000 BEGIN INSERT into dbo. Fact_salecar SELECT RTRIM (Year (DATEADD (year,0,getdate))) +rtrim (@NUM), ' Store ' +rtrim (Year (DATEADD (Year,0,getdate () ))) +rtrim (@NUM), DATEADD (Year,0,getdate ()), RTRIM (Year (DATEADD (year,0,getdate))) + ' ", RTRIM (year (DATEADD),            0,getdate ())) + ' RTRIM ', RTRIM (year (DATEADD (Year,0,getdate ()))) + ' ', and the Year (DATEADD (Year,0,getdate ())) + ' 04 ', RTRIM (Year (DATEADD (year,0,getdate)) + ' on ', RTRIM (year (DATEADD (Year,0,getdate ())) + ' ", RTRIM (year (D Ateadd (Year,0,getdate ())) + ' ", RTRIM (Year (DATEADD (Year,0,getdate ())) + ', RTRIM (DATEADD (Year,0,getdate ( )) + ' DATEADD ', RTRIM (year (Year,0,getdate ())) + ' Ten ', RTRIM (year (DATEADD (year,0,getdate))) + ' One ', RTRIM (year (D Ateadd (YEAR,0,GETDATE ()))) + ' 12 ';     SET @[email protected]+1;      IF @ @error <>0 BEGIN ROLLBACK TRAN RETURN; End END; COMMIT TRAN END; --select * FROM Fact_salecar/* Function Description: Create partition Table */use Pfcube GO//Function Description: Convert the normal table to the component Area table. Introduction: In the above code, we can see that this table has a general common table characteristics-there is a primary key, and this primary key         or a clustered index. As mentioned earlier, a partitioned table is a partition condition for a field, so you cannot create a clustered index other than this field.        Therefore, to convert a normal table to a constituent table, you must first delete the clustered index and then create a new clustered index that uses the partitioning scheme in the clustered index. Unfortunately, in SQL Server, if a field is both a primary key and a clustered index, you cannot simply delete the clustered index. Therefore, we can only delete the entire primary key, and then recreate a primary key, but do not set it as a clustered index when creating the primary key, as shown in the following code:/* Function Description: Create filegroup */alter DATABASE pfcube ADD Filegrou P [fg_pfcube_01] ALTER DATABASE pfcube add FILEGROUP [fg_pfcube_02] ALTER DATABASE pfcube ADD FILEGROUP [fg_pfcube_03] GO /* */* Function Description: Create file */alter DATABASE pfcube ADD file (NAME = n ' fg_pfcube_01_data ', FILENAME = N ' D:\DB\PT\FG_PFCube_01_dat A.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/* Function Description: Create partition function */create PARTITION function pt_fun_fact_salecar_checkoutdate (DATETIME) as RANGE left for VALUES (' 2010-1-1 ', ' 2011-1-1 ') GO/* Feature description: Create partition scheme */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/*------------------- To create a partitioned table------------------------function Description: Convert the normal table to the Component Area table first: Delete The primary key, create the primary key, But not set as a clustered index-------------------------------------------------------*/alter TABLE fact_salecar DROP constraint pk_fact_ Salecar ALTER TABLE fact_salecar ADD CONSTRAINT pk_fact_salecar PRIMARY KEY nonclustered (Salecarid ASC) on [PRIM ARY] GO/* Function Description: After you re-nonclustered the primary key, you can create a new clustered index for the table and use the partitioning scheme in the clustered index, as shown in the following code: Second: Create a new clustered index that uses the partitioning scheme in the clustered index */cre ATE CLUSTERED INdex Ct_fact_salecar on Fact_salecar (checkoutdate) on Pt_sch_fact_salecar_checkoutdate (CheckOutDate) GO/* Feature Description: Write query, Root According to the partition to check the effect is much faster. Benefits.. */select * from Fact_salecar WHERE year (checkoutdate) =2010--$PARTITION. Pt_fun_fact_salecar_checkoutdate ( checkoutdate) = 2

SQL Server Common script collation