SQLServer啟動和關閉bat腳本
安裝完畢SQL SERVER 2005后，會默認自動啟動SQL Server等幾個服務，這幾個服務比較占用系統資源。當不運行SQL Server時，最好停止這些服務。如果一個個點擊這些服務，過于麻煩了，可以用批處理文件來啟動和停止該類服務。
首先將“控制面板->管理工具->服務”里面的SQL SERVER 2005相關的服務啟動類型設置為手動（SQL Server開頭的服務），然后新建立一個文本文檔，把后綴名改為“bat”。下面就添加批處理命令：
啟動SQL SERVER.bat：
@echo.服務啟動......
@echo off
@sc start MSSQLSERVER
@sc start SQLSERVERAGENT
@sc start MSSQLServerOLAPService
@sc start msftesql
@sc start MsDtsServer
@sc start SQLWriter
@echo off
@echo.啟動完畢！
@pause
停止SQL SERVER.bat：
@echo.服務停止......
@echo off
@sc stop SQLSERVERAGENT
@sc stop MSSQLServerOLAPService
@sc stop msftesql
@sc stop MsDtsServer
@sc stop SQLWriter
@sc stop MSSQLSERVER
@echo off
@echo.停止完畢！
@pause
*如果想要配置服務的啟動方式則可以通過腳本實現
@sc config SQLAgent$wincc start=auto
用sc可打開被禁用的服務，語法是：
sc config 服務名 start=demand //手動
sc condig 服務名 start=auto //自動
sc config 服務名 start=disabled //禁用
啟動服務格式為：
sc start 服務名
sc stop 服務名
其中sc（server control）命令具體用法可以用“sc -help”命令查看，也可到網上搜索。建立好這兩個批處理文件后，當運行SQL Server 2005時就執行“啟動SQL SERVER.bat”這個批處理文件，退出時就執行“停止SQL SERVER.bat”，很方便。
where datediff(day,@datetime,getdate())>30
@datetime為存儲過程的傳入參數，也可以為數據庫字段名稱，但字段名稱必須為datetime類型
導入數據庫的方法
一、excel數據導入到數據庫
select* into ccc from
OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="G:\tenptable123";User ID=recipe_db_user;Password=recipe_db_user;Extended properties=Excel 5.0')...[Sheet1$]
select * into b from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=G:\tenptable123',CFG_Phase_Info$);
二、txt數據導入到數據庫中
insert into b select* from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;HDR=No;DATABASE=G:\',tenptable123#txt);G:\是目錄,aa#txt) --tenptable123#txt是文本文件名
BULK INSERT b FROM 'g:\tenptable123.txt' WITH ( FIELDTERMINATOR=',', ROWTERMINATOR='\n')，每個數據是以逗號分隔的，ROWTERMINATOR='，\n'每行數據以逗號回車結尾
SQL Server日常維護常用的一些腳本整理。
1.sql server開啟clr權限：
exec sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
ALTER DATABASE HWMESTC SET TRUSTWORTHY ON
ALTER AUTHORIZATION ON Database::HWMESTC TO sa;2.查詢數據庫大小
Exec sp_spaceused
select name, convert(float,size) * (8192.0/1024.0)/1024. from dbo.sysfiles3.數據庫日志壓縮
--選擇需要使用的數據庫
USE PIMS
--將數據庫模式設置為SIMPLE
ALTER DATABASE PIMS SET RECOVERY SIMPLE
-- 將日志文件收縮到1M
DBCC SHRINKFILE ('PIMS_log', 1)
-- 還原數據庫
ALTER DATABASE PIMS SET RECOVERY FULL4.查看數據庫連接用戶
Select * From sys.dm_exec_connections5.查看當前占用 cpu 資源最高的會話和其中執行的語句(及時CPU)
select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc6.查看緩存中重用次數少，占用內存大的查詢語句（當前緩存中未釋放的）--全局
SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts,p.size_in_bytes desc7.看BUFFER POOL中，都緩存了哪些表(當前數據庫)的數據
select OBJECT_NAME(object_id) 表名,COUNT(*) 頁數,COUNT(*)*8/1024.0 Mb
from sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c
where a.allocation_unit_id=b.allocation_unit_id
and b.container_id=c.hobt_id
and database_id=DB_ID()
group by OBJECT_NAME(object_id)
order by 2 desc 8.查詢SQLSERVER內存使用情況
select * from sys.dm_os_process_memory9.查詢SqlServer總體的內存使用情況
select type,
sum(virtual_memory_reserved_kb)*0.1*10/1024/1024 as vm_Reserved_gb,--保留的內存
sum(virtual_memory_committed_kb)*0.1*10/1024/1024 as vm_Committed_gb,--提交的內存
sum(awe_allocated_kb)*0.1*10/1024/1024 as awe_Allocated_gb,--開啟AWE后使用的內存
sum(shared_memory_reserved_kb)*0.1*10/1024/1024 as sm_Reserved_gb,--共享的保留內存
sum(shared_memory_committed_kb)*0.1*10/1024/1024 as sm_Committed_gb--共享的提交內存
from sys.dm_os_memory_clerks
group by type
order by type10.查詢當前數據庫緩存的所有數據頁面，哪些數據表，緩存的數據頁面數量
-- 查詢當前數據庫緩存的所有數據頁面，哪些數據表，緩存的數據頁面數量
-- 從這些信息可以看出，系統經常要訪問的都是哪些表，有多大？
select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*)
from sys.allocation_units a,
sys.dm_os_buffer_descriptors b,
sys.partitions p
where a.allocation_unit_id=b.allocation_unit_id
and a.container_id=p.hobt_id
and b.database_id=db_id()
group by p.object_id,p.index_id
order by buffer_pages desc 11.查詢緩存的各類執行計劃，及分別占了多少內存
-- 查詢緩存的各類執行計劃，及分別占了多少內存
-- 可以對比動態查詢與參數化SQL（預定義語句）的緩存量
select cacheobjtype
, objtype
, sum(cast(size_in_bytes as bigint))/1024 as size_in_kb
, count(bucketid) as cache_count
from sys.dm_exec_cached_plans
group by cacheobjtype, objtype
order by cacheobjtype, objtype12.查詢緩存中具體的執行計劃，及對應的SQL
-- 查詢緩存中具體的執行計劃，及對應的SQL
-- 將此結果按照數據表或SQL進行統計，可以作為基線，調整索引時考慮
-- 查詢結果會很大，注意將結果集輸出到表或文件中
SELECT usecounts ,
refcounts ,
size_in_bytes ,
cacheobjtype ,
objtype ,
TEXT
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC ;
GO13.查詢sql server內存整體使用情況
--查詢sql server內存整體使用情況
SELECT object_name, cntr_value*0.1*10/1024/1024 ,cntr_value,cntr_type,t.counter_name,t.instance_name
FROM sys.dm_os_performance_counters t
WHERE counter_name='Total Server Memory (KB)';14.一次性清除數據庫所有表的數據
CREATE PROCEDURE sp_DeleteAllData
AS
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'
GO 15.SQL優化相關、執行時間
SELECT creation_time N'語句編譯時間'
,last_execution_time N'上次執行時間'
,total_physical_reads N'物理讀取總次數'
,total_logical_reads/execution_count N'每次邏輯讀次數'
,total_logical_reads N'邏輯讀取總次數'
,total_logical_writes N'邏輯寫入總次數'
,execution_count N'執行次數'
,total_worker_time/1000 N'所用的CPU總時間ms'
,total_elapsed_time/1000 N'總花費時間ms'
,(total_elapsed_time / execution_count)/1000 N'平均時間ms'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'執行語句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) NOT LIKE '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC; 16.truncate外鍵表存儲過程
USE PIMS
GO
CREATE PROCEDURE [dbo].[usp_Truncate_Table]
@TableToTruncate VARCHAR(64)
AS
BEGIN
SET NOCOUNT ON
--==變量定義
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)
DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)
SET @Debug=0--(0:將執行相關語句|1:不執行語句)
SET @Recycle=0--(0:不創建/不清除存儲表|1:將創建/清理存儲表)
set @Verbose=1--(1:每步執行均打印消息|0:不打印消息)
SET @i=1
SET @CreateStatement='ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
SET @DropStatement='ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
SET @TruncateStatement='TRUNCATE TABLE [<tablename>]'
-- 創建外鍵臨時表
IF OBJECT_ID('tempdb..#FKs') IS NOT NULL
DROP TABLE #FKs
-- 獲取外鍵
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
OBJECT_NAME(constraint_object_id) as ConstraintName,
OBJECT_NAME(parent_object_id) as TableName,
clm1.name as ColumnName,
OBJECT_NAME(referenced_object_id) as ReferencedTableName,
clm2.name as ReferencedColumnName
INTO #FKs
FROM sys.foreign_key_columns fk
JOIN sys.columns clm1 ON fk.parent_column_id=clm1.column_id AND fk.parent_object_id=clm1.object_id
JOIN sys.columns clm2 ON fk.referenced_column_id=clm2.column_id AND fk.referenced_object_id=clm2.object_id
--WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
WHERE OBJECT_NAME(referenced_object_id)=@TableToTruncate
ORDER BY OBJECT_NAME(parent_object_id)
-- 外鍵操作(刪除|重建)表
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Internal_FK_Definition_Storage')
BEGIN
IF @Verbose=1
PRINT '1. 正在創建表(Internal_FK_Definition_Storage)...'
CREATE TABLE [Internal_FK_Definition_Storage]
(
ID int not null identity(1,1) primary key,
FK_Name varchar(250) not null,
FK_CreationStatement varchar(max) not null,
FK_DestructionStatement varchar(max) not null,
Table_TruncationStatement varchar(max) not null
)
END
ELSE
BEGIN
IF @Recycle=0
BEGIN
IF @Verbose=1
PRINT '1. 正在清理表(Internal_FK_Definition_Storage)...'
TRUNCATE TABLE [Internal_FK_Definition_Storage]
END
ELSE
PRINT '1. 正在清理表(Internal_FK_Definition_Storage)...'
END
IF @Recycle=0
BEGIN
IF @Verbose=1
PRINT '2. 正在備份外鍵定義...'
WHILE (@i <=(SELECT MAX(ID) FROM #FKs))
BEGIN
SET @ConstraintName=(SELECT ConstraintName FROM #FKs WHERE ID=@i)
SET @TableName=(SELECT TableName FROM #FKs WHERE ID=@i)
SET @ColumnName=(SELECT ColumnName FROM #FKs WHERE ID=@i)
SET @ReferencedTableName=(SELECT ReferencedTableName FROM #FKs WHERE ID=@i)
SET @ReferencedColumnName=(SELECT ReferencedColumnName FROM #FKs WHERE ID=@i)
SET @DropStatementTemp=REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
SET @CreateStatementTemp=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
SET @TruncateStatementTemp=REPLACE(@TruncateStatement,'<tablename>',@TableName)
INSERT INTO [Internal_FK_Definition_Storage]
SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp
SET @i=@i + 1
IF @Verbose=1
PRINT ' > 已備份外鍵:[' + @ConstraintName + '] 所屬表: [' + @TableName + ']'
END
END
ELSE
PRINT '2. 正在備份外鍵定義...'
IF @Verbose=1
PRINT '3. 正在刪除外鍵...'
BEGIN TRAN
BEGIN TRY
SET @i=1
WHILE (@i <=(SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName=(SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID=@i)
SET @Statement=(SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID=@i)
IF @Debug=1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i=@i + 1
IF @Verbose=1
PRINT ' > 已刪除外鍵:[' + @ConstraintName + ']'
END
IF @Verbose=1
PRINT '4. 正在清理數據表...'
--先清除該外鍵所在表(由于外鍵所在表仍可能又被其他外鍵所引用，因此需要循環遞歸處理)(注：本處理未實現)
--請不要使用下面注釋代碼
/*
SET @i=1
WHILE (@i <=(SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @Statement=(SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID=@i)
IF @Debug=1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i=@i + 1
IF @Verbose=1
PRINT ' > ' + @Statement
END
*/
IF @Debug=1
PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'
ELSE
EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')
IF @Verbose=1
PRINT ' > 已清理數據表[' + @TableToTruncate + ']'
IF @Verbose=1
PRINT '5. 正在重建外鍵...'
SET @i=1
WHILE (@i <=(SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName=(SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID=@i)
SET @Statement=(SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID=@i)
IF @Debug=1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i=@i + 1
IF @Verbose=1
PRINT ' > 已重建外鍵:[' + @ConstraintName + ']'
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
PRINT '出錯信息:'+ERROR_MESSAGE()
END CATCH
IF @Verbose=1
PRINT '6. 處理完成！'
END17. 查看job運行持續時間
SELECT
[T1].[job_id]
,[T1].[name] AS [job_name]
,[T2].[run_status]
,[T2].[run_date]
,[T2].[run_time]
,[dbo].[agent_datetime]([T2].[run_date], [T2].[run_time]) AS [run_datetime]
,[T2].[run_duration]
,DATEDIFF(SECOND, '1900-01-01', DATEADD(SECOND, 31, [dbo].[agent_datetime](19000101, [run_duration]))) AS [run_duration_s]
FROM
[dbo].[sysjobs] AS T1
INNER JOIN [dbo].[sysjobhistory] AS T2
ON [T2].[job_id]=[T1].[job_id]
WHERE
[T1].[enabled]=1
AND [T2].[step_id]=0
AND [T2].[run_duration] >=1
and [T1].[name]='PIMS_CreatePaperCraftParameterAnalysisData'
ORDER BY
[T2].[job_id] ASC
,[T2].[run_date] ASC
GO18. 從所有緩存中釋放所有未使用的緩存條目
DBCC FREESYSTEMCACHE('ALL');19. 查詢、解除死鎖
--查詢表死鎖信息
select object_name(resource_associated_entity_id) as tableName, request_session_id as pid from sys.dm_tran_locks
where resource_type='OBJECT'
dbcc opentran
--查看死鎖的詳細信息、執行的sql語句
exec sp_who2 53
--exec sp_who 53
DBCC inputbuffer (53)
--解除死鎖
kill 5320. 查詢SQL Server根據CPU消耗列出前5個最差性能的查詢
-- Worst performing CPU bound queries
SELECT TOP 5
st.text,
qp.query_plan,
qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC
GO21. 顯示如何依據I/O消耗來找出你性能最差的查詢
-- Worst performing I/O bound queries
SELECT TOP 5
st.text,
qp.query_plan,
qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_logical_reads DESC
GO22. 查詢服務器部分特殊信息
select SERVERPROPERTY(N'edition') as Edition --數據版本，如企業版、開發版等
,SERVERPROPERTY(N'collation') as Collation --數據庫字符集
,SERVERPROPERTY(N'servername') as ServerName --服務名
,@@VERSION as Version --數據庫版本號
,@@LANGUAGE AS Language --數據庫使用的語言，如us_english等23.查詢數據庫中各數據表大小
--=============================================-- 描 述：更新查詢數據庫中各表的大小，結果存儲到數據表中
--=============================================--查詢是否存在結果存儲表
IF NOT EXISTS (SELECT * FROM sysobjects where id=OBJECT_ID(N'temp_tableSpaceInfo') AND OBJECTPROPERTY(id, N'IsUserTable')=1)
BEGIN
--不存在則創建
CREATE TABLE temp_tableSpaceInfo
(name NVARCHAR(128),
rows char(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18))
END
--清空數據表
DELETE FROM temp_tableSpaceInfo
--定義臨時變量在遍歷時存儲表名稱
DECLARE @tablename VARCHAR(255)
--使用游標讀取數據庫內所有表表名
DECLARE table_list_cursor CURSOR FOR
SELECT name FROM sysobjects
WHERE OBJECTPROPERTY(id, N'IsTable')=1 AND name NOT LIKE N'#%%' ORDER BY name
--打開游標
OPEN table_list_cursor
--讀取第一條數據
FETCH NEXT FROM table_list_cursor INTO @tablename
--遍歷查詢到的表名
WHILE @@FETCH_STATUS=0
BEGIN
--檢查當前表是否為用戶表
IF EXISTS (SELECT * FROM sysobjects WHERE id=OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable')=1)
BEGIN
--當前表則讀取其信息插入到表格中
EXECUTE sp_executesql N'INSERT INTO temp_tableSpaceInfo EXEC sp_spaceused @tbname', N'@tbname varchar(255)', @tbname=@tablename
END
--讀取下一條數據
FETCH NEXT FROM table_list_cursor INTO @tablename
END
--釋放游標
CLOSE table_list_cursor
DEALLOCATE table_list_cursor
SELECT *,replace(reserved,'KB','')/1024 數據表大小M FROM temp_tableSpaceInfo order by replace(reserved,'KB','')/1024 desc
drop table temp_tableSpaceInfo24.壓縮數據庫、文件、日志
DBCC ShrinkFile(‘數據庫名’, targetsize); /* 收縮數據庫文件 */
DBCC ShrinkFile(‘數據庫名_log’, targetsize); /* 收縮日志文件 */
Targetsize：單位為兆，必須為整數，DBCC SHRINKFILE 嘗試將文件收縮到指定大小。
DBCC SHRINKFILE 不會將文件收縮到小于“實際使用的空間”大小，例如“分配空間”為10M，“實際使用空間”為6M，當制定targetsize為1時，則將該文件收縮到6M，不會將文件收縮到1M。
--收縮數據庫
DBCC SHRINKDATABASE(數據庫名，百分比)
百分比：即“收縮后文件中的最大可用空間”，取值范圍“大于等于0， 小于100%”，實際使用中設為0即可。25.用擴展時間抓取過去的死鎖信息
View Code
26.數據庫對象信息檢索
--查看對象的說明信息
exec sp_help 'T_papermachine'
--顯示視圖、存儲過程、函數、觸發器的定義腳本。
exec sp_helptext 'proc_report_getmeasuredata'
--顯示表的行數和占用空間。
exec sp_spaceused 'T_papermachine'
--顯示表或視圖的前100行，選定“tablename,1000”按Ctrl+F1可顯示表的前1000行。
exec sp_executesql N'IF OBJECT_ID(@tablename) IS NOT NULL EXEC(N''SELECT TOP(''+@n+N'') * FROM ''+@tablename)',N'@tablename nvarchar(100)=''t_papermachine'',@n int=100'
--顯示表中每個索引占用的空間。
exec sp_executesql N'SELECT index_name=ind.name, ddps.used_page_count, ddps.reserved_page_count, ddps.row_count FROM sys.indexes ind INNER JOIN sys.dm_db_partition_stats ddps ON ind.object_id=ddps.object_id AND ind.index_id=ddps.index_id WHERE ind.object_id=OBJECT_ID(@tablename)',N'@tablename nvarchar(100)=''t_papermachine'''
--顯示表或視圖的字段名，以逗號分隔。
exec sp_executesql N'SELECT columns=STUFF((SELECT '', ''+name FROM sys.columns WHERE object_id=OBJECT_ID(@tablename) FOR XML PATH('''')),1,2,'''')',N'@tablename nvarchar(100)=''T_Papermachine'''
--根據選定關鍵詞在當前數據庫中查找表、視圖、存儲過程、函數
exec sp_executesql N'SELECT * FROM sys.objects WHERE type IN (''U'',''V'',''P'',''FN'') AND name LIKE ''%''+@keyword+''%'' ORDER BY type,name',N'@keyword nvarchar(50)=''machine'''
--查詢數據庫中包含指定關鍵詞的表、視圖、存儲過程、函數
select routine_name,routine_definition,routine_type
from information_schema.routines
where routine_definition like '%AssessmentSpeed%'
order by routine_type
--模糊查詢存儲過程sql中包含某個文本
SELECT obj.Name 存儲過程名, sc.TEXT 存儲過程內容
FROM syscomments sc
INNER JOIN sysobjects obj ON sc.Id=obj.ID
WHERE sc.TEXT LIKE '%存儲過程內容%'27.數據庫用戶、權限操作
USE [master]
GO
--待確認賬號密碼
CREATE LOGIN [NDIT] WITH PASSWORD=N'1', DEFAULT_DATABASE=[PIMS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE PIMS
go
CREATE USER [NDIT] FOR LOGIN [NDIT]
GO
--大權限， 如果是指定的部分表，不執行這個，如果是所有內容都可以讀，用此腳本
--EXEC sp_addrolemember N'db_datareader', N'NDIT'
--GO
--指定特定表名賦予新增/更新/查詢
DECLARE @Sql NVARCHAR(max)
SET @Sql=''
--table
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.tables AS a WHERE name IN ('Tab1','Tab2');
--view
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.views AS a WHERE name IN ('view1','view2');
--procedure
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.procedures AS a WHERE name IN ('proc1','proc2');
PRINT @Sql
EXEC(@Sql)
go
--禁用登陸帳戶
alter login NDIT disable
--啟用登陸帳戶
alter login NDIT enable
--登陸帳戶改名
alter login NDIT with name=dba_tom
--登陸帳戶改密碼：
alter login NDIT with password='aabb@ccdd'
--數據庫用戶改名：
alter user NDIT with name=dba_tom
--更改數據庫用戶 defult_schema：
alter user NDIT with default_schema=sales
--刪除數據庫用戶：
drop user NDIT
--刪除 SQL Server登陸帳戶：
drop login NDIT28.使用Checksum結合NewID獲得隨機數
Create FUNCTION Scalar_CheckSumNEWID
(
@From int,
@To int,
@Keep int,
@newid varchar(50)
)
RETURNS float
BEGIN
DECLARE @ResultVar float
SELECT @ResultVar=CONVERT(BIGINT,RIGHT(ABS(CHECKSUM(@newid)),9))*0.1/100000000
RETURN @From+round((@To-@From)*@ResultVar,@Keep)
END
GO 29. 查詢數據庫表字段各項屬性信息，便于直接復制導出excel表
View Code
30. 判斷是否存在數據庫、表、列、視圖
View Code
31. CTE查詢的存儲過程執行時間明顯超出T-Sql查詢。 可以通過添加“WITH RECOMPILE”參數，強制存儲過程每次執行時重編譯，實現快速查詢。
大神的帖子： Parameter Sniffing, Embedding, and the RECOMPILE Options
32. 解決insert exec 嵌套問題，解決辦法是建立一個指向自己的數據庫，增加鏈接服務器。
--1. 首先，增加鏈接服務器：
exec sp_addlinkedserver 'srv1','','SQLOLEDB','(local)'
exec sp_addlinkedsrvlogin 'srv1','false',null,'sa','sa'
--2. 其次找到該鏈接服務器，右鍵屬性，開啟RPC：
服務器對象->鏈接服務器->右鍵->屬性->服務器選項->RPC、RPC Out 都設置為True
--3. 啟動MSDTC服務：
服務名稱為：MSDTC（顯示名稱為Distributed Transaction Coordinator） 如果沒啟動會報錯如下：MSDTC on server 'servername' is unavailable
--4. 調整存儲過程訪問，使用srv1調用存儲過程
insert #Temp exec srv1.DBName.dbo.Proc_Test @param
--5. 成功！結束！33. 查詢數據庫連接數、用戶等
--查看連接到數據庫"DB"的連接
SELECT * from master.dbo.sysprocesses WHERE dbid=DB_ID('DB')
--查詢某個數據庫用戶的連接情況
sp_who 'sa'
--查看數據庫允許的最大連接
select @@MAX_CONNECTIONS
--查看數據庫自上次啟動以來的連接次數
SELECT @@CONNECTIONS
--關閉連接，上面的查詢可以得到spid，根據spid，關閉進程就可以了。
kill 5434. 數據庫緩存清理
CREATE PROCEDURE [dbo].ClearMemory
AS
BEGIN
--清除所有緩存
DBCC DROPCLEANBUFFERS
--打開高級配置
exec sp_configure 'show advanced options', 1
--設置最大內存值，清除現有緩存空間
exec sp_configure 'max server memory', 25600
EXEC ('RECONFIGURE')
--設置等待時間
WAITFOR DELAY '00:00:01'
--重新設置最大內存值
EXEC sp_configure 'max server memory',40960
EXEC ('RECONFIGURE')
--關閉高級配置
exec sp_configure 'show advanced options',0
END
GO 35. sqlcmd命令修改memory
net start MSSQLServer /mSQLCMD /f
SQLCMD
EXEC sp_configure 'show advanced option', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max server memory', '40960'
GO
RECONFIGURE WITH OVERRIDE
GO
go36. 數據庫還原中。。。
RESTORE DATABASE 數據庫名 WITH RECOVERY37.SQLServer查詢所有子節點
用CTE遞歸
;with f as
(
select * from tab where id=1
union all
select a.* from tab as a inner join f as b on a.pid=b.id
)
select * from f38.同數據集通過偏移量進行行關聯計算
LAG(訪問相同結果集的先前行中的數據)、Lead(訪問相同結果集的后續行中的數據)
https://docs.microsoft.com/zh-cn/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15
39.列拼接字符串
select stuff((
select ';'+name
from Student for xml path('')),1,1,'') as name40. 更新函數
EXECUTE sp_refreshsqlmodule N'[dbo].[Proc_Chart]';41.行轉列
Declare @tanks nvarchar(200);
Declare @sql nvarchar(4000)
set @tanks=(SELECT STUFF((SELECT ','+tl.UnitCode FROM dbo.V_CP_Tank tl FOR XML PATH('')),1,1,''))
SELECT @tanks
SET @sql='
SELECT DataDate,'+@tanks+'
FROM CP_TankMass
PIVOT(sum(RealQuantity) FOR [UnitCode] IN('+@tanks+')) AS T
'
Exec(@sql)42.延時
SQL有定時執行的語句WaitFor。
語法格式：waitfor {delay 'time'|time 'time'}
delay后面的時間是需要延遲多長時間后執行。
time后面的時間是指定何時執行，格式為'HH:MM:SS',不支持日期如果覺得文章對您有幫助，請不吝點個贊，表示一下支持！謝謝！