今天给大家分享自己在工作当中用到的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
-
-
-
IT技术分享社区
个人博客网站：https://programmerblog.xyz
文章推荐程序员效率：画流程图常用的工具程序员效率：整理常用的在线笔记软件远程办公：常用的远程协助软件，你都知道吗？51单片机程序下载、ISP及串口基础知识硬件：断路器、接触器、继电器基础知识
转载：https://blog.csdn.net/xishining/article/details/111188838
查看评论