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

数据库 SQL Server
今天给大家分享自己在工作当中用到的SQLServer一些常用的脚本,希望能对大家有所帮助!

[[358579]]

今天给大家分享自己在工作当中用到的SQLServer一些常用的脚本,希望能对大家有所帮助!

1、 查询数据库所有表结构

通过该脚本可以快速查找表字段,或者生成数据库设计文档、进行数据库对比。

 

  1. SELECT obj.name 表名, 
  2. col.colorder AS 序号 , 
  3. col.name AS 列名 , 
  4. ISNULL(ep.[value], ''AS 列说明 , 
  5. t.name AS 数据类型 , 
  6. CASE WHEN col.isnullable = 1 THEN '1' 
  7. ELSE '' 
  8. END AS 允许空 , 
  9. ISNULL(comm.text, ''AS 默认值, 
  10. Coalesce(epTwo.value, ''AS documentation 
  11. FROM dbo.syscolumns col 
  12. LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype 
  13. inner JOIN dbo.sysobjects obj ON col.id = obj.id 
  14. AND obj.xtype = 'U' 
  15. AND obj.status >= 0 
  16. LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id 
  17. LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id 
  18. AND col.colid = ep.minor_id 
  19. AND ep.name = 'MS_Description' 
  20. LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id 
  21. AND epTwo.minor_id = 0 
  22. AND epTwo.name = 'MS_Description' 
  23. WHERE obj.name in
  24. SELECT 
  25. ob.name  
  26. FROM sys.objects AS ob 
  27. LEFT OUTER JOIN sys.extended_properties AS ep 
  28. ON ep.major_id = ob.object_id 
  29. AND ep.class = 1 
  30. AND ep.minor_id = 0 
  31. WHERE ObjectProperty(ob.object_id, 'IsUserTable') = 1  
  32. ORDER BY obj.name ; 

2、SQLServer 查询数据库各个数据表、索引文件占用的存储空间

可以快速查询数据库中表、索引占用的存储空间,找到哪些表占用了大量的存储空间,便于进行数据库优化。

 

  1. CREATE PROCEDURE [dbo].[sys_viewTableSpace] 
  2. AS  
  3.  
  4. BEGIN  
  5.  
  6. SET NOCOUNT ON 
  7.  
  8. CREATE TABLE [dbo].#tableinfo( 
  9.  表名 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
  10.  记录数 [intNULL
  11.  预留空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
  12.  使用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
  13.  索引占用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
  14.  未用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL 
  15.  
  16.  
  17. insert into #tableinfo(表名, 记录数, 预留空间, 使用空间, 索引占用空间, 未用空间) 
  18. exec sp_MSforeachtable "exec sp_spaceused '?'"  
  19.  
  20. select * from #tableinfo 
  21. order by 记录数 desc  
  22.  
  23. drop table #tableinfo  
  24.  
  25. END 
  26. -- 执行方法 
  27. exec sys_viewtablespace 

3、清理数据库日志文件

数据库日志文件一般都会非常大,甚至占用超过几百G甚至上T,如果不需要进行一直保留数据库日志文件,可以建一个数据库作业,定时清理数据库日志文件,具体可以采用下面的脚本。

 

  1. USE master  
  2. ALTER DATABASE DB SET RECOVERY SIMPLE WITH NO_WAIT  
  3. ALTER DATABASE DB SET RECOVERY SIMPLE --调整为简单模式  
  4. USE DB  
  5. DBCC SHRINKFILE (N'DB_log' , 2, TRUNCATEONLY) --设置压缩后的日志大小为2M,可以自行指定  
  6. USE master  
  7. ALTER DATABASE DB SET RECOVERY FULL WITH NO_WAIT  
  8. ALTER DATABASE DB SET RECOVERY FULL --还原为完全模式 

4、SQLServer查看锁表和解锁

工作中遇到查询的时候一直查询不出来结果,可以执行该脚本判断是否锁表,然后解锁就可以正常查询数据了。

 

  1. -- 查询被锁表 
  2. select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName    
  3. from   sys.dm_tran_locks where resource_type='OBJECT'
  4. --参数说明 spid   锁表进程 ;tableName   被锁表名 
  5. -- 解锁语句 需要拿到spid然后杀掉缩表进程 
  6. declare @spid  int  
  7. Set @spid  = 57 --锁表进程 
  8. declare @sql varchar(1000) 
  9. set @sql='kill '+cast(@spid  as varchar
  10. exec(@sql) 

5、SQLServer生成日期维度表

该脚本可以生成一个日期维度的数据表,通过该数据表可以解决很多报表查询问题。非常实用。

 

  1. --1、创建数据表 T_Date 
  2. CREATE TABLE [dbo].[T_Date]( 
  3. [the_date] [intNOT NULL
  4. [date_name] [nvarchar](30) NULL
  5. [the_year] [intNULL
  6. [year_name] [nvarchar](30) NULL
  7. [the_quarter] [intNULL
  8. [quarter_name] [nvarchar](30) NULL
  9. [the_month] [intNULL
  10. [month_name] [nvarchar](30) NULL
  11. [the_week] [intNULL
  12. [week_name] [nvarchar](30) NULL
  13. [week_day] [intNULL
  14. [week_day_name] [nvarchar](30) NULL
  15. CONSTRAINT [PK_T_Date] PRIMARY KEY CLUSTERED  
  16. [the_date] ASC 
  17. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,  
  18.        IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
  19. ON [PRIMARY
  20. ON [PRIMARY
  21. GO 
  22.  
  23.  
  24. -- 2、创建生成日期的存储过程 
  25. GO 
  26. /****** Object: StoredProcedure [dbo].[SP_CREATE_TIME_DIMENSION]  ******/ 
  27. SET ANSI_NULLS ON 
  28. GO 
  29. SET QUOTED_IDENTIFIER ON 
  30. GO 
  31.  
  32.  
  33. CREATE PROCEDURE [dbo].[SP_CREATE_TIME_DIMENSION] 
  34. @begin_date nvarchar(50)='2015-01-01' , 
  35. @end_date nvarchar(50)='2030-12-31' 
  36. as 
  37. /* 
  38. SP_CREATE_TIME_DIMENSION: 生成时间维数据 
  39. begin_date: 开始时间 
  40. end_date:结束时间 
  41. */ 
  42. declare  
  43. @dDate date=convert(date,@begin_date), 
  44. @v_the_date varchar(10), 
  45. @v_the_year varchar(4), 
  46. @v_the_quarter varchar(2), 
  47. @v_the_month varchar(10), 
  48. @v_the_month2 varchar(2), 
  49. @v_the_week varchar(2), 
  50. @v_the_day varchar(10), 
  51. @v_the_day2 varchar(2), 
  52. @v_week_day nvarchar(10), 
  53. @adddays int=1; 
  54. WHILE (@dDate<=convert(date,@end_date)) 
  55. begin 
  56. set @v_the_date=convert(char(10),@dDate,112);--key值格式为yyyyMMdd 
  57. set @v_the_year=DATEPART("YYYY",@dDate);--年份 
  58. set @v_the_quarter=DATEPART("QQ",@dDate);--季度 
  59. set @v_the_month=DATEPART("MM",@dDate);--月份(字符型) 
  60. set @v_the_day=DATEPART("dd",@dDate);--日(字符型) 
  61. set @v_the_week=DATEPART("WW",@dDate);--年的第几周 
  62. set @v_week_day=DATEPART("DW",@dDate); --星期几 
  63. -- 插入数据 
  64. insert into T_Date(the_date,date_name,the_year,year_name,the_quarter, 
  65.  quarter_name,the_month,month_name,the_week,week_name,week_day,week_day_name) 
  66. values
  67. @v_the_date, 
  68. convert(nvarchar(10),@v_the_year)+'年'+convert(nvarchar(10),@v_the_month) 
  69.   +'月'+convert(nvarchar(10),@v_the_day)+'日'
  70. @v_the_year, 
  71. convert(nvarchar(10),@v_the_year)+'年'
  72. @v_the_quarter, 
  73. convert(nvarchar(10),@v_the_year)+'年'+convert(nvarchar(10),@v_the_quarter)+'季度'
  74. case when @v_the_month>=10 then  
  75. convert(int,(convert(nvarchar(10),@v_the_year)+convert(nvarchar(10),@v_the_month))) 
  76. else convert(int,convert(nvarchar(10),@v_the_year)+'0' 
  77.              +convert(nvarchar(10),@v_the_month)) end
  78. convert(nvarchar(10),@v_the_year)+'年'+convert(nvarchar(10),@v_the_month)+'月'
  79. @v_the_week 
  80. ,'第'+convert(nvarchar(10),@v_the_week)+'周'
  81. @v_week_day, 
  82. case @v_week_day-1  
  83. when 1 then '星期一'  
  84. when 2 then '星期二'  
  85. when 3 then '星期三' 
  86. when 4 then '星期四'  
  87. when 5 then '星期五'  
  88. when 6 then '星期六' 
  89. when 0 then '星期日' 
  90. else '' end 
  91. ); 
  92. set @dDate=dateadd(day,@adddays,@dDate); 
  93. continue 
  94. if @dDate=dateadd(day,-1,convert(date,@end_date)) 
  95. break 
  96. end 
  97.  
  98.  
  99. -- 3、执行存储过程生成数据 
  100. GO 
  101. DECLARE @return_value int 
  102. EXEC    @return_value = [dbo].[SP_CREATE_TIME_DIMENSION] 
  103. SELECT    'Return Value' = @return_value 
  104. GO 

 

责任编辑:未丽燕 来源: 今日头条
相关推荐

2021-01-08 16:27:52

SQL数据库开发

2010-07-01 11:49:13

SQL Server

2010-07-15 17:28:50

SQL Server

2010-07-08 11:05:14

SQL Server数

2011-03-18 13:23:47

SQL ServerOracle

2011-03-24 09:07:11

SQL Server数备份

2011-03-24 09:45:34

SQL Server数恢复

2011-03-24 09:24:08

SQL Server数还原

2020-08-21 10:25:02

PythonWget

2020-09-14 11:10:43

Python代码

2018-12-12 19:10:01

Oracle数据库自动备份

2021-05-17 06:57:34

SQLServer数据库

2010-09-14 09:53:52

sql server还

2021-03-28 10:16:18

SQL Server数据库PowerDesign

2010-10-22 15:42:59

SQL Server创

2011-04-07 14:50:21

SQL Server数据库

2010-06-30 11:16:50

SQL Server

2011-04-29 14:30:23

2011-04-01 17:05:44

SQL Server数日志

2009-03-19 09:44:07

SQL Server数据库迁移数据库
点赞
收藏

51CTO技术栈公众号