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

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?

Your question doesn't make any sense at all. What are you trying to do? — Sean Lange, Sep 17 '19 at 14:53
Ex, someone renames the view from Namea to Nameb. Now the object_definition will still return "create view Namea as ..." I do get the script from the system and change the code of the view but I also need to alter the view as the Nameb now instead of Namea. Or I might want to take a copy of the view or some proc and create it as something else. We do a lot of script generation/ object creation without leaving tsql space in sql server. If you used ScriptDom objects before in .Net you will understand what I mean. — Gokhan, Sep 17 '19 at 16:19
Huh? If you change the name of a view the script from sql server will reflect the new name. I am sure this makes sense to you as your are close to your project but I can't even pretend to understand what you are asking. — Sean Lange, Sep 17 '19 at 16:38
I do not use SSMS or visual studio. Everything is done via TSQL. I am looking for a way to retrieve the object script, given what's stored in system metadata(which doesn't reflect the actual name of the object after rename) and the actual name (maybe some other name depending on what I want to do ) and retrive the script. A function like ... select dbo.GetNewScript(@SQLMain, @NewSchemaAndObjectName, @Return_Alter_Or_Create) — Gokhan, Sep 17 '19 at 16:46
We use a LOT of dynamic scripts, code generation without leaving tSQL environment. — Gokhan, Sep 17 '19 at 16:47
Are you saying that you are using sp_rename and then trying to retrieve the correct script to recreate the object? The documentation warns against this. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-2017 The very thing you are struggling with sounds like the thing the big red Caution box is talking about. — Sean Lange, Sep 17 '19 at 16:53
I just want to be able to script the object with the right name not trying to educate over 5000+ users we have, please can you help if you know how. — Gokhan, Sep 17 '19 at 20:15
No offense but why in the world do you have 5000+ users that can rename objects in your database??? Here is the problem mate, to retrieve the create/alter script you use sys.sql_modules which MS clearly states does NOT get updated when the name is changed. You could probably leverage sys.objects to get the current name assuming you know the object_id. Then go back to the definition in sys.sql_modules and replace the old name (which you would have to parse) with the name found in sys.objects. I think I would find a way to stop everybody being allowed to rename objects. — Sean Lange, Sep 18 '19 at 13:26
I am not planning on discussing confidential information related to the design. There is a need for s clr function i mentioned before. We have over few thousand databases, few hundredt offshore developers and quite a bit users. The createdate colum in sys.objects must not change for the objects and i need to be automatically and as fast as possible script the database code properly. Thank you for all your help telling me how to do this differently. — Gokhan, Sep 19 '19 at 01:04

score 0 · Answer 1 · answered Sep 19 '19 at 14:04

This seem to work for us.

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]*\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;

            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;
        }
    }
}

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

1 Answers1