百度360必应搜狗淘宝本站头条
mqtt服务器xftp6vscode使用技巧mybatiscollectionkeyerror
当前位置：网站首页 > 热门文章 > 正文

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

bigegpt 2024-08-04 11:39 8 浏览


今天给大家分享自己在工作当中用到的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,
 记录数 [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

相关推荐

最全的MySQL总结，助你向阿里“开炮”(面试题+笔记+思维图)

前言作为一名编程人员，对MySQL一定不会陌生，尤其是互联网行业，对MySQL的使用是比较多的。对于求职者来说，MySQL又是面试中一定会问到的重点，很多人拥有大厂梦，却因为MySQL败下阵来。实际上...

Redis数据库从入门到精通（redis数据库设计）

目录一、常见的非关系型数据库NOSQL分类二、了解Redis三、Redis的单节点安装教程四、Redis的常用命令1、Help帮助命令2、SET命令3、过期命令4、查找键命令5、操作键命令6、GET命...

netcore 急速接入第三方登录，不看后悔

新年新气象，趁着新年的喜庆，肝了十来天，终于发了第一版，希望大家喜欢。如果有不喜欢看文字的童鞋，可以直接看下面的地址体验一下：https://oauthlogin.net/前言此次带来得这个小项目是...

精选 30 个 C++ 面试题(含解析)（c++面试题和答案汇总）

大家好，我是柠檬哥，专注编程知识分享。欢迎关注@程序员柠檬橙，编程路上不迷路，私信发送以下关键字获取编程资源：发送1024打包下载10个G编程资源学习资料发送001获取阿里大神LeetCode...

Oracle 12c系列(一)|多租户容器数据库

作者杨禹航出品沃趣技术Oracle12.1发布至今已有多年，但国内Oracle12C的用户并不多，随着12.2在去年的发布，选择安装Oracle12c的客户量明显增加，在接下来的几年中，Or...

flutter系列之:UI layout简介（flutter-ui-nice）

简介对于一个前端框架来说，除了各个组件之外，最重要的就是将这些组件进行连接的布局了。布局的英文名叫做layout，就是用来描述如何将组件进行摆放的一个约束。在flutter中，基本上所有的对象都是wi...

Flutter 分页功能表格控件（flutter 列表）

老孟导读：前2天有读者问到是否有带分页功能的表格控件，今天分页功能的表格控件详细解析来来。PaginatedDataTablePaginatedDataTable是一个带分页功能的DataTable，...

Flutter | 使用BottomNavigationBar快速构建底部导航

平时我们在使用app时经常会看到底部导航栏,而在flutter中它的实现也较为简单.需要用到的组件:BottomNavigationBar导航栏的主体BottomNavigationBarI...

Android中的数据库和本地存储在Flutter中是怎样实现的

如何使用SharedPreferences？在Android中，你可以使用SharedPreferencesAPI来存储少量的键值对。在Flutter中，使用Shared_Pref...

Flet，一个Flutter应用的实用Python库!

▼Flet：用Python轻松构建跨平台应用！在纷繁复杂的Python框架中，Flet宛如一缕清风，为开发者带来极致的跨平台应用开发体验。它用最简单的Python代码，帮你实现移动端、桌面端...

flutter系列之:做一个图像滤镜（flutter photo）

简介很多时候，我们需要一些特效功能，比如给图片做个滤镜什么的，如果是h5页面，那么我们可以很容易的通过css滤镜来实现这个功能。那么如果在flutter中，如果要实现这样的滤镜功能应该怎么处理呢？一起...

flutter软件开发笔记20-flutter web开发

flutterweb开发优势比较多，采用统一的语言，就能开发不同类型的软件，在web开发中，特别是后台式软件中，相比传统的html5开发，更高效，有点像c++编程的方式，把web设计出来了。一...

Flutter实战-请求封装(五)之设置抓包Proxy

用了两年的flutter，有了一些心得，不虚头巴脑，只求实战有用，以供学习或使用flutter的小伙伴参考，学习尚浅，如有不正确的地方还望各路大神指正，以免误人子弟，在此拜谢~（原创不易，转发请标注来...

为什么不在 Flutter 中使用全局变量来管理状态

我相信没有人用全局变量来管理Flutter应用程序的状态。毫无疑问，我们的Flutter应用程序需要状态管理包或Flutter的基本小部件（例如InheritedWidget或St...

Flutter 攻略(Dart基本数据类型，变量 整理 2)

代码运行从main方法开始voidmain(){print("hellodart");}变量与常量var声明变量未初始化变量为nullvarc;//未初始化print(c)...

一周热门
最近发表
标签列表