Shulou(Shulou.com)05/31 Report--

This article focuses on "summing up a very useful script for SQL Server". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "summing up a very useful script for SQL Server".

1. Query all the table structures of the database

Through this script, you can quickly find table fields, or generate database design documents and compare databases.

SELECT obj.name table name, col.colorder AS serial number, col.name AS column name, ISNULL (ep. [value],'') AS column description, t.name AS data type, CASE WHEN col.isnullable = 1 THEN'1' ELSE''END AS allows null, ISNULL (comm.text,'') AS default value, Coalesce (epTwo.value AS documentation FROM dbo.syscolumns col LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype inner JOIN dbo.sysobjects obj ON col.id = obj.id AND obj.xtype ='U' AND obj.status > = 0 LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description' LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo. Major_id AND epTwo.minor_id = 0 AND epTwo.name = 'MS_Description' WHERE obj.name in (SELECT ob.name FROM sys.objects AS ob LEFT OUTER JOIN sys.extended_properties AS ep ON ep.major_id = ob.object_id AND ep.class = 1 AND ep.minor_id = 0 WHERE ObjectProperty (ob.object_id) 'IsUserTable') = 1) ORDER BY obj.name

2. SQLServer queries the storage space occupied by various data tables and index files in the database

You can quickly query the storage space occupied by tables and indexes in the database, and find out which tables occupy a lot of storage space, which is convenient for database optimization.

CREATE PROCEDURE [dbo]. [sys_viewTableSpace] AS BEGIN SET NOCOUNT ON CREATE TABLE [dbo]. # tableinfo (table name [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL, number of records [int] NULL, reserved space [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL, used space [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL, index occupied space [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL Unused space [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL) insert into # tableinfo (table name, number of records, reserved space, used space, index occupied space, unused space) exec sp_MSforeachtable "exec sp_spaceused'?'" Select * from # tableinfo order by number of records desc drop table # tableinfo END-execution method exec sys_viewtablespace

3. Clean up the database log files

Database log files are generally very large, or even occupy more than a few hundred gigabytes or even T, if you do not need to keep database log files all the time, you can build a database job and clean up database log files regularly. You can use the following script.

USE master ALTER DATABASE DB SET RECOVERY SIMPLE WITH NO_WAIT ALTER DATABASE DB SET RECOVERY SIMPLE-adjust to simple mode USE DB DBCC SHRINKFILE (NumberDBMS log', 2, TRUNCATEONLY)-set the compressed log size to 2m, and you can specify USE master ALTER DATABASE DB SET RECOVERY FULL WITH NO_WAIT ALTER DATABASE DB SET RECOVERY FULL-restore to full mode

4. SQLServer check the lock table and unlock

Work encountered when the query has been unable to query the results, you can execute the script to determine whether to lock the table, and then unlock the data can be queried normally.

-- query locked table select request_session_id spid,OBJECT_NAME (resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT';-Parameter indicates spid locked table process; tableName locked table name-unlocked statement needs to get spid and kill shrinking table process declare @ spid int Set @ spid = 57-- locked table process declare @ sql varchar (1000) set @ sql='kill'+ cast (@ spid as varchar) exec (@ sql)

5. SQLServer generates date dimension table

The script can generate a date-dimensional data table through which many report query problems can be solved. Very practical.

Create datasheet T_Date CREATE TABLE [dbo]. [T_Date] ([the_date] [int] NOT NULL, [date_name] [nvarchar] (30) NULL, [the_year] [int] NULL, [year_name] [nvarchar] (30) NULL, [the_quarter] [int] NULL, [quarter_name] [nvarchar] (30) NULL, [the_month] [int] NULL [month_name] [nvarchar] (30) NULL, [the_week] [int] NULL, [week_name] [nvarchar] (30) NULL, [week_day] [int] NULL, [week_day_name] [nvarchar] (30) NULL, CONSTRAINT [PK_T_Date] PRIMARY KEY CLUSTERED ([the_date] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO-- 2, create the stored procedure GO / * Object: StoredProcedure [dbo]. [SP_CREATE_TIME_DIMENSION] * / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo]. [SP_CREATE_TIME_DIMENSION] @ begin_date nvarchar (50) = '2015-01-01' @ end_date nvarchar (50) = '2030-12-31' as / * SP_CREATE_TIME_DIMENSION: generate time dimension data begin_date: start time end_date: end time * / declare @ dDate date=convert (date,@begin_date), @ v_the_date varchar (10), @ v_the_year varchar (4), @ v_the_quarter varchar (2), @ v_the_month varchar (10), @ v_the_month3 varchar (2) @ v_the_week varchar (2), @ v_the_day varchar (10), @ v_the_day2 varchar (2), @ v_week_day nvarchar (10), @ adddays int=1 WHILE (@ dDate=10 then convert (int, (convert (nvarchar (10), @ v_the_year) + convert (nvarchar (10), @ v_the_month)) else convert (int,convert (nvarchar (10), @ v_the_year) +'0' + convert (nvarchar (10), @ v_the_month)) end, convert (nvarchar (10), @ v_the_year) + 'year' + convert (nvarchar (10), @ v_the_month) + month, @ v_the_week '1st'+ convert (nvarchar (10), @ v_the_week) + 'week', @ v_week_day, case @ v_week_day-1 when 1 then 'Monday' when 2 then 'Tuesday' when 3 then 'Wednesday' when 4 then 'Thursday' when 5 then 'Friday' when 6 then 'Saturday' when 0 then 'Sunday' else 'end) Set @ dDate=dateadd (day,@adddays,@dDate); continue if @ dDate=dateadd (day,-1,convert (date,@end_date)) break end-3. Execute stored procedure generation data GO DECLARE @ return_value int EXEC @ return_value = [dbo]. [SP_CREATE_TIME_DIMENSION] SELECT 'Return Value' = @ return_value GO so far, I believe you have a deeper understanding of "summing up SQL Server very useful scripts", you might as well do it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.