本篇内容主要讲解“总结SQL Server非常实用的脚本”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“总结SQL Server非常实用的脚本”吧!

1、 查询数据库所有表结构

通过该脚本可以快速查找表字段，或者生成数据库设计文档、进行数据库对比。

SELECTobj.name表名,col.colorderAS序号,col.nameAS列名,ISNULL(ep.[value],'')AS列说明,t.nameAS数据类型,CASEWHENcol.isnullable=1THEN'1'ELSE''ENDAS允许空,ISNULL(comm.text,'')AS默认值,Coalesce(epTwo.value,'')ASdocumentationFROMdbo.syscolumnscolLEFTJOINdbo.systypestONcol.xtype=t.xusertypeinnerJOINdbo.sysobjectsobjONcol.id=obj.idANDobj.xtype='U'ANDobj.status>=0LEFTJOINdbo.syscommentscommONcol.cdefault=comm.idLEFTJOINsys.extended_propertiesepONcol.id=ep.major_idANDcol.colid=ep.minor_idANDep.name='MS_Description'LEFTJOINsys.extended_propertiesepTwoONobj.id=epTwo.major_idANDepTwo.minor_id=0ANDepTwo.name='MS_Description'WHEREobj.namein(SELECTob.nameFROMsys.objectsASobLEFTOUTERJOINsys.extended_propertiesASepONep.major_id=ob.object_idANDep.class=1ANDep.minor_id=0WHEREObjectProperty(ob.object_id,'IsUserTable')=1)ORDERBYobj.name;

2、SQLServer 查询数据库各个数据表、索引文件占用的存储空间

可以快速查询数据库中表、索引占用的存储空间，找到哪些表占用了大量的存储空间，便于进行数据库优化。

CREATEPROCEDURE[dbo].[sys_viewTableSpace]ASBEGINSETNOCOUNTON;CREATETABLE[dbo].#tableinfo(表名[varchar](50)COLLATEChinese_PRC_CI_ASNULL,记录数[int]NULL,预留空间[varchar](50)COLLATEChinese_PRC_CI_ASNULL,使用空间[varchar](50)COLLATEChinese_PRC_CI_ASNULL,索引占用空间[varchar](50)COLLATEChinese_PRC_CI_ASNULL,未用空间[varchar](50)COLLATEChinese_PRC_CI_ASNULL)insertinto#tableinfo(表名,记录数,预留空间,使用空间,索引占用空间,未用空间)execsp_MSforeachtable"execsp_spaceused'?'"select*from#tableinfoorderby记录数descdroptable#tableinfoEND--执行方法execsys_viewtablespace

3、清理数据库日志文件

数据库日志文件一般都会非常大，甚至占用超过几百G甚至上T，如果不需要进行一直保留数据库日志文件，可以建一个数据库作业，定时清理数据库日志文件，具体可以采用下面的脚本。

USEmasterALTERDATABASEDBSETRECOVERYSIMPLEWITHNO_WAITALTERDATABASEDBSETRECOVERYSIMPLE--调整为简单模式USEDBDBCCSHRINKFILE(N'DB_log',2,TRUNCATEONLY)--设置压缩后的日志大小为2M，可以自行指定USEmasterALTERDATABASEDBSETRECOVERYFULLWITHNO_WAITALTERDATABASEDBSETRECOVERYFULL--还原为完全模式

4、SQLServer查看锁表和解锁

工作中遇到查询的时候一直查询不出来结果，可以执行该脚本判断是否锁表，然后解锁就可以正常查询数据了。

--查询被锁表selectrequest_session_idspid,OBJECT_NAME(resource_associated_entity_id)tableNamefromsys.dm_tran_lockswhereresource_type='OBJECT';--参数说明spid锁表进程；tableName被锁表名--解锁语句需要拿到spid然后杀掉缩表进程declare@spidintSet@spid=57--锁表进程declare@sqlvarchar(1000)set@sql='kill'+cast(@spidasvarchar)exec(@sql)

5、SQLServer生成日期维度表

该脚本可以生成一个日期维度的数据表，通过该数据表可以解决很多报表查询问题。非常实用。

--1、创建数据表T_DateCREATETABLE[dbo].[T_Date]([the_date][int]NOTNULL,[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]PRIMARYKEYCLUSTERED([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]******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATEPROCEDURE[dbo].[SP_CREATE_TIME_DIMENSION]@begin_datenvarchar(50)='2015-01-01',@end_datenvarchar(50)='2030-12-31'as/*SP_CREATE_TIME_DIMENSION:生成时间维数据begin_date:开始时间end_date:结束时间*/declare@dDatedate=convert(date,@begin_date),@v_the_datevarchar(10),@v_the_yearvarchar(4),@v_the_quartervarchar(2),@v_the_monthvarchar(10),@v_the_month3varchar(2),@v_the_weekvarchar(2),@v_the_dayvarchar(10),@v_the_day2varchar(2),@v_week_daynvarchar(10),@adddaysint=1;WHILE(@dDate<=convert(date,@end_date))beginset@v_the_date=convert(char(10),@dDate,112);--key值格式为yyyyMMddset@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);--星期几--插入数据insertintoT_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)+'季度',casewhen@v_the_month>=10thenconvert(int,(convert(nvarchar(10),@v_the_year)+convert(nvarchar(10),@v_the_month)))elseconvert(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-1when1then'星期一'when2then'星期二'when3then'星期三'when4then'星期四'when5then'星期五'when6then'星期六'when0then'星期日'else''end);set@dDate=dateadd(day,@adddays,@dDate);continueif@dDate=dateadd(day,-1,convert(date,@end_date))breakend--3、执行存储过程生成数据GODECLARE@return_valueintEXEC@return_value=[dbo].[SP_CREATE_TIME_DIMENSION]SELECT'ReturnValue'=@return_valueGO

到此，相信大家对“总结SQL Server非常实用的脚本”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！