总结SQL Server非常实用的脚本
发表于:2024-10-19 作者:千家信息网编辑
千家信息网最后更新 2024年10月19日,本篇内容主要讲解"总结SQL Server非常实用的脚本",感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习"总结SQL Server非常实用的脚本"吧!1、 查
千家信息网最后更新 2024年10月19日总结SQL Server非常实用的脚本
本篇内容主要讲解"总结SQL Server非常实用的脚本",感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习"总结SQL Server非常实用的脚本"吧!
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_month3 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
到此,相信大家对"总结SQL Server非常实用的脚本"有了更深的了解,不妨来实际操作一番吧!这里是网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
数据
数据库
空间
查询
脚本
星期
生成
实用
文件
日志
存储
数据表
索引
日期
时间
进程
内容
方法
模式
维度
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
中关村软件开发工资
网络安全内涵ppt
通用数据库访问接口
芜湖电商系统软件开发
电子发票提示服务器
做网络安全工作怎么样
扩展存储的数据库
sas 调用数据库数据
计算机网络安全课件和教案
怎么修改数据库etc
怎么在国外搭载服务器
网络安全法 发布
数据库基础入门
华为服务器如何开放安全组的
网络安全电影影评
对于软件开发者来说
网络安全从什么时候开始执行
软件开发公司 SaaS
从数据库获取数据生成二维码
陕西信合网络安全
传统服务器和云主机哪个好
全球有多少网络根服务器
天荒坪民宿软件开发
人间地狱cn81服务器
腾讯建服务器
java 数据库递归查询
护苗网络安全课开展方案
怎么关网络安全检查
并行软件开发涉及到的技术
服务器搭建助手