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