[[358579]]

今天給大家分享自己在工作當(dāng)中用到的SQLServer一些常用的腳本，希望能對大家有所幫助!

1、 查詢數(shù)據(jù)庫所有表結(jié)構(gòu)

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

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

可以快速查詢數(shù)據(jù)庫中表、索引占用的存儲空間，找到哪些表占用了大量的存儲空間，便于進(jìn)行數(shù)據(jù)庫優(yōu)化。

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

3、清理數(shù)據(jù)庫日志文件

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

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

4、SQLServer查看鎖表和解鎖

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

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

5、SQLServer生成日期維度表

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

--1、創(chuàng)建數(shù)據(jù)表 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、創(chuàng)建生成日期的存儲過程 
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: 生成時間維數(shù)據(jù) 
begin_date: 開始時間 
end_date:結(jié)束時間 
*/ 
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);--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); --星期幾 
-- 插入數(shù)據(jù) 
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、執(zhí)行存儲過程生成數(shù)據(jù) 
GO 
DECLARE @return_value int 
EXEC    @return_value = [dbo].[SP_CREATE_TIME_DIMENSION] 
SELECT    'Return Value' = @return_value 
GO