服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Sql Server - SQL Server数据库非常实用的脚本整理[干货]

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

2020-12-18 23:22数据库技术分享社区 Sql Server

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

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

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

 记录数 [intNULL

 预留空间 [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] [intNOT NULL

[date_name] [nvarchar](30) NULL

[the_year] [intNULL

[year_name] [nvarchar](30) NULL

[the_quarter] [intNULL

[quarter_name] [nvarchar](30) NULL

[the_month] [intNULL

[month_name] [nvarchar](30) NULL

[the_week] [intNULL

[week_name] [nvarchar](30) NULL

[week_day] [intNULL

[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 

原文地址:https://www.toutiao.com/a6906850379334484487/

延伸 · 阅读

精彩推荐