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

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

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

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

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

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_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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。