数据库干货：整理SQL Server非常实用的脚本

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 ; 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.

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 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

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 --还原为完全模式 
1.
2.
3.
4.
5.
6.
7.
8.

-- 查询被锁表 
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) 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

--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_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); --星期几 
-- 插入数据 
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 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.