今天给大家分享一些我在工作中常用的SQLServer脚本，希望对大家有所帮助！1、通过本脚本查询数据库所有表结构，查找表字段，或生成数据库设计文档，进行数据库比对。SELECTobj.name表名，col.colorderAS序列号，col.nameAS列名，ISNULL(ep.[value],'')AS列描述，t.nameAS数据类型，CASEWHENcol.isnullable=1THEN'1'ELSE''ENDAS允许Null，ISNULL（comm.text，''）作为默认值，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=woepTwo.majorW.obinDescription_id.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(表名,记录数,保留空间,已用空间,索引占用空间,未用spaceused)execsp_MSforeachtable"execsp_spaceused'?'"select*from#tableinfoorderbyrecordnumberdescdroptable#tableinfoEND--执行??方法execsys_viewtablespace3。清理数据库日志文件数据库日志文件一般都很大，甚至占用几百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锁表进程；tableNameislockedtablename--unlock语句需要获取spid然后kill缩表进程declare@spidintSet@spid=57--locktableprocessdeclare@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_month2varchar(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);--年份设置@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)+'year'+convert(nvarchar(10),@v_the_quarter)+'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)+'year'+convert(nvarchar(10),@v_the_month)+'month',@v_the_week,'第一个'+convert(nvarchar(10),@v_the_week)+'week',@v_week_day,case@v_week_day-1when1then'Monday'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
