deciacco.com blog

NOTE: This is a first draft, alpha code release.

Whenever you are working with Microsoft's SQL server you inevitably have to write some Transact SQL code.

I'm currently working on a data migration script and I found I'm using similar code over and over for the different tables found in the source database.

I've written a small utility that can help in this task. The code connects to the database, collects table information like the columns and column data types, and writes the script.

Below is a list of the current scripts that it creates. I've provided the code for you as well so that you can simply copy and paste into a new console app. You can also download the Visual Studio 2k5 Solution.

Example Usage:
tablescriptgen.exe /server:(local) /initialcatalog:mydb /tablename:mytable /tablealias:mytbl

List of Parameters:
Required

/server: sever to connect to; uses Windows Authentication
/initialcatalog: database name to use
/tablename: the table to retrieve data for
/tablealias: the tablename used in the sql statement
ex: "Select * from mytable as 'tablealias' "

Optional

/writefilefullname: full path with filename and extension of the file to write the script to; defaults to user's desktop
/overridedbname: the db name to use in the "USE" statement of the written script
/usefromlinkedtablename: create a cursor from the tablename that exists in a linked server

List of supported scripts:

WriteTableIterator()
Creates Transactsql code to iterate through the source table rows with a cursor.

WriteInsertStatement()
Creates the insert into statement

I plan on adding more as as I need them and if you add enhancements please shoot me a copy. (contact #ATT# deciacco #DOTT# com)

/*
 * http://www.deciacco.com/blog/
 *
 * TableScriptGen Program: Generates sql script for a given
 * MS Sql Server table
 *
 * Authors:		Deciacco.com
 * Contributors:
 * Created:		15 June 2007
 * Modified:		15 June 2007
 *
 * Version:		1.0
 * Arguments:		See below in the Arguments class for valid format
 *
 * Notes:			As of modified date above this program works with
 *			both SQL Server 2k and 2k5
 *
 * License:		This program is free and distributed 'AS IS' and without
 *			any warranty. If you make enhacements, please send a copy
 *			to contact #ATT# deciacco #DOTT# com.
 */
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Collections.Specialized;
using System.Text.RegularExpressions;

namespace TableScriptGen
{
	///
	/// Main program
	///
	class Program
	{
		#region Fields
		static string _server = string.Empty;
		static string _initialcatalog = string.Empty;
		static string _tablealias = string.Empty;
		static string _tablename = string.Empty;

		static DataSet _tabledata_ds = null;
		static SqlConnectionStringBuilder _sconnbldr = null;
		static SqlConnection _conx = null;
		static SqlDataAdapter _da = null;

		static string _getinfosql = string.Empty;

		static string _writefilefullname = string.Empty;
		static string _overridedbname = string.Empty;
		static string _usefromlinkedtablename = string.Empty;

		#endregion

		static void Main(string[] args)
		{
			try
			{
				// get the arguments
				Arguments cmargs = new Arguments(args);

				if (cmargs["server"] == null ||
					cmargs["initialcatalog"] == null ||
					cmargs["tablealias"] == null ||
					cmargs["tablename"] == null)
					throw new Exception("Invalid Arguments");

				// set the variables
				Program._server = cmargs["server"];
				Program._initialcatalog = cmargs["initialcatalog"];
				Program._tablealias = cmargs["tablealias"];
				Program._tablename = cmargs["tablename"];
				Program._writefilefullname = Environment.GetFolderPath(
						Environment.SpecialFolder.DesktopDirectory).ToString() +
						@"\" + Program._tablename + ".txt";

				// optional arguments
				if(cmargs["writefilefullname"] != null)
					Program._writefilefullname = cmargs["writefilefullname"];

				if(cmargs["overridedbname"] != null)
					Program._overridedbname = cmargs["overridedbname"];

				if(cmargs["usefromlinkedtablename"] != null)
					Program._usefromlinkedtablename = cmargs["usefromlinkedtablename"];

				// set the sql statement to get table information from the db
				Program._getinfosql = "select table_name, column_name, column_default, data_type, ";
				Program._getinfosql += "character_maximum_length, is_nullable ";
				Program._getinfosql += "from information_schema.columns ";
				Program._getinfosql += "where table_name in ";
				Program._getinfosql += "(select table_name ";
				Program._getinfosql += "from information_schema.tables ";
				Program._getinfosql += "where table_type = 'base table')";
				Program._getinfosql += "and ";
				Program._getinfosql += "table_name = '" + Program._tablename + "'";

				// create the connection string builder object and set its properties
				Program._sconnbldr = new SqlConnectionStringBuilder();
				Program._sconnbldr.DataSource = Program._server;
				Program._sconnbldr.InitialCatalog = Program._initialcatalog;
				Program._sconnbldr.IntegratedSecurity = true;

				// create the connection object
				Program._conx = new SqlConnection(Program._sconnbldr.ToString());

				// open the connection and get the data
				Program._conx.Open();
				Program._da = new SqlDataAdapter(Program._getinfosql, Program._conx);

				Program._tabledata_ds = new DataSet(Program._tablealias);

				Program._da.Fill(Program._tabledata_ds, Program._tablealias);

				// process table data
				// this is a good place to add other functions to do other
				// scripts
				Program.WriteTableIterator(Program._writefilefullname, false,
					Program._overridedbname, Program._usefromlinkedtablename);

				Program.WriteInsertStatement(Program._writefilefullname, true);

			}
			catch (Exception ex)
			{
				Console.WriteLine(ex.Message);
			}
			finally
			{
				// if the connection object has been set and the error
				// occured after it was open, lets close the connection
				if(Program._conx != null)
					if (Program._conx.State == ConnectionState.Open)
					{
						Program._conx.Close();
						Program._conx.Dispose();
					}
			}
		}

		static void WriteTableIterator(string p_writefilefullname, bool p_appendtofile,
				string p_overridedbname, string p_usefromlinkedtablename)
		{
			TextWriter tw = new StreamWriter(p_writefilefullname, p_appendtofile);
			DataRowCollection drw = Program._tabledata_ds.Tables[Program._tablealias].Rows;

			string dataTypes = "varchar";
			int cnt;

			if (p_overridedbname != string.Empty)
				tw.WriteLine("use " + p_overridedbname + Environment.NewLine + "go" + Environment.NewLine);
			else
				tw.WriteLine("use " + Program._initialcatalog + Environment.NewLine + "go" + Environment.NewLine);

			tw.Write("declare ");
			for (cnt = 0; cnt < drw.Count; cnt++)
			{
				DataRow dr = drw[cnt];

				if (dr["data_type"].ToString() == "text") //Can't use text for local variable
				{
					dr["data_type"] = "varchar";
					dr["character_maximum_length"] = 1000;
				}

				tw.Write("@" + Program._tablealias + dr["column_name"] + " " + dr["data_type"]);

				if (dataTypes.IndexOf(dr["data_type"].ToString()) >= 0)
					tw.Write("(" + dr["character_maximum_length"] + ")");

				if (cnt != drw.Count - 1)
					tw.Write(",");
				else
					tw.Write(Environment.NewLine);

				tw.Write(Environment.NewLine);
			}

			tw.WriteLine("declare " + Program._tablealias +
				"_cursor cursor forward_only read_only" + Environment.NewLine + "for");

			tw.Write("select ");
			for (cnt = 0; cnt < drw.Count; cnt++)
			{
				DataRow dr = drw[cnt];

				tw.Write(Program._tablealias + "." + dr["column_name"] + " as _" + Program._tablealias + dr["column_name"]);

				if (cnt != drw.Count - 1)
					tw.Write(", ");

				if ((cnt + 1) % 4 == 0)
					tw.Write(Environment.NewLine);
			}

			if (p_usefromlinkedtablename != string.Empty)
				tw.WriteLine(Environment.NewLine + "from " + p_usefromlinkedtablename + " as " + Program._tablealias);
			else
				tw.WriteLine(Environment.NewLine + "from " + Program._tablename + " as " + Program._tablealias);

			tw.Write(Environment.NewLine + "open " + Program._tablealias + "_cursor " + Environment.NewLine +
					Environment.NewLine + "fetch next from " + Program._tablealias + "_cursor " + Environment.NewLine +
					"into ");

			for (cnt = 0; cnt < drw.Count; cnt++)
			{
				DataRow dr = drw[cnt];

				tw.Write("@" + Program._tablealias + dr["column_name"]);

				if (cnt != drw.Count - 1)
					tw.Write(",");
				else
					tw.Write(Environment.NewLine);

				tw.Write(Environment.NewLine);
			}

			tw.WriteLine("while @@fetch_status = 0" + Environment.NewLine + "begin" + Environment.NewLine);
			tw.WriteLine(Environment.NewLine + "--start your code here" +
				Environment.NewLine + Environment.NewLine);

			tw.WriteLine("fetch next from " + Program._tablealias + "_cursor");
			tw.Write("into ");

			for (cnt = 0; cnt < drw.Count; cnt++)
			{
				DataRow dr = drw[cnt];

				tw.Write("@" + Program._tablealias + dr["column_name"]);

				if (cnt != drw.Count - 1)
					tw.Write(",");
				else
					tw.Write(Environment.NewLine);

				tw.Write(Environment.NewLine);
			}

			tw.WriteLine("end");

			tw.WriteLine("close " + Program._tablealias + "_cursor");
			tw.WriteLine("deallocate " + Program._tablealias + "_cursor");
			tw.Write("go");
			tw.WriteLine();
			tw.WriteLine();

			tw.Close();
			tw.Dispose();
		}

		static void WriteInsertStatement(string p_writefilefullname, bool p_appendtofile)
		{
			int cnt;

			TextWriter tw = new StreamWriter(p_writefilefullname, p_appendtofile);
			DataRowCollection drw = Program._tabledata_ds.Tables[Program._tablealias].Rows;

			tw.Write("insert into " + Program._tablename + " (");

			for (cnt = 0; cnt < drw.Count; cnt++)
			{
				DataRow dr = drw[cnt];

				tw.Write(dr["column_name"]);

				if (cnt != drw.Count - 1)
					tw.Write(", ");

				if ((cnt + 1) % 4 == 0)
					tw.Write(Environment.NewLine);
			}

			tw.WriteLine(")");

			tw.WriteLine("values (");
			for (cnt = 0; cnt < drw.Count; cnt++)
			{
				DataRow dr = drw[cnt];

				tw.Write("@" + Program._tablealias + dr["column_name"]);

				if (cnt != drw.Count - 1)
					tw.Write(",");

				if ((cnt + 1) % 4 == 0)
					tw.Write(Environment.NewLine);
			}

			tw.WriteLine(")");

			tw.WriteLine();
			tw.WriteLine();

			tw.Close();
			tw.Dispose();
		}
	}

/*
 * http://www.codeproject.com/csharp/command_line.asp
 * See below for valid parameter forms
 *
 * Arguments class: application arguments interpreter
 *
 * Authors:			R. LOPES
 * Contributors:	R. LOPES
 * Created:			25 October 2002
 * Modified:		28 October 2002
 *
 * Version:			1.0
 *
 */
    ///
    /// Arguments class
    ///
    public class Arguments{
        // Variables
        private StringDictionary Parameters;

        // Constructor
        public Arguments(string[] Args)
        {
            Parameters = new StringDictionary();
            Regex Spliter = new Regex(@"^-{1,2}|^/|=|:",
                RegexOptions.IgnoreCase|RegexOptions.Compiled);

            Regex Remover = new Regex(@"^['""]?(.*?)['""]?$",
                RegexOptions.IgnoreCase|RegexOptions.Compiled);

            string Parameter = null;
            string[] Parts;

            // Valid parameters forms:
            // {-,/,--}param{ ,=,:}((",')value(",'))
            // Examples:
            // -param1 value1 --param2 /param3:"Test-:-work"
            //   /param4=happy -param5 '--=nice=--'
            foreach(string Txt in Args)
            {
                // Look for new parameters (-,/ or --) and a
                // possible enclosed value (=,:)
                Parts = Spliter.Split(Txt,3);

                switch(Parts.Length){
                // Found a value (for the last parameter
                // found (space separator))
                case 1:
                    if(Parameter != null)
                    {
                        if(!Parameters.ContainsKey(Parameter))
                        {
                            Parts[0] =
                                Remover.Replace(Parts[0], "$1");

                            Parameters.Add(Parameter, Parts[0]);
                        }
                        Parameter=null;
                    }
                    // else Error: no parameter waiting for a value (skipped)
                    break;

                // Found just a parameter
                case 2:
                    // The last parameter is still waiting.
                    // With no value, set it to true.
                    if(Parameter!=null)
                    {
                        if(!Parameters.ContainsKey(Parameter))
                            Parameters.Add(Parameter, "true");
                    }
                    Parameter=Parts[1];
                    break;

                // Parameter with enclosed value
                case 3:
                    // The last parameter is still waiting.
                    // With no value, set it to true.
                    if(Parameter != null)
                    {
                        if(!Parameters.ContainsKey(Parameter))
                            Parameters.Add(Parameter, "true");
                    }

                    Parameter = Parts[1];

                    // Remove possible enclosing characters (",')
                    if(!Parameters.ContainsKey(Parameter))
                    {
                        Parts[2] = Remover.Replace(Parts[2], "$1");
                        Parameters.Add(Parameter, Parts[2]);
                    }

                    Parameter=null;
                    break;
                }
            }
            // In case a parameter is still waiting
            if(Parameter != null)
            {
                if(!Parameters.ContainsKey(Parameter))
                    Parameters.Add(Parameter, "true");
            }
        }

        // Retrieve a parameter value if it exists
        // (overriding C# indexer property)
        public string this [string Param]
        {
            get
            {
                return(Parameters[Param]);
            }
        }
    }
}

Click here to download the C# 2 Source (Visual Studio 2k5) (45.2k)