SQL Server 杂项
数据库清除日志
1 --2005 2 DUMP TRANSACTION [DataBaseName] WITH NO_LOG 3 BACKUP LOG [DataBaseName] WITH NO_LOG 4 DBCC SHRINKDATABASE([DataBaseName]) 5 6 --2012 7 use [DataBaseName] 8 ALTER DATABASE [DataBaseName] 9 SET RECOVERY SIMPLE; 10 DBCC SHRINKFILE (DataBaseName_log, 1); 11 ALTER DATABASE [DataBaseName] 12 SET RECOVERY full;
数据库死锁
1 select spid, blocked, loginame, last_batch, status, cmd, hostname, program_name 2 from sysprocesses 3 where spid in 4 ( select blocked from sysprocesses where blocked <> 0 ) or (blocked <>0) 5 6 select distinct 'kill '+ convert(nvarchar, spid) 7 from sysprocesses 8 where spid in 9 ( select blocked from sysprocesses where blocked <> 0 ) or (blocked <>0)
SQL查询执行优先级
1 (8)SELECT (9)DISTINCT (11)<TOP_specification> <select_list> 2 (1)FROM <left_table> 3 (3) <join_type> JOIN <right_table> 4 (2) ON <join_condition> 5 (4) WHERE <where_condition> 6 (5) GROUP BY <group_by_list> 7 (6)WITH {CUBE|ROLLUP} 8 (7)Having <having_condition> 9 (10)ORDER BY <order_by_list>
sql全库匹配
set nocount on Declare @Str varchar(50) Declare @CName varchar(50) Declare @CID varchar(20) --结果表变量 create table #a ( tableName varchar(100), columnName varchar(100), NumCount int ) set @str='关键字' --要搜索的字符串 Declare cur_Depart Cursor --定义游标 查询出所有列 For select name,id from syscolumns where id in (Select id From sysobjects Where xtype ='U') and xtype in (select xtype from systypes where name in ( 'varchar', 'nvarchar', 'char', 'nchar','text','ntext')) order by id Open cur_Depart Fetch From cur_Depart into @CName,@CID While @@Fetch_Status=0 Begin Declare @Sql nvarchar(500) Declare @TName nvarchar(500) Declare @Count int Declare @UID nvarchar(500) --查询出表面及其所有者 Select @TName=name,@UID=(select name from sys.database_principals where principal_id=uid) From sysobjects Where ID=@CID --定义匹配sql set @Sql='select @c=Count(*) from '+@UID+'.'+@TName+' WHERE PATINDEX(''%'+@Str+'%'',['+@CName+'])>0' exec sp_executesql @Sql,N'@c int output',@Count output if (@Count>0) begin insert into #a values(@TName,@CName,@Count) print 'delete ' +@TName +' where PATINDEX(''%'+@Str+'%'',['+@CName+'])>0' --print @TName+' '+@CName +' '+ convert(varchar,@Count) end Fetch From cur_Depart into @CName,@CID End select * from #a drop table #a Close cur_Depart Deallocate cur_Depart
--全库匹配目标字符串存在的表 declare @name nvarchar(100) declare cur cursor for select name from sysobjects where type = 'U' open cur fetch next from cur into @name WHILE @@FETCH_STATUS = 0 begin declare @sql nvarchar(500),@s varchar(5000) set @s ='' set @sql='select @s=isnull(@s+''+'','''')+'''''',''''''+''+cast(isnull([''+name+''],'''''''') as varchar)'' from syscolumns where id=object_id('''+@name+''') and xtype in(175,239,99,231,35,167) ' exec sp_executesql @sql,N'@s varchar(5000) out',@s out if len(@s) > 0 exec('if exists(select 1 from (select '+ @s+' as col from ['+@name+']) b where charindex(''要查询的字符串'',col)>0) print '''+@name+'''') fetch next from cur into @name end close cur DEALLOCATE cur
查看当前库所有表各有多少行数据
1 --查看当前库所有表各有多少行数据 2 create table #Tmp --创建临时表#Tmp 3 ( 4 ID int IDENTITY (1,1) not null, --创建列ID,并且每次新增一条记录就会加1 5 tablename varchar(500), 6 rows int, 7 primary key (ID) --定义ID为临时表#Tmp的主键 8 ); 9 declare @name nvarchar(100) 10 declare cur cursor for select name from sysobjects where type = 'U' 11 open cur 12 fetch next from cur into @name 13 WHILE @@FETCH_STATUS = 0 14 begin 15 16 declare @rowsd int 17 --select @rowsd=count(1) from @name 18 exec(' 19 declare @rowsd int 20 select @rowsd=count(1) from ['+@name+'] 21 22 insert #Tmp(tablename,rows) values('''+@name+''',@rowsd) 23 ') 24 fetch next from cur into @name 25 end 26 close cur 27 DEALLOCATE cur 28 29 select * from #Tmp order by rows 30 31 drop table #Tmp
游标
DECLARE @tablename nvarchar(100),@sql nvarchar(200) DECLARE contact_cursor CURSOR FOR SELECT name from sysobjects where xtype='U' OPEN contact_cursor FETCH NEXT FROM contact_cursor INTO @tablename WHILE @@FETCH_STATUS = 0 BEGIN PRINT @tablename -- exec('truncate table '+@tablename) 慎重使用 FETCH NEXT FROM contact_cursor INTO @tablename END CLOSE contact_cursor DEALLOCATE contact_cursor GO
sql server split函数 返回 非空数组成员长度
--sql server split函数 返回 非空数组成员长度 --author:tudou --说明: --支持分割符多字节 --使用方法 --select dbo.f_sqlserver_split_count('1203401230105045','0') --select dbo.f_sqlserver_split_count('120,34012,301,05045',',') --select dbo.f_sqlserver_split_count('abc1234a12348991234','1234') --select dbo.f_sqlserver_split_count('abc',',') create function [dbo].f_sqlserver_split_count(@long_str varchar(8000),@split_str varchar(100)) returns int as begin declare @long_str_tmp varchar(8000),@short_str varchar(8000),@split_str_length int ,@recordCount int set @split_str_length = len(@split_str) set @recordCount = 0 if charindex(@split_str,@long_str)=1 --开头即是分割符号 则屏弃开头 set @long_str_tmp=substring(@long_str,@split_str_length+1,len(@long_str)-@split_str_length) else set @long_str_tmp=@long_str --如果结尾不是分割符号 则加上分隔符 if charindex(reverse(@split_str),reverse(@long_str_tmp))>1 set @long_str_tmp=@long_str_tmp+@split_str else set @long_str_tmp=@long_str_tmp while charindex(@split_str,@long_str_tmp)>0 begin set @short_str=substring(@long_str_tmp,1,charindex(@split_str,@long_str_tmp)-1) declare @long_str_tmp_len int,@split_str_position_end int set @long_str_tmp_len = len(@long_str_tmp) set @split_str_position_end = len(@short_str)+@split_str_length set @long_str_tmp=reverse(substring(reverse(@long_str_tmp),1,@long_str_tmp_len-@split_str_position_end)) if @short_str<>'' set @recordCount = @recordCount+1 end return @recordCount end
数据库操作:创建-附加-分离等
--附加 if db_id('libraryDB') is not null begin return end else begin exec sp_attach_db @dbname='libraryDB', @filename1='E:\LibrarySystem\data\libraryDB.mdf', @filename2='E:\LibrarySystem\data\libraryDB.ldf' end --分离 if db_id('libraryDB') is not null begin use master exec sp_detach_db 'libraryDB' return end --Sql启动外围 Exec sp_configure 'show advanced options',1 reconfigure Exec sp_configure 'xp_cmdshell',1 reconfigure go --调用DOs命令创建文件夹 Exec xp_cmdshell 'md E:\library',no_output go --=======================================创建LibraryDb数据库=================== --检查是否存在library数据库 if exists(select * from sys.databases where name='LibraryDb') begin drop database LibraryDb end go --创建数据库 create database LibraryDb on ( /*数据库文件的详细描述*/ name='LibraryDb', ----主数据库文件的逻辑名 filename='E:\library\LibraryDb.mdf', ----主数据文件的物理名 size=3mb, --主文件的初始大小 maxsize=100mb, --主文件最大值 filegrowth=10% --增长率 ) log on ( /*日志文件的详细描述*/ NAME = 'libraryDB_ldf', --日志文件的逻辑名 FILENAME = 'E:\library\libraryDB_ldf.ldf', --日志文件的物理名 SIZE = 1MB, --初始大小 MAXSIZE = 15MB, --最大值 FILEGROWTH = 10% --增长率 ) go use LibraryDb go
sqlserver 基本语句