SQL Script 杂记

1、提交sql server中未提交的事务

commit select @@TRANCOUNT

2、查询存储过程中包含某个字符串的所有存储过程

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Fetion%'
ORDER BY SPECIFIC_NAME

3、删除主键-更改类型-添加主键

ALTER TABLE entry_baseinfo drop pk_entry_baseinfo

ALTER TABLE entry_baseinfo ALTER COLUMN ID NVARCHAR(36) NOT NULL

ALTER TABLE entry_baseinfo ADD CONSTRAINT pk_entry_baseinfo PRIMARY KEY (ID,,UPDATE_ITEM)

4、游标

-声明一个游标
DECLARE MyCursor CURSOR FOR
SELECT A.Id
FROM dbo.tmp_qiye_final A
LEFT JOIN dbo.School B
ON A.Name = B.Name
WHERE A.SchoolCode IS NULL
AND EducationType = '大学'

DECLARE @SchoolCode varchar(50)

--打开一个游标
OPEN MyCursor

--循环一个游标
DECLARE @Id int
FETCH NEXT FROM MyCursor INTO @Id
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @SchoolCode = RIGHT('000000000' + CONVERT(varchar, (MAX(Code) + 1)), 9) FROM dbo.School WHERE [Level] = 1 AND AlumniSchoolType = 0
  UPDATE dbo.tmp_qiye_final SET SchoolCode = @SchoolCode WHERE Id = @Id
  FETCH NEXT FROM MyCursor INTO @Id
END

--关闭游标
CLOSE MyCursor
--释放资源
DEALLOCATE MyCursor

5、查看当前连接用户

SP_WHO

SP_WHO 'YOUR DATABASE USER NAME'

6、查看统计信息

SET STATISTICS IO ON

SET STATISTICS TIME ON

SET STATISTICS PROFILE ON

7、表值函数，通过分隔符将字符串转换成表

Create function [dbo].[SplitToTable]
(
@String varchar(8000),
@Seprate varchar(10)
)
returns @TempTable table(F1 varchar(100))
as
begin
declare @i int
   set @String=rtrim(ltrim(@String))
   set @i=charindex(@Seprate,@String)
   while @i>0
   begin
     insert @TempTable values(left(@String,@i-1))
     set @String=substring(@String,@i+1,len(@String)-@i)
     set @i=charindex(@Seprate,@String)
   end
   if (LEN(@String) > 0)
     insert @TempTable values(@String)
   return
end

8、。。。。。。