I downloaded some code a recently that creates an Oracle Database, but I don’t have Oracle, so I converted most of the code to SQL Server.  One of the harder things to convert was the column and table comments.  One of the features of Oracle that Microsoft SQL Server does not have directly is commenting. It appears that you can have comments on a table and comments on a column (field) in the table.

Well after doing some research into how to convert these to SQL Server,  I came across an article at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37796 that told how to do this.  Well, I had a script with over 1,100 comments, so I used LinqPad to create a small program that would convert my script using Regular Expressions.

Here is the code for that, please let me know if this helps anyone.  It sure made my work a lot easier.

This is for Table Comments:

void Main()
{
		List<string> lines = System.IO.File.ReadAllLines(@"C:\Users\username\Downloads\TableComments.SQL").ToList();

		// Display the file contents by using a foreach loop.
		Regex ex = new Regex(@"^comment on table (\w+) IS (\'.+\');", RegexOptions.IgnoreCase);
		StringBuilder sb = new StringBuilder();

		string repl = @"EXEC sp_addextendedproperty @name = N'MS_Description', @value = $2, @level0type = N'USER', @level0name = 'dbo',@level1type = N'Table', @level1name = '$1';";
		string newline = string.Empty;
		foreach (string line in lines)
		{
			if (ex.IsMatch(line))
			{
				newline = ex.Replace(line,repl);
				sb.AppendLine(newline);
				Console.WriteLine(newline);
			}
			else
			{
				// If there is not a match, output it anyway, might be comments in the code.
				sb.AppendLine(line);
				Console.WriteLine(line);
			}
		}

		// save the converted file.
		using (StreamWriter outfile = new StreamWriter(@"C:\Users\username\Downloads\TableCommentsConverted.SQL"))
		{
			outfile.Write(sb.ToString());
		}

}

And here is the code for Column comments.

void Main()
{
		string[] lines = System.IO.File.ReadAllLines(@"C:\Users\username\Downloads\ColumnComments.SQL");

		Regex ex = new Regex(@"^comment on column (\w+)\.(\w+) IS (\'.+\');", RegexOptions.IgnoreCase);
		StringBuilder sb = new StringBuilder();
		string repl = @"EXEC sp_addextendedproperty @name = N'$1.$2', @value = $3, @level0type = N'USER', @level0name = 'dbo',@level1type = N'Table', @level1name = '$1',  @level2type = N'Column', @level2name = '$2';";
		string newline = string.Empty;
		foreach (string line in lines)
		{

			if (ex.IsMatch(line))
			{
				newline = ex.Replace(line,repl);
				sb.AppendLine(newline);
				Console.WriteLine(newline);
			}
			else
			{
				// if there is not a match, output the line anyway, for spaces and comments in the file.
				sb.AppendLine(line);
				Console.WriteLine(line);
			}
		}

		// save the converted file.
		using (StreamWriter outfile = new StreamWriter(@"C:\Users\username\Downloads\ColumnCommentsConverted.SQL"))
		{
			outfile.Write(sb.ToString());
		}

}

// Define other methods and classes here