Introduction
Creating, generating and altering stored procedures are common tasks when we are working with database applications. These tasks won't take up our time if our database has a small number of stored procedures.
But for databases with thousands of stored procedures, it takes up our time to identify the stored procedure name in the list and then do some clicks to generate its script. To prevent these boring tasks, I spent a couple of hours to create a very simple tool for quick searching and regenerating stored procedure scripts using SQL-DMO Object Library.
Using the Code
The code is very simple and this sample is not a perfect one, but it helps my work. ;)
using System;
using System.Collections;
using SQLDMO;
using System.Text;
namespace SQLScriptGen
{
public class SQLServerUtil
{
private SQLServer myServer;
private bool isConnected = false;
public SQLServerUtil()
{
myServer = new SQLServer();
}
public delegate void delegateSendText (string info);
public event delegateSendText OnInfo;
public event delegateSendText OnSearchFound;
public ArrayList GetServers()
{
ArrayList ret = new ArrayList();
try
{
NameList nl = myServer.Application.ListAvailableSQLServers();
int i, n = nl.Count;
for (i = 0; i< n; i++)
{
string sn = nl.Item(i);
if (sn != null)
ret.Add(sn);
}
}
catch (System.Exception ex)
{
if (OnInfo != null)
OnInfo(ex.Message);
}
return ret;
}
public bool Connect(string serverName)
{
Disconnect();
try
{
myServer.LoginTimeout = 15;
myServer.LoginSecure = true;
myServer.Connect(serverName,null , null);
}
catch (Exception ex)
{
isConnected = false;
if (OnInfo != null)
OnInfo(ex.Message);
}
isConnected = true;
return isConnected;
}
private void Disconnect()
{
try
{
myServer.DisConnect();
}
catch (Exception ex)
{
if (OnInfo != null)
OnInfo(ex.Message);
}
}
public bool Connect(string serverName, string userName, string password)
{
Disconnect();
try
{
myServer.LoginSecure = false;
myServer.Connect(serverName, userName, password);
}
catch (Exception ex)
{
isConnected = false;
if (OnInfo != null)
OnInfo(ex.Message);
}
isConnected = true;
return isConnected;
}
public ArrayList GetDatabaseNames()
{
ArrayList ret = new ArrayList();
try
{
foreach (_Database db in myServer.Databases)
{
if (db != null)
ret.Add(db.Name);
}
}
catch (System.Exception ex)
{
if (OnInfo != null)
OnInfo(ex.Message);
}
return ret;
}
public string GetSPScript(string dbName, string spName, bool createFile)
{
string ret = "";
try
{
string fileName = "output\\" + spName + ".sql";
if (!createFile)
fileName = null;
ret = myServer.Databases.Item
(dbName, "dbo").StoredProcedures.Item(spName, "dbo")
.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops
| SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_IncludeHeaders
| SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default
, fileName,
SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default );
}
catch (Exception ex)
{
if (OnInfo != null)
OnInfo(ex.Message);
}
return ret;
}
public void SearchSP(string dbName, string searchText, bool inNameOnly)
{
try
{
StoredProcedures colSP = myServer.Databases.Item
(dbName, "dbo").StoredProcedures;
if (inNameOnly)
{
foreach (_StoredProcedure sp in colSP)
{
if (sp.Name.IndexOf(searchText) >=0)
{
if (OnSearchFound != null)
{
OnSearchFound(sp.Name);
System.Windows.Forms.Application.DoEvents();
}
}
}
}
else
{
foreach (_StoredProcedure sp in colSP)
{
if (sp.Text.IndexOf(searchText) >=0)
{
if (OnSearchFound != null)
{
OnSearchFound(sp.Name);
}
}
}
}
}
catch (System.Exception ex)
{
if(OnInfo != null)
{
OnInfo(ex.Message);
}
}
}
}
}
Have fun with this!