前言:
眼前你们对“删除sql server表显示不存在或没有权限”大约比较珍视,姐妹们都想要分析一些“删除sql server表显示不存在或没有权限”的相关文章。那么小编也在网络上汇集了一些关于“删除sql server表显示不存在或没有权限””的相关内容,希望大家能喜欢,咱们快快来了解一下吧!SQL Server日常维护常用的一些脚本整理。
1.sql server开启clr权限:
exec sp_configure 'clr enabled', 1GORECONFIGUREGOALTER DATABASE HWMESTC SET TRUSTWORTHY ONALTER AUTHORIZATION ON Database::HWMESTC TO sa;
2.查询数据库大小
Exec sp_spaceusedselect name, convert(float,size) * (8192.0/1024.0)/1024. from dbo.sysfiles
3.数据库日志压缩
--选择需要使用的数据库USE PIMS--将数据库模式设置为SIMPLEALTER DATABASE PIMS SET RECOVERY SIMPLE-- 将日志文件收缩到1M DBCC SHRINKFILE ('PIMS_log', 1)-- 还原数据库ALTER DATABASE PIMS SET RECOVERY FULL
4.查看数据库连接用户
Select * From sys.dm_exec_connections
5.查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)
select spid,cmd,cpu,physical_io,memusage,(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_textfrom master..sysprocesses order by cpu desc,physical_io desc
6.查看缓存中重用次数少,占用内存大的查询语句(当前缓存中未释放的)--全局
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 desc
7.看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_memory
9.查询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_clerksgroup by typeorder by type
10.查询当前数据库缓存的所有数据页面,哪些数据表,缓存的数据页面数量
-- 查询当前数据库缓存的所有数据页面,哪些数据表,缓存的数据页面数量-- 从这些信息可以看出,系统经常要访问的都是哪些表,有多大?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_countfrom sys.dm_exec_cached_plansgroup by cacheobjtype, objtypeorder by cacheobjtype, objtype
12.查询缓存中具体的执行计划,及对应的SQL
-- 查询缓存中具体的执行计划,及对应的SQL-- 将此结果按照数据表或SQL进行统计,可以作为基线,调整索引时考虑-- 查询结果会很大,注意将结果集输出到表或文件中SELECT usecounts , refcounts , size_in_bytes , cacheobjtype , objtype , TEXTFROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(plan_handle)ORDER BY objtype DESC ;GO
13.查询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 PIMSGOCREATE PROCEDURE [dbo].[usp_Truncate_Table] @TableToTruncate VARCHAR(64)AS BEGINSET NOCOUNT ON--==变量定义DECLARE @i intDECLARE @Debug bitDECLARE @Recycle bitDECLARE @Verbose bitDECLARE @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 ELSEBEGIN 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)...'ENDIF @Recycle = 0BEGIN 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 TRYSET @i = 1WHILE (@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 = 1WHILE (@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 ' > ' + @StatementEND*/IF @Debug = 1 PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'ELSE EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')IF @Verbose = 1 PRINT ' > 已清理数据表[' + @TableToTruncate + ']' IF @Verbose = 1 PRINT '5. 正在重建外键...'SET @i = 1WHILE (@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 COMMITEND TRYBEGIN CATCH ROLLBACK PRINT '出错信息:'+ERROR_MESSAGE()END CATCHIF @Verbose = 1 PRINT '6. 处理完成!'END
17. 查看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] ASCGO
18. 从所有缓存中释放所有未使用的缓存条目
DBCC FREESYSTEMCACHE('ALL');
19. 查询、解除死锁
--查询表死锁信息select object_name(resource_associated_entity_id) as tableName, request_session_id as pid from sys.dm_tran_lockswhere resource_type = 'OBJECT'dbcc opentran--查看死锁的详细信息、执行的sql语句exec sp_who2 53--exec sp_who 53 DBCC inputbuffer (53)--解除死锁kill 53
20. 查询SQL Server根据CPU消耗列出前5个最差性能的查询
-- Worst performing CPU bound queriesSELECT TOP 5 st.text, qp.query_plan, qs.*FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY total_worker_time DESCGO
21. 显示如何依据I/O消耗来找出你性能最差的查询
-- Worst performing I/O bound queriesSELECT TOP 5 st.text, qp.query_plan, qs.*FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY total_logical_reads DESCGO
22. 查询服务器部分特殊信息
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_tableSpaceInfo
24.压缩数据库、文件、日志
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_typefrom information_schema.routineswhere routine_definition like '%AssessmentSpeed%'order by routine_type--模糊查询存储过程sql中包含某个文本SELECT obj.Name 存储过程名, sc.TEXT 存储过程内容FROM syscomments scINNER JOIN sysobjects obj ON sc.Id = obj.IDWHERE sc.TEXT LIKE '%存储过程内容%'
27.数据库用户、权限操作
USE [master]GO--待确认账号密码CREATE LOGIN [NDIT] WITH PASSWORD=N'1', DEFAULT_DATABASE=[PIMS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOUSE PIMSgoCREATE 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 @SqlEXEC(@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 NDIT
28.使用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 54
34. 数据库缓存清理
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 ENDGO
35. sqlcmd命令修改memory
net start MSSQLServer /mSQLCMD /fSQLCMDEXEC sp_configure 'show advanced option', '1'GORECONFIGURE WITH OVERRIDEGOEXEC sp_configure 'max server memory', '40960'GORECONFIGURE WITH OVERRIDEGOgo
36. 数据库还原中。。。
RESTORE DATABASE 数据库名 WITH RECOVERY
37.SQLServer查询所有子节点
用CTE递归;with f as (select * from tab where id=1union allselect a.* from tab as a inner join f as b on a.pid=b.id)select * from f
38.同数据集通过偏移量进行行关联计算
LAG(访问相同结果集的先前行中的数据)、Lead(访问相同结果集的后续行中的数据)
39.列拼接字符串
select stuff((select ';'+namefrom Student for xml path('')),1,1,'') as name
40. 更新函数
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 @tanksSET @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',不支持日期
如果觉得文章对您有帮助,请不吝点个赞,表示一下支持!谢谢!