總結SQL Server非常實用的腳本

本篇內容主要講解“總結SQL Server非常實用的腳本”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“總結SQL Server非常實用的腳本”吧!

1、 查詢數據庫所有表結構

通過該腳本可以快速查找表字段，或者生成數據庫設計文檔、進行數據庫對比。

SELECT obj.name 表名, col.colorder AS 序號 , col.name AS 列名 , ISNULL(ep.[value], '') AS 列說明 , t.name AS 數據類型 , CASE WHEN col.isnullable = 1 THEN '1' ELSE '' END AS 允許空 , ISNULL(comm.text, '') AS 默認值, 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 查詢數據庫各個數據表、索引文件占用的存儲空間

可以快速查詢數據庫中表、索引占用的存儲空間，找到哪些表占用了大量的存儲空間，便于進行數據庫優化。

CREATE PROCEDURE [dbo].[sys_viewTableSpace] AS   BEGIN   SET NOCOUNT ON;   CREATE TABLE [dbo].#tableinfo(  表名 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,  記錄數 [int] NULL,  預留空間 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,  使用空間 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,  索引占用空間 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,  未用空間 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL )   insert into #tableinfo(表名, 記錄數, 預留空間, 使用空間, 索引占用空間, 未用空間) exec sp_MSforeachtable "exec sp_spaceused '?'"   select * from #tableinfo order by 記錄數 desc   drop table #tableinfo   END -- 執行方法 exec sys_viewtablespace

3、清理數據庫日志文件

數據庫日志文件一般都會非常大，甚至占用超過幾百G甚至上T，如果不需要進行一直保留數據庫日志文件，可以建一個數據庫作業，定時清理數據庫日志文件，具體可以采用下面的腳本。

USE master  ALTER DATABASE DB SET RECOVERY SIMPLE WITH NO_WAIT  ALTER DATABASE DB SET RECOVERY SIMPLE --調整為簡單模式  USE DB  DBCC SHRINKFILE (N'DB_log' , 2, TRUNCATEONLY) --設置壓縮后的日志大小為2M，可以自行指定  USE master  ALTER DATABASE DB SET RECOVERY FULL WITH NO_WAIT  ALTER DATABASE DB SET RECOVERY FULL --還原為完全模式

4、SQLServer查看鎖表和解鎖

工作中遇到查詢的時候一直查詢不出來結果，可以執行該腳本判斷是否鎖表，然后解鎖就可以正常查詢數據了。

-- 查詢被鎖表 select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName    from   sys.dm_tran_locks where resource_type='OBJECT'; --參數說明 spid   鎖表進程 ；tableName   被鎖表名 -- 解鎖語句 需要拿到spid然后殺掉縮表進程 declare @spid  int  Set @spid  = 57 --鎖表進程 declare @sql varchar(1000) set @sql='kill '+cast(@spid  as varchar) exec(@sql)

5、SQLServer生成日期維度表

該腳本可以生成一個日期維度的數據表，通過該數據表可以解決很多報表查詢問題。非常實用。

--1、創建數據表 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、創建生成日期的存儲過程 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: 生成時間維數據 begin_date: 開始時間 end_date:結束時間 */ 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<=convert(date,@end_date)) begin set @v_the_date=convert(char(10),@dDate,112);--key值格式為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); --星期幾 -- 插入數據 insert 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,week_day_name) values( @v_the_date, convert(nvarchar(10),@v_the_year)+'年'+convert(nvarchar(10),@v_the_month)   +'月'+convert(nvarchar(10),@v_the_day)+'日', @v_the_year, convert(nvarchar(10),@v_the_year)+'年', @v_the_quarter, convert(nvarchar(10),@v_the_year)+'年'+convert(nvarchar(10),@v_the_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)+'年'+convert(nvarchar(10),@v_the_month)+'月', @v_the_week ,'第'+convert(nvarchar(10),@v_the_week)+'周', @v_week_day, case @v_week_day-1  when 1 then '星期一'  when 2 then '星期二'  when 3 then '星期三' when 4 then '星期四'  when 5 then '星期五'  when 6 then '星期六' when 0 then '星期日' else '' end ); set @dDate=dateadd(day,@adddays,@dDate); continue if @dDate=dateadd(day,-1,convert(date,@end_date)) break end   -- 3、執行存儲過程生成數據 GO DECLARE @return_value int EXEC    @return_value = [dbo].[SP_CREATE_TIME_DIMENSION] SELECT    'Return Value' = @return_value GO

到此，相信大家對“總結SQL Server非常實用的腳本”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！