今天給大家分享自己在工作當中用到的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 documentationFROM dbo.syscolumns colLEFT JOIN dbo.systypes t ON col.xtype = t.xusertypeinner JOIN dbo.sysobjects obj ON col.id = obj.idAND obj.xtype = 'U'AND obj.status >= 0LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.idLEFT JOIN sys.extended_properties ep ON col.id = ep.major_idAND col.colid = ep.minor_idAND ep.name = 'MS_Description'LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_idAND epTwo.minor_id = 0AND epTwo.name = 'MS_Description'WHERE obj.name in(SELECTob.name FROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS epON ep.major_id = ob.object_idAND ep.class = 1AND ep.minor_id = 0WHERE ObjectProperty(ob.object_id, 'IsUserTable') = 1 )ORDER BY obj.name ;

2、SQLServer 查詢數據庫各個數據表、索引文件占用的存儲空間

可以快速查詢數據庫中表、索引占用的存儲空間，找到哪些表占用了大量的存儲空間，便于進行數據庫優化。

CREATE PROCEDURE [dbo].[sys_viewTableSpace]ASBEGINSET 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 #tableinfoorder by 記錄數 descdrop table #tableinfoEND-- 執行方法exec sys_viewtablespace

3、清理數據庫日志文件

數據庫日志文件一般都會非常大，甚至占用超過幾百G甚至上T，如果不需要進行一直保留數據庫日志文件，可以建一個數據庫作業，定時清理數據庫日志文件，具體可以采用下面的腳本。

USE masterALTER DATABASE DB SET RECOVERY SIMPLE WITH NO_WAITALTER DATABASE DB SET RECOVERY SIMPLE --調整為簡單模式USE DBDBCC SHRINKFILE (N'DB_log' , 2, TRUNCATEONLY) --設置壓縮后的日志大小為2M，可以自行指定USE masterALTER DATABASE DB SET RECOVERY FULL WITH NO_WAITALTER 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_DateCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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))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); --星期幾-- 插入數據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);continueif @dDate=dateadd(day,-1,convert(date,@end_date))breakend-- 3、執行存儲過程生成數據GODECLARE @return_value intEXEC @return_value = [dbo].[SP_CREATE_TIME_DIMENSION]SELECT 'Return Value' = @return_valueGO

總結

以上是生活随笔為你收集整理的sqlserver date转nvarchar_数据库干货：整理SQLServer非常实用的脚本的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。