能够编写一个 sql clr 函数，该函数将 sql 服务器中的过程/视图/函数/触发器编写为不同的对象名称

Question

我想我可以使用Microsoft.SqlServer.TransactSql.ScriptDom来做到这一点，使用此DLL将对象的SQL定义传递给CLR函数，并更改传递的SQL(可以创建/更改过程/函数/视图/触发器(对象名称。 喜欢能够改变

CREATE VIEW [dbo].Oldname as select * from ..

自

CREATE VIEW [NewSchema].[NewName] as select * from ..

我能做到吗？

Answer 1

这似乎对我们有用。

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.TransactSql.ScriptDom;
namespace ScriptDomClr
{
public partial class ScriptDom
{
static readonly Regex _SpaceBeforeNewLine = new Regex(@"[ t]*rn", RegexOptions.Multiline);
static readonly Regex _LeadingAndTrailingWhiteSpace = new Regex(@"^[s]+|[s]+$");
static readonly string NewLine = "rn";
static string TrimMultilineLocal(string Input)
{
return _LeadingAndTrailingWhiteSpace.Replace(_SpaceBeforeNewLine.Replace(Input, NewLine), "");
}
public static SqlString RestructObjectNameAndAlter(SqlString SQL, SqlBoolean ReturnAlter, SqlString ReplaceObjectFullName, SqlBoolean Trim)
{
if (SQL.IsNull || string.IsNullOrWhiteSpace(SQL.Value))
return SqlString.Null;
bool ReplaceObjectName = !ReplaceObjectFullName.IsNull && !string.IsNullOrWhiteSpace(ReplaceObjectFullName.Value) ? true : false;
string strSQL;
if (!Trim.IsNull && Trim.Value == true)
strSQL = TrimMultilineLocal(SQL.Value);
else
strSQL = SQL.Value;
try
{
StringBuilder mstring = new StringBuilder("");
TSql150Parser SqlParser = new TSql150Parser(false);

TSql150Parser mParser = new TSql150Parser(true);
Sql150ScriptGenerator gen = new Sql150ScriptGenerator();
System.Collections.Generic.IList<ParseError> Errors = null;
System.Collections.Generic.IList<TSqlParserToken> tokens = mParser.GetTokenStream(new StringReader(strSQL), out Errors);
int FirstIsCreateOrAlter = -1;
int iNameStarted = -1;
foreach (TSqlParserToken token in tokens)
{
switch (token.TokenType)
{
case TSqlTokenType.SingleLineComment:
case TSqlTokenType.VerticalLine:
case TSqlTokenType.MultilineComment:
case TSqlTokenType.WhiteSpace:
{
if (iNameStarted == 1)
{
if (ReplaceObjectName)
mstring.Append(ReplaceObjectFullName.Value);
mstring.Append(strSQL.Substring(token.Offset, strSQL.Length - token.Offset));
return (SqlString)mstring.ToString();
}
mstring.Append(token.Text);
}
break;
case TSqlTokenType.Procedure:
case TSqlTokenType.View:
case TSqlTokenType.Function:
case TSqlTokenType.Trigger:
{
if (iNameStarted == 1)
return SqlString.Null;
if (FirstIsCreateOrAlter != 1)
return SqlString.Null;
mstring.Append(token.Text.ToUpper());
iNameStarted = 0;
}
break;
case TSqlTokenType.Identifier:
case TSqlTokenType.QuotedIdentifier:
case TSqlTokenType.Dot:
{
if (iNameStarted == 0)
iNameStarted = 1;
}
break;
case TSqlTokenType.Proc:
{
if (iNameStarted == 1)
return SqlString.Null;
if (FirstIsCreateOrAlter != 1)
return SqlString.Null;
mstring.Append("PROCEDURE");
iNameStarted = 0;
}
break;
case TSqlTokenType.As:
{
if (iNameStarted == 1)
{
mstring.Append(strSQL.Substring(token.Offset, strSQL.Length - token.Offset));
return (SqlString)mstring.ToString();
}
if (FirstIsCreateOrAlter != 1)
return SqlString.Null;
mstring.Append("AS");
}
break;
case TSqlTokenType.Create:
{
if (iNameStarted == 1)
return SqlString.Null;
if (FirstIsCreateOrAlter != -1)
return SqlString.Null;
else
FirstIsCreateOrAlter = 1;
if (!ReturnAlter.IsNull && ReturnAlter.Value == true)
{
mstring.Append("ALTER");
}
else
{
mstring.Append("CREATE");
}
if (!ReplaceObjectName)
{
mstring.Append(strSQL.Substring(token.Offset + token.Text.Length, strSQL.Length - token.Offset - token.Text.Length));
return (SqlString)mstring.ToString();
}
}
break;
case TSqlTokenType.Alter:
{
if (iNameStarted == 1)
return SqlString.Null;
if (FirstIsCreateOrAlter != -1)
return SqlString.Null;
else
FirstIsCreateOrAlter = 1;
if (!ReturnAlter.IsNull && ReturnAlter.Value == false)
{
mstring.Append("CREATE");
}
else
{
mstring.Append("ALTER");
}
if (!ReplaceObjectName)
{
mstring.Append(strSQL.Substring(token.Offset + token.Text.Length, strSQL.Length - token.Offset - token.Text.Length));
return (SqlString)mstring.ToString();
}
}
break;
default:
{
if (FirstIsCreateOrAlter != 1)
return SqlString.Null;
if (iNameStarted == 1)
{
mstring.Append(strSQL.Substring(token.Offset, strSQL.Length - token.Offset));
return (SqlString)mstring.ToString();
}
return SqlString.Null;
}
}
}
}
catch
{
}
return SqlString.Null;
}
}
}