Today to share their own use of SQLServer in the work of some common scripts, I hope to help you!
1, query all table structure of database
This script can be used to quickly find table fields or generate database design documents and perform database comparisons.
SELECTObj. name Specifies the name of the table, col.colorderASThe serial number, col. NameASThe column name, ISNULL (ep. [value].' ') ASColumn description, t. NameASData type,CASE WHEN col.isnullable = 1 THEN '1'
ELSE ' '
END ASNull allowed, ISNULL(comm.text,' ') ASThe 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 ;
Copy the code2. SQLServer queries the storage space occupied by database tables and index files
You can quickly query the storage space occupied by tables and indexes in a database to find out which tables occupy a large amount of storage space, facilitating database optimization.
CREATE PROCEDURE [dbo].[sys_viewTableSpace] AS BEGIN SET NOCOUNT ON; CREATE TABLE [dbo].#tableinfovarchar] (50) COLLATE Chinese_PRC_CI_AS NULL, record number [int] NULL, reserved space [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL, use 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, table number, reserved space, index occupied space, unused space) exec sp_MSforeachtable "exec sp_spaceused '? '" select* FROM #tableinfo order by tablespace desc drop table #tableinfo END exec sys_viewTABLESPACECopy the code3. Clear database log files
Database log files are usually very large, and occupy more than hundreds of GIGABytes or even terabytes. If you do not need to keep database log files all the time, you can create a database job to periodically clear database log files.
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 (N'DB_log' , 2, TRUNCATEONLY) -- Set the compressed log size to 2 MB
USE master
ALTER DATABASE DB SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE DB SET RECOVERY FULL Restore to full mode
Copy the codeSQLServer check the lock table and unlock
You can execute this script to determine whether to lock the table, and then unlock the table to query the data normally.
-- Query the locked table
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT';
Spid Table locking process. TableName Specifies the name of the locked table
To unlock the table, get the SPID and kill the shrink process
declare @spid int
Set @spid = 57 -- Table lock process
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)
Copy the code
SQLServer generates the date dimension table
The script can generate a date-dimensional data table that can solve many report query problems. Very practical.
Create table 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 --* * * *** 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: Generates time DIMENSION data begin_date: indicates the start time end_Date: indicates the 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_month2 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<=convert(date,@end_date)) begin set @v_the_date=convert(char(10),@dDate,112); -- The key value is in the yyyyMMdd set @v_THE_year =DATEPART("YYYY", @ddate); Set @v_the_quarter=DATEPART("QQ", @ddate); Set @v_the_month=DATEPART("MM", @ddate); Set @v_the_day=DATEPART("dd", @ddate); Set @v_the_week=DATEPART("WW", @ddate); Set @v_week_day=DATEPART("DW", @ddate); -- What day of the week -- Insert data into T_Date(the_date,date_name,the_year,year_name,the_quarter,quarter_name,the_month,month_name,the_week,week_name,week_day,w eek_day_name) values( @v_the_date, Convert (nvarchar (10), @ v_the_year) + 'years' + convert (nvarchar (10), @ v_the_month) +' month '+ convert (nvarchar (10), @ v_the_day) +', '. @v_the_year, convert(nvarchar(10),@v_the_year)+' year ', @v_the_quarter, Nvarchar (10) +convert(nvARCHar (10),@v_the_year)+ convert(nvARCHar (10),@v_the_quarter)+' quarter ', case when @v_the_month>=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) + 'years' + convert (nvarchar (10), @ v_the_month) +' month ', @ v_the_week, 'the first' + 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 -- DECLARE @return_value int EXEC @return_value = [dbo].[SP_CREATE_TIME_DIMENSION] SELECT 'Return Value' = @return_value GOCopy the codeIT technology sharing community
Personal blog website: Programmerblog.xyz
Programmer Productivity: Common tools for drawing flow charts Programmer productivity: Common software for organizing online notes Telecommuting: Common remote assistance software, do you know? SCM program download, ISP and basic knowledge of serial port Hardware: circuit breaker, contactor, basic knowledge of relay