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)
