用于确定存储过程是只读还是读写的脚本 [英] Script to determine if a stored procedure is readonly or read-write
问题描述
我需要审核我们所有的存储过程，其中有数千个，并确定哪些是只读的或读写的.我想知道是否有人知道准确地做到这一点的好方法.
I have a requirement to audit all of our stored procedures, thousands of them, and determine which are readonly, or read-write. I was wondering if anyone knows of a good way to do it accurately.
到目前为止，我已经编写了自己的脚本，但准确率只有 85%.我绊倒了真正只读的存储过程，但它们创建了一些临时表.就我而言，这些是只读的.我也不能忽略这些，因为有很多读写过程也使用临时表.
I have written my own script so far, but I only get ~85% accuracy. I trip up on the stored procedures that are really readonly but they create a few temp tables. For my purposes these are readonly. I cannot just ignore these either, because there are a lot of read-write procedures working with temp tables too.
通过查看我知道的 20 个非常复杂的过程并将它们与我从查询中得到的结果进行比较，我获得了大约 85% 的准确度.
I got to roughly ~85% accuracy by looking at 20 procedures I know that are pretty complex and comparing them to the results I got from the query.
这是我目前使用的查询:
Here is the query I am currently using:
CREATE TABLE tempdb.dbo.[_tempProcs]
(objectname varchar(150), dbname varchar(150), ROUTINE_DEFINITION varchar(4000))
GO
EXEC sp_MSforeachdb
'USE [?]
DECLARE @dbname VARCHAR(200)
SET @dbname = DB_NAME()
IF 1 = 1 AND ( @dbname NOT IN (''master'',''model'',''msdb'',''tempdb'',''distribution'')
BEGIN
EXEC(''
INSERT INTO tempdb.dbo.[_tempProcs](objectname, dbname, ROUTINE_DEFINITION)
SELECT ROUTINE_NAME AS ObjectName, ''''?'''' AS dbname, ROUTINE_DEFINITION
FROM [?].INFORMATION_SCHEMA.ROUTINES WITH(NOLOCK)
WHERE ROUTINE_DEFINITION LIKE ''''%INSERT [^]%''''
OR ROUTINE_DEFINITION LIKE ''''%UPDATE [^]%''''
OR ROUTINE_DEFINITION LIKE ''''%INTO [^]%''''
OR ROUTINE_DEFINITION LIKE ''''%DELETE [^]%''''
OR ROUTINE_DEFINITION LIKE ''''%CREATE TABLE[^]%''''
OR ROUTINE_DEFINITION LIKE ''''%DROP [^]%''''
OR ROUTINE_DEFINITION LIKE ''''%ALTER [^]%''''
OR ROUTINE_DEFINITION LIKE ''''%TRUNCATE [^]%''''
AND ROUTINE_TYPE=''''PROCEDURE''''
'')
END
'
GO
SELECT * FROM tempdb.dbo.[_tempProcs] WITH(NOLOCK)
我还没有完善它，目前我只想专注于可写的查询，看看我是否能得到它的准确.另外一个问题是 ROUTINE_DEFINITION 只给出前 4000 个字符，所以我可能会错过任何在 4000 个字符长度之后写入的字符.我实际上可能会得到一些建议的组合.获取此查询返回的 proc 列表，然后进一步尝试 Arrons 建议，看看我是否可以淘汰更多.如果准确率达到 95%，我会很高兴.
I have not refined it yet, at the moment I just want to focus on the writeable queries and see if I can get it accurate. Also one other issue is that the ROUTINE_DEFINITION only gives the first 4000 characters, so I might miss any that are writing after the 4000 char length. I might actually end up with a combination of suggestions. Get a list of procs that this query returns, and then further try Arrons suggestion and see if I can weed out even more. I would be happy with 95% accuracy.
我会再过一天左右，看看我是否能得到任何进一步的建议，但到目前为止非常感谢你.
I will give this another day or so to see if I can get any further suggestions, but thank you very much so far.
[最终编辑]好的，这就是我最终做的事情，看起来我至少获得了 95% 的准确率，可能会更高.我试图迎合我能想到的任何场景.
[FINAL EDIT] Ok, here is what I ended up doing, and it looks like I am getting at least 95% accuracy, could be higher. I have attempted to cater for any scenario that I could come up with.
我将存储过程脚本化到文件中，并编写了一个 c# winform 应用程序来解析文件并找到对真实数据库进行合法写入"的文件.
I scripted out the stored procedures to files, and wrote a c# winform application to parse over the files and find the ones that have legitimate 'writes' to the real database.
我很高兴为我在这里使用的状态引擎发布此代码，但没有任何保证.我面临着交付的压力，真的没有时间美化代码，用漂亮的变量名等进行重构，并在其中添加漂亮的注释，我有 3 个小时的时间来完成，我只是挤进去了，所以对于那些关心并可能在未来提供帮助的人，这里是:
I am happy to post this code for the state engine I used up here, but comes with no gurantees. I am under pressure to deliver, and really did not have time to beautify the code, and refactor with nice variable names etc and put nice comments in it either, I had 3 hours to do it, and I just squeezed it in, so for those who cares, and might help in the future, here it is:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
namespace SQLParser
{
public class StateEngine
{
public static class CurrentState
{
public static bool IsInComment;
public static bool IsInCommentBlock;
public static bool IsInInsert;
public static bool IsInUpdate;
public static bool IsInDelete;
public static bool IsInCreate;
public static bool IsInDrop;
public static bool IsInAlter;
public static bool IsInTruncate;
public static bool IsInInto;
}
public class ReturnState
{
public int LineNumber { get; set; }
public bool Value { get; set; }
public string Line { get; set; }
}
private static int _tripLine = 0;
private static string[] _lines;
public ReturnState ParseFile(string fileName)
{
var retVal = false;
_tripLine = 0;
ResetCurrentState();
_lines = File.ReadAllLines(fileName);
for (int i = 0; i < _lines.Length; i++)
{
retVal = ParseLine(_lines[i], i);
//return true the moment we have a valid case
if (retVal)
{
ResetCurrentState();
return new ReturnState() { LineNumber = _tripLine, Value = retVal, Line = _lines[_tripLine] };
}
}
if (CurrentState.IsInInsert ||
CurrentState.IsInDelete ||
CurrentState.IsInUpdate ||
CurrentState.IsInDrop ||
CurrentState.IsInAlter ||
CurrentState.IsInTruncate)
{
retVal = true;
ResetCurrentState();
return new ReturnState() { LineNumber = _tripLine, Value = retVal, Line = _lines[_tripLine] };
}
return new ReturnState() { LineNumber = -1, Value = retVal };
}
private static void ResetCurrentState()
{
CurrentState.IsInAlter = false;
CurrentState.IsInCreate = false;
CurrentState.IsInDelete = false;
CurrentState.IsInDrop = false;
CurrentState.IsInInsert = false;
CurrentState.IsInTruncate = false;
CurrentState.IsInUpdate = false;
CurrentState.IsInInto = false;
CurrentState.IsInComment = false;
CurrentState.IsInCommentBlock = false;
}
private static bool ParseLine(string sqlLine, int lineNo)
{
var retVal = false;
var _currentWord = 0;
var _tripWord = 0;
var _offsetTollerance = 4;
sqlLine = sqlLine.Replace("\t", " ");
//This would have been set in previous line, so reset it
if (CurrentState.IsInComment)
CurrentState.IsInComment = false;
var words = sqlLine.Split(char.Parse(" ")).Where(x => x.Length > 0).ToArray();
for (int i = 0; i < words.Length; i++)
{
if (string.IsNullOrWhiteSpace(words[i]))
continue;
_currentWord += 1;
if (CurrentState.IsInCommentBlock && words[i].EndsWith("*/") || words[i] == "*/") { CurrentState.IsInCommentBlock = false; }
if (words[i].StartsWith("/*")) { CurrentState.IsInCommentBlock = true; }
if (words[i].StartsWith("--") && !CurrentState.IsInCommentBlock) { CurrentState.IsInComment = true; }
if (words[i].Length == 1 && CurrentState.IsInUpdate)
{
//find the alias table name, find 'FROM' and then next word
var tempAlias = words[i];
var tempLine = lineNo;
for (int l = lineNo; l < _lines.Length; l++)
{
var nextWord = "";
var found = false;
var tempWords = _lines[l].Replace("\t", " ").Split(char.Parse(" ")).Where(x => x.Length > 0).ToArray();
for (int m = 0; m < tempWords.Length; m++)
{
if (found) { break; }
if (tempWords[m].ToLower() == tempAlias && tempWords[m - m == 0 ? m : 1].ToLower() != "update")
{
nextWord = m == tempWords.Length - 1 ? "" : tempWords[m + 1].ToString();
var prevWord = m == 0 ? "" : tempWords[m - 1].ToString();
var testWord = "";
if (nextWord.ToLower() == "on" || nextWord == "")
{
testWord = prevWord;
}
if (prevWord.ToLower() == "from")
{
testWord = nextWord;
}
found = true;
if (testWord.StartsWith("#") || testWord.StartsWith("@"))
{
ResetCurrentState();
}
break;
}
}
if (found) { break; }
}
}
if (!CurrentState.IsInComment && !CurrentState.IsInCommentBlock)
{
#region SWITCH
if (words[i].EndsWith(";"))
{
retVal = SetStateReturnValue(retVal);
ResetCurrentState();
return retVal;
}
if ((CurrentState.IsInCreate || CurrentState.IsInDrop && (words[i].ToLower() == "procedure" || words[i].ToLower() == "proc")) && (lineNo > _tripLine ? 1000 : _currentWord - _tripWord) < _offsetTollerance)
ResetCurrentState();
switch (words[i].ToLower())
{
case "insert":
//assume that we have parsed all lines/words and got to next keyword, so return previous state
retVal = SetStateReturnValue(retVal);
if (retVal)
return retVal;
CurrentState.IsInInsert = true;
_tripLine = lineNo;
_tripWord = _currentWord;
continue;
case "update":
//assume that we have parsed all lines/words and got to next keyword, so return previous state
retVal = SetStateReturnValue(retVal);
if (retVal)
return retVal;
CurrentState.IsInUpdate = true;
_tripLine = lineNo;
_tripWord = _currentWord;
continue;
case "delete":
//assume that we have parsed all lines/words and got to next keyword, so return previous state
retVal = SetStateReturnValue(retVal);
if (retVal)
return retVal;
CurrentState.IsInDelete = true;
_tripLine = lineNo;
_tripWord = _currentWord;
continue;
case "into":
//assume that we have parsed all lines/words and got to next keyword, so return previous state
//retVal = SetStateReturnValue(retVal, lineNo);
//if (retVal)
// return retVal;
CurrentState.IsInInto = true;
_tripLine = lineNo;
_tripWord = _currentWord;
continue;
case "create":
//assume that we have parsed all lines/words and got to next keyword, so return previous state
retVal = SetStateReturnValue(retVal);
if (retVal)
return retVal;
CurrentState.IsInCreate = true;
_tripLine = lineNo;
_tripWord = _currentWord;
continue;
case "drop":
//assume that we have parsed all lines/words and got to next keyword, so return previous state
retVal = SetStateReturnValue(retVal);
if (retVal)
return retVal;
CurrentState.IsInDrop = true;
_tripLine = lineNo;
continue;
case "alter":
//assume that we have parsed all lines/words and got to next keyword, so return previous state
retVal = SetStateReturnValue(retVal);
if (retVal)
return retVal;
CurrentState.IsInAlter = true;
_tripLine = lineNo;
_tripWord = _currentWord;
continue;
case "truncate":
//assume that we have parsed all lines/words and got to next keyword, so return previous state
retVal = SetStateReturnValue(retVal);
if (retVal)
return retVal;
CurrentState.IsInTruncate = true;
_tripLine = lineNo;
_tripWord = _currentWord;
break;
default:
break;
}
#endregion
if (CurrentState.IsInInsert || CurrentState.IsInDelete || CurrentState.IsInUpdate || CurrentState.IsInDrop || CurrentState.IsInAlter || CurrentState.IsInTruncate || CurrentState.IsInInto)
{
if ((words[i].StartsWith("#") || words[i].StartsWith("@") || words[i].StartsWith("dbo.#") || words[i].StartsWith("dbo.@")) && (lineNo > _tripLine ? 1000 : _currentWord - _tripWord) < _offsetTollerance)
{
ResetCurrentState();
continue;
}
}
if ((CurrentState.IsInInsert || CurrentState.IsInInto || CurrentState.IsInUpdate) && (((_currentWord != _tripWord) && (lineNo > _tripLine ? 1000 : _currentWord - _tripWord) < _offsetTollerance) || (lineNo > _tripLine)))
{
retVal = SetStateReturnValue(retVal);
if (retVal)
return retVal;
}
}
}
return retVal;
}
private static bool SetStateReturnValue(bool retVal)
{
if (CurrentState.IsInInsert ||
CurrentState.IsInDelete ||
CurrentState.IsInUpdate ||
CurrentState.IsInDrop ||
CurrentState.IsInAlter ||
CurrentState.IsInTruncate)
{
retVal = (CurrentState.IsInInsert ||
CurrentState.IsInDelete ||
CurrentState.IsInUpdate ||
CurrentState.IsInDrop ||
CurrentState.IsInAlter ||
CurrentState.IsInTruncate);
}
return retVal;
}
}
}
用法
var fileResult = new StateEngine().ParseFile(*path and filename*);
推荐答案
您可以尝试将 sys.sql_modules 与词解析表值函数结合使用.将 UDF 重命名为 fnParseSQLWords，用于标识注释在右侧行添加了一个条件并将所有 varchar 更改为 nvarchar添加和 w.id >1; 到主选择语句，以避免在 CREATE 上过滤时命中前导 CREATE PROC.
You could try combining sys.sql_modules with a word-parsing table-valued function.
renamed UDF to fnParseSQLWords, which identifies comments
added a condition to the RIGHT line and changed all varchar to nvarchar
Added and w.id > 1; to the main select statement to avoid hits on the leading CREATE PROC when filtering on CREATE.
create function [dbo].[fnParseSQLWords](@str nvarchar(max), @delimiter nvarchar(30)='%[^a-zA-Z0-9\_]%')
returns @result table(id int identity(1,1), bIsComment bit, word nvarchar(max))
begin
if left(@delimiter,1)<>'%' set @delimiter='%'+@delimiter;
if right(@delimiter,1)<>'%' set @delimiter+='%';
set @str=rtrim(@str);
declare @pi int=PATINDEX(@delimiter,@str);
declare @s2 nvarchar(2)=substring(@str,@pi,2);
declare @bLineComment bit=case when @s2='--' then 1 else 0 end;
declare @bBlockComment bit=case when @s2='/*' then 1 else 0 end;
while @pi>0 begin
insert into @result select case when (@bLineComment=1 or @bBlockComment=1) then 1 else 0 end
, LEFT(@str,@pi-1) where @pi>1;
set @s2=substring(@str,@pi,2);
set @str=RIGHT(@str,len(@str)-@pi);
set @pi=PATINDEX(@delimiter,@str);
set @bLineComment=case when @s2='--' then 1 else @bLineComment end;
set @bBlockComment=case when @s2='/*' then 1 else @bBlockComment end;
set @bLineComment=case when left(@s2,1) in (char(10),char(13)) then 0 else @bLineComment end;
set @bBlockComment=case when @s2='*/' then 0 else @bBlockComment end;
end
insert into @result select case when (@bLineComment=1 or @bBlockComment=1) then 1 else 0 end
, @str where LEN(@str)>0;
return;
end
GO
-- List all update procedures
select distinct ProcName=p.name --, w.id, w.bIsComment, w.word
from sys.sql_modules m
inner join sys.procedures p on p.object_id=m.object_id
cross apply dbo.fnParseSQLWords(m.[definition], default) w
where w.word in ('INSERT','UPDATE','DELETE','INTO','CREATE','DROP','ALTER','TRUNCATE')
and w.bIsComment=0
and w.id > 1;
GO
这篇关于用于确定存储过程是只读还是读写的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
