September 24, 2009 at 8:49 am
Matt Whitfield (9/24/2009)
Is there a particular reason you want to use ALTER as opposed to DROP then CREATE?Do you have permissions you'd like to maintain?
As I said, changing CREATE statements to ALTER statements *reliably* is not trivial.
Exactly so. We write all our sp scripts as "if not exists CREATE then ALTER". Then we can run the scripts multiple times without worrying about permissions.
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
September 24, 2009 at 9:06 am
In that case, you've really got three options:
1) (easiest) Just replace 'CREATE PROC' with 'ALTER PROC' in the resulting string, and hope that it doesn't come across any procedures that will break it.
2) (medium) Script as DROP then CREATE, but also script any associated permissions (possibly not valid if you have different permissions in dev/production environments)
3) (harder) Parse the string completely, looking for the words 'CREATE PROCEDURE' that are under the following rules:
i) Are not in a string
ii) Are not in an end-of-line (-- ) or multi-line (/* ... */) comment
iii) Are the first keywords in the batch
If you want to go a step further and correct object names that are incorrect from having used sp_rename - then that's another level entirely.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 26, 2009 at 9:06 am
You can use optional parameters to ensure that only the very first CREATE PROC is changed:
sql = Replace(sql, "CREATE PROC", "ALTER PROC", 1, 1)
which tells VB to start at the beginning & replace only one time. The only way that this could fail is if the string "CREATE PROC" is embedded in comments that precede the actual CREATE PROC statement. You'd have to examine any comments to verify that.
In the event that your code has extra spaces between CREATE and PROC, you would remove them before doing the replace, like this:
For x = 20 To 1 Step -1
sql = Replace(sql, "CREATE " & String(x, " ") & "PROC", "CREATE PROC")
Next x
September 26, 2009 at 12:49 pm
I'm quite aware how replace works 🙂
If you must go down the string replace route, then at least use a regex to match an undefined number of spaces, rather than brute forcing 20 string replaces on the entire proc. Also then you could cater for comments between create and proc which are also valid.
But... so lame. Especially if you do the examining of comments bit - you may as well just properly lex the statement if you were going to do that.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 26, 2009 at 5:41 pm
Matt, I have been here in the forums for the past six years to help others. There is no need to make ignorant comments. That being said,
Matt Whitfield (9/26/2009)
I'm quite aware how replace works 🙂
well maybe you "forgot" when you asked the question
Matt Whitfield (9/24/2009)
How would that handle a procedure which created another procedure dynamically?e.g.
CREATE PROCEDURE stproc_CreateMyProc
AS
EXEC ('CREATE PROCEDURE stproc_MyProc as SELECT ''hello'' ')
My revised solution does cover the case where an sp creates another sp. Althought that is definitely not a best-practice.
If you must go down the string replace route, then at least use a regex to match an undefined number of spaces, rather than brute forcing 20 string replaces on the entire proc. Also then you could cater for comments between create and proc which are also valid.
Would you kindly post your solution so we can all benefit from that approach. The replace must change only the CREATE...PROC portion of the sp text and nothing else.
My brute-force code touches only the CREATE..PROC phrase and nothing else.
BTW the only reason I threw in the "remove extra spaces" code was that when I tested my code against a very poorly-written database I found the "developers" had used as many as 12 spaces between the CREATE and the PROC. If the developer uses a single space then the point is moot.
September 26, 2009 at 6:09 pm
WILLIAM MITCHELL (9/26/2009)
Matt, I have been here in the forums for the past six years to help others. There is no need to make ignorant comments.
I don't think I've made any ignorant comments. Your efforts are commendable, I just don't happen to agree with you right here. Maybe you missed the smile.
WILLIAM MITCHELL (9/26/2009)
well maybe you "forgot" when you asked the question
No, that was simply one of the edge cases that would break that solution. I don't perceive replacing only the first one to be any more valid.
WILLIAM MITCHELL (9/26/2009)
My revised solution does cover the case where an sp creates another sp. Althought that is definitely not a best-practice.
The 20 space one didn't??
WILLIAM MITCHELL (9/26/2009)
Would you kindly post your solution so we can all benefit from that approach. The replace must change only the CREATE...PROC portion of the sp text and nothing else.
Sure. Note that the string tokenizer I use is based on Andrew Deren's example from code project, thus the header is preserved in it. Also I'm really not sure what this will format like... I won't be re-writing it in VB though...
Edit -> yeah, it formats quite badly. I'm pretty sure copying from that direct won't compile. If you actually want the solution, PM me your email address and I'll mail the cs as an attachment.
/********************************************************8
*Author: Andrew Deren
*Date: July, 2004
*
*StringTokenizer class. You can use this class in any way you want
* as long as this header remains in this file.
*
**********************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace FixCreate
{
///
///
public enum TokenKind
{
///
///
Unknown,
///
///
Word,
///
///
Number,
///
///
QuotedString,
///
///
WhiteSpace,
///
///
Symbol,
///
///
StartComment,
///
///
EndComment,
///
///
EOLComment,
///
///
StartSquare,
///
///
EndSquare,
///
///
EOL,
///
///
EOF
}
///
///
public class Token
{
///
///
///
int _line;
///
///
///
int _column;
///
///
///
string _value;
///
///
///
TokenKind _kind;
///
///
///
int _offset;
///
///
/// The value relating to the kind of the token
/// The value of the token
/// The line number on which the token occurs
/// The column number on which the token occurs
/// The offset of the token, in bytes from the start of the tokenized string
///
public Token(TokenKind Kind, string Value, int Line, int Column, int Offset)
{
this._kind = Kind;
this._value = Value;
this._line = Line;
this._column = Column;
this._offset = Offset;
}
///
///
public int Column
{
get { return this._column; }
}
///
///
///
public TokenKind Kind
{
get { return this._kind; }
}
///
///
public int Line
{
get { return this._line; }
}
///
///
public int Offset
{
get { return this._offset; }
}
///
///
public string Value
{
get { return this._value; }
}
}
///
///
public abstract class StringTokenizer
{
///
///
/// The first character to test
/// The second character to test
/// True if the specified characters start an end-of-line comment.
protected abstract bool startsEOLComment(char c1, char c2);
///
///
protected abstract bool hasSquareStringState { get; }
///
///
protected abstract char[] defaultSymbols { get; }
///
///
const char _EOF = (char)0;
///
///
int _line;
///
///
int _column;
///
///
int _pos;
///
///
string _data;
///
///
///
bool _ignoreWhiteSpace;
///
///
///
char[] _symbolChars;
///
///
int _saveLine;
///
///
int _saveCol;
///
///
int _savePos;
///
///
/// The string to tokenize
protected StringTokenizer(string data)
{
if (data == null)
throw new ArgumentNullException("data");
this._data = data;
Reset();
}
///
///
public char[] SymbolChars
{
get { return this._symbolChars; }
set { this._symbolChars = value; }
}
///
/// but EOL and whitespace inside of string will still be tokenized
///
public bool IgnoreWhiteSpace
{
get { return this._ignoreWhiteSpace; }
set { this._ignoreWhiteSpace = value; }
}
///
///
private void Reset()
{
this._ignoreWhiteSpace = false;
this._symbolChars = defaultSymbols;
_line = 1;
_column = 1;
_pos = 0;
}
///
///
/// The count of bytes to read forward past
/// The specified character
protected char LA(int count)
{
if (_pos + count >= _data.Length)
return _EOF;
else
return _data[_pos + count];
}
///
///
/// The next character
protected char Consume()
{
char ret = _data[_pos];
_pos++;
_column++;
return ret;
}
///
///
/// The value representing the type of token being created
/// The value of the token
/// The class instance
///
///
protected Token CreateToken(TokenKind kind, string value)
{
return new Token(kind, value, _line, _column, _pos - value.Length);
}
///
///
/// The value representing the type of token being created
/// The class instance
///
///
protected Token CreateToken(TokenKind kind)
{
string tokenData = _data.Substring(_savePos, _pos - _savePos);
return new Token(kind, tokenData, _saveLine, _saveCol, _savePos);
}
///
///
/// The next class instance from the data
///
public Token Next()
{
ReadToken:
char ch = LA(0);
switch (ch)
{
case _EOF:
return null;
case ' ':
case '\t':
{
if (this._ignoreWhiteSpace)
{
Consume();
goto ReadToken;
}
else
return ReadWhitespace();
}
case '0':
case '1':
case '2':
case '3':
case '4':
case '5':
case '6':
case '7':
case '8':
case '9':
return ReadNumber();
case '\r':
{
StartRead();
Consume();
if (LA(0) == '')
Consume();// on DOS/Windows we have \r for new line
_line++;
_column = 1;
return CreateToken(TokenKind.EOL);
}
case '':
{
StartRead();
Consume();
_line++;
_column = 1;
return CreateToken(TokenKind.EOL);
}
case '"':
case '\'':
{
return ReadString();
}
default:
{
if (Char.IsLetter(ch) || ch == '_' || ch == '#')
return ReadWord();
else if ((ch == '[') && (hasSquareStringState))
{
StartRead();
Consume();
return CreateToken(TokenKind.StartSquare);
}
else if ((ch == ']') && (hasSquareStringState) && (LA(1) != ']'))
{
StartRead();
Consume();
return CreateToken(TokenKind.EndSquare);
}
else if (startsEOLComment(ch, LA(1))) // ((ch == '-') && (LA(1) == '-'))
{
StartRead();
Consume();
Consume();
return CreateToken(TokenKind.EOLComment);
}
else if ((ch == '/') && (LA(1) == '*'))
{
StartRead();
Consume();
Consume();
return CreateToken(TokenKind.StartComment);
}
else if ((ch == '*') && (LA(1) == '/'))
{
StartRead();
Consume();
Consume();
return CreateToken(TokenKind.EndComment);
}
else if (IsSymbol(ch))
{
StartRead();
Consume();
return CreateToken(TokenKind.Symbol);
}
else
{
StartRead();
Consume();
return CreateToken(TokenKind.Unknown);
}
}
}
}
///
///
///
private void StartRead()
{
_saveLine = _line;
_saveCol = _column;
_savePos = _pos;
}
///
///
/// A class instance
///
protected Token ReadWhitespace()
{
StartRead();
Consume(); // consume the looked-ahead whitespace char
while (true)
{
char ch = LA(0);
if (ch == '\t' || ch == ' ')
Consume();
else
break;
}
return CreateToken(TokenKind.WhiteSpace);
}
///
///
/// A class instance
///
protected Token ReadNumber()
{
StartRead();
bool hadDot = false;
Consume(); // read first digit
while (true)
{
char ch = LA(0);
if ((ch >= '0') && (ch <= '9'))
{
Consume();
}
else if (ch == '.' && !hadDot)
{
hadDot = true;
Consume();
}
else
break;
}
return CreateToken(TokenKind.Number);
}
///
///
/// A class instance
///
protected Token ReadWord()
{
StartRead();
Consume(); // consume first character of the word
while (true)
{
char ch = LA(0);
if (Char.IsLetter(ch) || ch == '_' || Char.IsNumber(ch))
Consume();
else
break;
}
return CreateToken(TokenKind.Word);
}
///
/// If "" (2 quotes), or '' (2 single quotes) are found, then they are consumed as
/// part of the string
///
/// A class instance
///
protected Token ReadString()
{
StartRead();
Consume(); // read "
while (true)
{
char ch = LA(0);
if (ch == _EOF)
break;
else if (ch == '\r')// handle CR in strings
{
Consume();
if (LA(0) == '')// for DOS & windows
Consume();
_line++;
_column = 1;
}
else if (ch == '')// new line in quoted string
{
Consume();
_line++;
_column = 1;
}
else if (ch == '"')
{
Consume();
// sort - escaping for C# / SQL is different. Don't care yet.
if (LA(0) != '"')
break;// done reading, and this quotes does not have escape character
else
Consume(); // consume second ", because first was just an escape
}
else if (ch == '\'')
{
Consume();
if (LA(0) != '\'')
break;// done reading, and this quotes does not have escape character
else
Consume(); // consume second ", because first was just an escape
}
else
Consume();
}
return CreateToken(TokenKind.QuotedString);
}
///
///
/// The character to check
/// True if c is a symbol.
protected bool IsSymbol(char c)
{
for (int i = 0; i < _symbolChars.Length; i++)
if (_symbolChars == c)
return true;
return false;
}
}
///
///
public class SQLStringTokenizer : StringTokenizer
{
///
///
/// The string to be tokenized
public SQLStringTokenizer(string Data)
: base(Data)
{
}
///
///
/// The first character to test
/// The second character to test
/// True if the characters represent the start of an EOL Comment
/// Returns true if both characters are '-' for SQL.
protected override bool startsEOLComment(char c1, char c2)
{
return ((c1 == '-') && (c2 == '-'));
}
///
///
/// Returns true for SQL.
protected override bool hasSquareStringState
{
get
{
return true;
}
}
///
///
/// Returns '=', '+', '-', '/', ',', '.', '*', '~', '!', '@', '%', '^', '&', '(', ')', '[', ']', ':', ';', '<', '>', '|', '\' for SQL.
protected override char[] defaultSymbols
{
get
{
return new char[] { '=', '+', '-', '/', ',', '.', '*', '~', '!', '@', '%', '^', '&', '(', ')', '[', ']', ':', ';', '', '|', '\\' };
}
}
}
class Program
{
///
///
enum _stringState
{
///
///
Default,
///
///
Comment,
///
///
SquareString,
///
///
EOLComment
}
///
///
/// The string containing the CREATE definition
/// A string containing an ALTER definition
public static string ChangeCreateToAlter(string sqlStatement)
{
StringTokenizer tokenizer = new SQLStringTokenizer(sqlStatement);
Token t;
_stringState state = _stringState.Default;
while ((t = tokenizer.Next()) != null)
{
if (((t.Kind == TokenKind.Word) ||
(t.Kind == TokenKind.Unknown)) &&
(state == _stringState.Default))
{
if (string.Equals(t.Value, "create", StringComparison.OrdinalIgnoreCase))
{
if (t.Offset > 0)
{
return sqlStatement.Substring(0, t.Offset) + "ALTER" + sqlStatement.Substring(t.Offset + 6);
}
else
{
return "ALTER" + sqlStatement.Substring(t.Offset + 6);
}
}
}
// state transitions
else if ((t.Kind == TokenKind.StartComment) && (state == _stringState.Default))
{
state = _stringState.Comment;
}
else if ((t.Kind == TokenKind.EndComment) && (state == _stringState.Comment))
{
state = _stringState.Default;
}
else if ((t.Kind == TokenKind.EOLComment) && (state == _stringState.Default))
{
state = _stringState.EOLComment;
}
else if ((t.Kind == TokenKind.EOL) && (state == _stringState.EOLComment))
{
state = _stringState.Default;
}
else if ((t.Kind == TokenKind.StartSquare) && (state == _stringState.Default))
{
state = _stringState.SquareString;
}
else if ((t.Kind == TokenKind.EndSquare) && (state == _stringState.SquareString))
{
state = _stringState.Default;
}
}
// well we couldn't find a 'CREATE' - just return it plain
return sqlStatement;
}
static void Main(string[] args)
{
string testString = "/* CREATE PROCEDURE -> ALTER PROCEDURE example */" + Environment.NewLine +
"-- CREATE PROCEDURE" + Environment.NewLine +
"CREATE /* CREATE PROCEDURE */ PROCEDURE [dbo].[proc_test]" + Environment.NewLine +
"AS" + Environment.NewLine +
"PRINT 'CREATE PROCEDURE';";
Console.WriteLine(testString);
Console.WriteLine(ChangeCreateToAlter(testString));
}
}
}
Output is:
C:\Documents and Settings\Matt Whitfield\My Documents\Visual Studio 2008\Projects\FixCreate\FixCreate\bin\Debug>FixCreate.exe
/* CREATE PROCEDURE -> ALTER PROCEDURE example */
-- CREATE PROCEDURE
CREATE /* CREATE PROCEDURE */ PROCEDURE [dbo].[proc_test]
AS
PRINT 'CREATE PROCEDURE';
/* CREATE PROCEDURE -> ALTER PROCEDURE example */
-- CREATE PROCEDURE
ALTER /* CREATE PROCEDURE */ PROCEDURE [dbo].[proc_test]
AS
PRINT 'CREATE PROCEDURE';
WILLIAM MITCHELL (9/26/2009)
My brute-force code touches only the CREATE..PROC phrase and nothing else.
No, it either replaces every occurrence, or a set number of occurrences. It doesn't pay any attention to lexical state, or edge-case syntax uses (like putting a comment in between CREATE and PROC(EDURE).
WILLIAM MITCHELL (9/26/2009)
BTW the only reason I threw in the "remove extra spaces" code was that when I tested my code against a very poorly-written database I found the "developers" had used as many as 12 spaces between the CREATE and the PROC. If the developer uses a single space then the point is moot.
Personally, I wish that some of t-sql's syntax was a little bit more restrictive in terms of what could be placed where. The whole 'comments in the middle of identifiers' thing is just asking for trouble IMHO. Witness the fact that SSMS doesn't handle it correctly. 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 26, 2009 at 6:54 pm
I have also been toying with a regex based way to solve this problem - and, using the regex \/\*.*CREATE.*\*\/|--.*CREATE|(CREATE) in case insensitive mode then you could look for the first match in the numbered capture group 1, and replace that with ALTER. I'm not sure what the regex support under vb is like, but that could potentially be a *much* easier route.
*kicks self for not thinking of it before* 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 27, 2009 at 9:07 am
Matt
Here is function using regular expressions to find & replace the first occurrence of CREATE...PROC seems to work in my testing. . .
Function create_to_alter(Text As String) As String
' this requires a reference to
' Microsoft VBScript Regular Expressions 5.5
' VBScript_RegExp_55
' C:\WINDOWS\System32\vbscript.dll\3
' {3F4DACA7-160D-11D2-A8E9-00104B365C9F}
'
Dim RE As RegExp, cMatch As MatchCollection
'
Set RE = New RegExp
RE.Pattern = "CREATE[\s]+PROCE*D*U*R*E*[\s]"
RE.IgnoreCase = True
Set cMatch = RE.Execute(Text)
If cMatch.Count > 0 Then
Text = Replace(Text, Mid(Text, cMatch.Item(0).FirstIndex + 1, cMatch.Item(0).Length), "ALTER PROC ", , 1)
End If
create_to_alter = Text
End Function
so then OP just needs to use this before writing the string to the output file:
sql = create_to_alter(sql)
September 27, 2009 at 10:29 am
Seeing as I don't have VB installed - would you just run it through the proc definition that I tested my solution with?
I.e. run this code
Dim SQL as String
SQL = "/* CREATE PROCEDURE -> ALTER PROCEDURE example */" + vbCrLf + _
"-- CREATE PROCEDURE" + vbCrLf + _
"CREATE /* CREATE PROCEDURE */ PROCEDURE [dbo].[proc_test]" + vbCrLf + _
"AS" + vbCrLf + _
"PRINT 'CREATE PROCEDURE';"
SQL = create_to_alter(SQL)
MsgBox SQL
Sorry if the syntax is a bit off, but it's been a long time since I used VB6...
Edit -> I believe, with that regex, the output will be:
/* ALTER PROC -> ALTER PROCEDURE example */
-- CREATE PROCEDURE
CREATE /* CREATE PROCEDURE */ PROCEDURE [dbo].[proc_test]
AS
PRINT 'CREATE PROCEDURE';
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 27, 2009 at 11:37 am
That of course is a ridiculous example.
Bye-bye
September 27, 2009 at 12:48 pm
WILLIAM MITCHELL (9/27/2009)
That of course is a ridiculous example.
No, it's just a test that covers a few edge cases, and breaks every code sample you've posted.
If you've never seen something like
CREATE
--ALTER
PROCEDURE dbo.procname
Where a dev has been swapping alter / create as they develop their proc, then I'm surprised.
I'm also slightly aggrieved that you set exacting requirements for my code, but posted three separate code examples which all failed to meet those requirements, and, having posted up three incorrect solutions, as well as branding people who use a differenty style to that which your code could cope with as developers in inverted commas, then have such a childish response as 'bye bye'. Poor.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 27, 2009 at 3:12 pm
Matt Whitfield (9/27/2009)
WILLIAM MITCHELL (9/27/2009)
That of course is a ridiculous example.No, it's just a test that covers a few edge cases, and breaks every code sample you've posted.
If you've never seen something like
CREATE
--ALTER
PROCEDURE dbo.procname
...
This is what standards are for, Matt. The simplest solution, by far, for examples like this:
/* CREATE PROCEDURE -> ALTER PROCEDURE example */
-- CREATE PROCEDURE
CREATE /* CREATE PROCEDURE */ PROCEDURE [dbo].[proc_test]
AS
PRINT 'CREATE PROCEDURE';
... is for the DBA Team to reject them as the unprofessional drek that they are. That the DBAs & DB Support development team should have to spend their company's time developing increasingly arcane work-arounds for stuff like this is an extraordinarily bad way for a company to spend it's money.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 27, 2009 at 4:52 pm
Barry
That's a test case. Can you really not imagine a procedure like this, which would also cause the posted code to fail:
/*
* Procedure: stproc_CreateProcessRequestItems
*
* Purpose: Create process request items from work queue
*/
CREATE PROCEDURE dbo.stproc_CreateProcessRequestItems
...
If you can honestly, truly, put your hand on your heart and say that never ever in the world ever would the words 'create proc' appear in a comment before the create procedure, then fair enough. But don't come in and say 'your test case is a stupid example' - because it's not meant to be an example of a realistic test - it's meant to be an example of the worst of the worst - the worst possible case that you can think of. The one that you can say 'you know what, if my code satisfies that, then I know it's robust'.
Personally, I'm happier knowing that I can produce something that won't fail to produce the correct result. That's mostly what databases are about for me. If 'it works mostly' is good enough for the OP, then that's also fine.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 28, 2009 at 10:27 am
Matt Whitfield (9/27/2009)
BarryThat's a test case.
Yep. And I supplied the SOP solution to a case like that. I am not going to spend my time trying to develop tools to handle every strange code mutation that wouldn't pass muster in a production environment. Your example SQL wouldn't, thus my tools should never have to run on your test case, because they wouldn't get past the coding standards. In particular, I know of no SQL coding standard that would permit comments between the DDL statement verb and it's class-type keyword.
Can you really not imagine a procedure like this, which would also cause the posted code to fail:
/*
* Procedure: stproc_CreateProcessRequestItems
*
* Purpose: Create process request items from work queue
*/
CREATE PROCEDURE dbo.stproc_CreateProcessRequestItems
...
That's irrelevant to the specific case Matt, because that was not the question that you asked. This is a different question/case, perhaps legitimate in it's own right, but nonetheless a red herring with respect to our specific foregoing discussion.
If you can honestly, truly, put your hand on your heart and say that never ever in the world ever would the words 'create proc' appear in a comment before the create procedure, then fair enough.
Well, I could say that because I never put comments before my script-based DDL anyway, I always put them within the definition block. But it's irrelevant either way, that wasn't what I was talking about.
But don't come in and say 'your test case is a stupid example'...
I didn't say anything like that, Matt. Please don't try to put words in my mouth, that's not right.
... - because it's not meant to be an example of a realistic test - it's meant to be an example of the worst of the worst - the worst possible case that you can think of. The one that you can say 'you know what, if my code satisfies that, then I know it's robust'.
And that's fine, but I guide my customers to institute what many shops have already been doing for decades: to enforce reasonable standards that eliminate having to support bad code, especially the "worst of the worst". It's just a better way of dealing with these things.
Personally, I'm happier knowing that I can produce something that won't fail to produce the correct result. That's mostly what databases are about for me. If 'it works mostly' is good enough for the OP, then that's also fine.
Which is why it's a good idea to catch bad code before it goes into production.
That said, I suppose that if I were developing a Product, instead of just an in-house tool, then I might share your concern, but even then, it's hard for me to believe that it should take 700-800 lines of code to write a function that can change a DMO or SMO generated CREATE PROC script into a valid ALTER PROC script for a single Stored Procedure object. Assuming that you set the options on the script generation correctly before-hand and that it was compilable SQL, I can't imagine that it should take more than 40 or 50 lines of VB at the most, no matter how strange the SQL code.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 28, 2009 at 10:51 am
RBarryYoung (9/28/2009)
And I supplied the SOP solution to a case like that. I am not going to spend my time trying to develop tools to handle every strange code mutation that wouldn't pass muster in a production environment. Your example SQL wouldn't, thus my tools should never have to run on your test case, because they wouldn't get past the coding standards. In particular, I know of no SQL coding standard that would permit comments between the DDL statement verb and it's class-type keyword.
Well you don't have to spend the time, I already published the code. And coding standards are irrelevent to producing robust code. You can't just say 'the input has to match standard x' for it to be a robust solution. As I've said two times already, doing a replace and hoping it never breaks is a valid option if you understand and are willing to accept the risk.
RBarryYoung (9/28/2009)
That's irrelevant to the specific case Matt, because that was not the question that you asked. This is a different question/case, perhaps legitimate in it's own right, but nonetheless a red herring with respect to our specific foregoing discussion.
I see it as totally relevant. The question I originally asked was 'can you run a test case'. Which you said was 'unprofessional drek' which a DBA should reject. So i produced something that you might not consider to be 'unprofessional drek' to further illustrate the point that doing a string replace is not a robust (edited -> valid to robust) solution here. I don't know of any sql coding standard that would prescribe what you can and cannot put in a comment.
RBarryYoung (9/28/2009)
]Well, I could say that because I never put comments before my script-based DDL anyway, I always put them within the definition block. But it's irrelevant either way, that wasn't what I was talking about.
I'm lost then. What were you talking about? I was talking about changing the text of a CREATE DDL to an ALTER DDL reliably.
RBarryYoung (9/28/2009)
I didn't say anything like that, Matt. Please don't try to put words in my mouth, that's not right.
'unprofessional drek' roughly equates to 'stupid example' in my book, I should have quoted you directly, apologies.
RBarryYoung (9/28/2009)
And that's fine, but I guide my customers to institute what many shops have already been doing for decades: to enforce reasonable standards that eliminate having to support bad code, especially the "worst of the worst". It's just a better way of dealing with these things.
If the 'worst of the worst' was to make it into a DB, yes. But as I said before - can you find anywhere in a coding standard that says 'don't put this / that in a comment'?
RBarryYoung (9/28/2009)
Which is why it's a good idea to catch bad code before it goes into production.
But the thread is about scripting out existing procedures, not about applying standards to those procedures before they were made. And I really do fail to see how the more mundane example is 'bad code'?
RBarryYoung (9/28/2009)
That said, I suppose that if I were developing a Product, instead of just an in-house tool, then I might share your concern, but even then, it's hard for me to believe that it should take 700-800 lines of code to write a function that can change a DMO or SMO generated CREATE PROC script into a valid ALTER PROC script for a single Stored Procedure object. Assuming that you set the options on the script generation correctly before-hand and that it was compilable SQL, I can't imagine that it should take more than 40 or 50 lines of VB at the most, no matter how strange the SQL code.
Well, I thought of a better way - which was using the regular expression with the tagged capture group. That would be shorter code. But i'm not sure it would be 100% reliable. My products require proper tokenisation, and so the code for me to implement the CREATE -> ALTER change took about 20 minutes to write, given that I already had a tokeniser (which didn't take long to adapt from it's source anyway). And I know that there will never be a procedure (or function, trigger or view, for that matter) which the code I posted will fail to deal with.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply