none
Being able to write a sql clr function that will script the procedure/view/function/trigger in sql server as a different object name RRS feed

  • Question

  • I was thinking I can do this using Microsoft.SqlServer.TransactSql.ScriptDom , Passing a SQL definition of the object to the clr function using this dll and change change passed sql (which can be create/alter of a procedure/function/view/trigger) object name. Like being able to change

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

    to

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

    Can I do that?

    Gokhan Varol

    Tuesday, September 17, 2019 2:48 PM

All replies

  • D'oh? What's wrong with renaming objects?

    See sp_rename or Stored​Procedure.​Rename(String).

    Tuesday, September 17, 2019 3:01 PM
  • When you rename the object with sp_rename the definition of the object stored in system metadata will still have the old name, I want to be able to retrieve the object definition with the correct name (or maybe get the object definition as a different name in some cases), I want a sql clr function that will get the definition of the object as plain text and the actual name and it will replace the definition with the actual name (there can be comments around or different whitespaces, schema/name could be quoted, schema may not exists etc I need to be able to safely get the right definition. All working with a plain sql text without running additional queries against sql server.

    a clr function like

    select dbo.GetNewScript(@SQLMain, @NewSchemaAndObjectName, @Return_Alter_or_Create_Script)

    and that will replace the name used in @SQLMain with NewSchemaAndObjectName and return the full script, conditionally I may want an ALTER script even though I passed a CREATE script vice versa.


    Gokhan Varol


    • Edited by GV1973 Tuesday, September 17, 2019 4:48 PM
    Tuesday, September 17, 2019 4:28 PM
  • I may want to store object scripts in a table as a backup, or from within sql server dump all scripts to a folder so that I can auto svn all objects etc.

    Gokhan Varol

    Tuesday, September 17, 2019 4:31 PM
  • I was thinking I can do this using Microsoft.SqlServer.TransactSql.ScriptDom , Passing a SQL definition of the object to the clr function using this dll and change change passed sql (which can be create/alter of a procedure/function/view/trigger) object name. Like being able to change

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

    to

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

    Can I do that?

    Gokhan Varol

    I am not an object oriented programmer or an advanced one, this is what I came up with, seem to work so far.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using Microsoft.SqlServer.Server;
    using Microsoft.SqlServer.TransactSql.ScriptDom;
    using System.Data.SqlTypes;
    using System.IO;
    using System.Text.RegularExpressions;
    
    namespace ScriptDomTest
    {
        class Program
        {
            static void Main(string[] args)
            {
                String mstring = File.ReadAllText(@"c:\temp\blah.sql");
                SqlString Mstring2 = RestructObjectNameAndAlter((SqlString)mstring, (SqlBoolean)false, (SqlString)"[GV].[Object]", (SqlBoolean)true);
                if (!Mstring2.IsNull)
                {
                    File.Delete(@"c:\temp\blah2.sql");
                    File.AppendAllText(@"c:\temp\blah2.sql", Mstring2.Value);
                }
            }
    
            static readonly Regex _SpaceBeforeNewLine = new Regex(@"[ \t]*\r\n", RegexOptions.Multiline);
            static readonly Regex _LeadingAndTrailingWhiteSpace = new Regex(@"^[\s]+|[\s]+$");
            static readonly string NewLine = "\r\n";
    
            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;
    
                bool bReturnAlter = ReturnAlter.IsNull ? false : ReturnAlter.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 icount = 0;
                    int iNameStarted = -1;
                    foreach (TSqlParserToken token in tokens)
                    {
                        //icount++;
                        //if (icount > 100)
                        //    return SqlString.Null;
    
                        Console.WriteLine(string.Format("TokenType {0}\tValue {1}", token.TokenType, token.Text));
                        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)
                                        return SqlString.Null;
                                    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;
            }
        }
    }
    


    Gokhan Varol

    Wednesday, September 18, 2019 2:19 AM
  • hmm, there exists already tools for this. E.g. Redagte SQL Prompt (Smart Rename) or ApexSQL Refactor (Safe Rename) (free).

    When you're looking for tool chain components, then you should imho really consider one of the available commercial solutions. Cause they are doing their job pretty well, and you'll get a lot of functionality for your money.

    Wednesday, September 18, 2019 10:02 AM
  • I use Redgate SQL Source Control. But ApexSQL does also offer an integration to code repositories.
    Wednesday, September 18, 2019 10:03 AM