energy in code ...

not so active anymore, yet still stuff and code that works in Perl, bash, sh, PLSQL, TSQL, NZPLSQL , XML, HTML, XSL, C#, Scala , Python, Teradata SQL, cmd batch scripts, Oracle , MySql some old Java stuff , Linux , DB2 , Sun Solaris , NSIS , VB 6.0 , some old Essbase VB API and plain English - all this in non-user friendly fashion combined with occasionally strong statements and opinions about IT best practices, Agile Development , energy ... dump end ...Wait - https://qto.fi

2009-06-30

Restore db from file on sql server 2005 / 2008

RESTORE DATABASE [GenApp] FROM

DISK = N'D:\GenApp\DATA\Backups\NameOfTheBackedUpFile.bak'

WITH  FILE = 1,  MOVE N'ocms_dev' TO N'D:\GenApp\data\DbFiles\GenApp.mdf',

MOVE N'ocms_dev_log' TO N'D:\GenApp\data\DbFiles\GenApp_log.ldf',

KEEP_REPLICATION,  NOUNLOAD,  REPLACE,  STATS = 10

GO

backup sql server 2005 / 2008 databases

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR

select

DATABASE_NAME   = db_name(s_mf.database_id)

from

sys.master_files s_mf

where

-- ONLINE

s_mf.state = 0

-- Only look at databases to which we have access

and has_dbaccess(db_name(s_mf.database_id)) = 1

-- Not master, tempdb or model

and db_name(s_mf.database_id) not in ('Master','tempdb','model') and

-- UNCOMMENT THIS ONE TO BACKUP ONLY THE ocms_dev db_name(s_mf.database_id) in ('ocms_dev' )

-- ALTERNATIVELY SPECIFY ONLY ONE DB , uncomment this line

--  and db_name(s_mf.database_id) in ('ocms_dev')

group by s_mf.database_id

order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

declare @DBFileName varchar(256)

set @DBFileName = @DbName + '_' + replace(convert(varchar, getdate(), 112), '-', '.') + '.bak'

--REMEMBER TO PUT HERE THE TRAILING \ FOR THE DIRECTORY !!!

declare @sqlToRun varchar(4000)

set @sqlToRun = 'BACKUP DATABASE [' + @DBName + '] TO  DISK = N''D:\GenApp\DATA\Backups\' +

@DBFileName + ''' WITH NOFORMAT, INIT,  NAME = N''' +

@DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 100'

-- debug print @sqlToRun

-- debug return

exec (@sqlToRun )

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

END

CLOSE DATABASES_CURSOR

DEALLOCATE DATABASES_CURSOR

2009-06-29

Effective debugging in asp.net

Effective debugging is a key part of effective software development. If you do not realize this statement or even do not appreciate this axiom in software development this article would not benefit you much. If you are still reading this article aims to provide a simple but powerfull aproach ( not ready solution ) for debugging contents of objects in Asp.Net. Actually you could use this approach for any other language or .Net area by simply using another type of control/debugging logic.

Background

Often in the code you have an object having contents you would like to see at a specific run time or when the page is loaded. Often you do think that some data structures holded specific values yet your program behaves in a way telling you that your assumptions were wrong, but you could not see it because you did not have a proper mean for seeing what was in those data structures.

The approach

The approach consists in using a simple control which renders the contents of a string , which happens to be nicely html formatted string , produced by dedicated debugger classes, which contain the logic of converting the contents of custom objects ( DataSets , DataReaders , YourFacyCoolTypes ) to a simple html user-friendly rendering string.

The code

The code consists of only one simple control you would simply copy paste to your App_Code folder ( and probably rename the namespaces ; ). Here it is:

//For docs read the comments for leagal disclaimer see the bottom of this file
using System;
using System.Web;
 
namespace GenApp.Gui.Controls
{
/// 
/// A simple control to present debug data. 
/// Note it inherits the Control class 
/// 
public class DebugDumper : System.Web.UI.Control
{
 
#region Text
 
/// the debugging text to present
private string _Text;
public string Text
{
get
{
//use ViewState if you want also
return System.Convert.ToString(
HttpContext.Current.Session["global." + 
this.ID + "Text"]);
 
} //eof get
 
set
{
_Text = value;
HttpContext.Current.Session["global." + 
this.ID + "Text"] = value;
}
} //eof prop
#endregion Text
 
 
public DebugDumper(string msg, string debugString)
{
this.Text = " " + msg + "  " + debugString;
}
 
 
/// <summary>
/// Renders the contents of the control 
/// by using the debug string passed on creation
/// </summary>
/// <param name="writer">The <see cref="HtmlTextWriter"/> to write to.</param>
 
protected override void Render(System.Web.UI.HtmlTextWriter writer)
{
//todo: add here logging according to log level
writer.Write("<div><p>");
string echo = this.Text ?? " null debug string passed " ;
writer.Write( echo ); 
writer.Write("</p></div>");
base.Render(writer);
 
} //eof method 
 
 
} //eof class 
} //eof namespace 
 
//Leagal disclaimer:
//You could do anything you want with this code. 
//Everything is on your own responsibility. 
//All trademarks belong to their respective owners.

//Leagal disclaimer: //You could do anything you want with this code. //Everything is on your own responsibility. //All trademarks belong to their respective owners.

Example HtmlDebugger class

Now the key point here is to realize that I am using here my HtmlDebugger class which "knows" how to debug DataSet objects , IDataReader objects and a custom list of my application which contains a custom Msg objects just for demonstration purposes. ( delete the DebugMsgList, while copy pasting since you will not know what is the Msg class of my app). Most probably you do have your own debug classes which take a specific objects as parameters and do return strings showing their content. I good example would be the Object Dumper by Piero Viano. Most probably your debugging classes do debug more nicely the objects in your app, but for the purpose of this article I would have to show my HtmlDebugger class :

using System;

using System.Text.RegularExpressions;

using System.Data;

using System.Collections.Specialized;

using System.Text;

namespace GenApp.Utils.Log

/// <summary>

///Debugs passed objects and returns ready formatted html with the objects values

/// </summary>

public class HtmlDebugger

public static string DebugDataSet(string msg, DataSet ds)

StringBuilder sb = new StringBuilder();

sb.Append("<p> START " + msg + "</p>");

if (ds == null)

return msg + " null ds passed ";

if (ds.Tables == null || ds.Tables.Count == 0)

return msg + " no tables in ds ";

sb.Append("<p> DEBUG START --- " + msg + "</p>");

foreach (System.Data.DataTable dt in ds.Tables)

sb.Append("================= My TableName is " +

dt.TableName + " ========================= START");

sb.Append("<table>\n");

int colNumberInRow = 0;

foreach (System.Data.DataColumn dc in dt.Columns)

sb.Append(" <th> ");

sb.Append(" |" + colNumberInRow + "| ");

sb.Append(dc.ColumnName + " </th> ");

colNumberInRow++;

} //eof foreach (DataColumn dc in dt.Columns)

int rowNum = 0;

foreach (System.Data.DataRow dr in dt.Rows)

string strBackGround = String.Empty;

if (rowNum% 2 == 0)

strBackGround = " bgcolor=\"#D2D2D2\" ";

sb.Append("\n " + rowNum + "<tr " + strBackGround + " >");

int colNumber = 0;

foreach (System.Data.DataColumn dc in dt.Columns)

sb.Append("<td> |" + colNumber + "| ");

sb.Append(dr[dc].ToString() + " </td>");

colNumber++;

} //eof foreach (DataColumn dc in dt.Columns)

rowNum++;

sb.Append("</tr>");

} //eof foreach (DataRow dr in dt.Rows)

sb.Append(" \n");

sb.Append("</table>");

} //eof foreach (DataTable dt in sb.Append.Tables)

sb.Append("<p> DEBUG END--- " + msg + "</p>");

return sb.ToString();

}//eof method

public static string DebugMsgList(string msg, System.Collections.Generic.List<GenApp.Dh.Msg> listMsgs)

System.Text.StringBuilder echo = new System.Text.StringBuilder();

if (listMsgs == null)

return "null listMsgs passed for debugging ";

if (listMsgs.Count == 0)

return "listMsgs.Count == 0";

echo.Append("<table>");

for (int msgCounter = 0; msgCounter < listMsgs.Count; msgCounter++)

GenApp.Dh.Msg objMsg = listMsgs[msgCounter];

string strBackGround = String.Empty;

if (msgCounter % 2 == 0)

strBackGround = " bgcolor=\"#D2D2D2\" ";

echo.Append("<tr" + strBackGround + ">");

echo.Append("<td>msg.MsgKey</td> <td> " + objMsg.MsgKey + "</td>");

echo.Append("<td>msg.MsgId</td><td>" + objMsg.MsgId + "</td>");

echo.Append("</tr>");

} //eof foreach

echo.Append("</table>");

return echo.ToString();

} //eof method

public static string DebugIDataReader(string msg, IDataReader rdr)

StringBuilder sb = new StringBuilder();

if (rdr == null)

return " <p> IDataReader rds is null </p>";

sb.Append("DEBUG START ---" + msg);

sb.Append("<table>");

int counter = 0;

while (rdr.Read() )

string strBackGround = String.Empty;

if (counter % 2 == 0)

strBackGround = " bgcolor=\"#3EBDE8\" ";

sb.Append("<tr" + strBackGround + ">");

for (int i = 0; i < rdr.FieldCount; i++)

sb.Append("<td>");

sb.Append(rdr[i].ToString() + " ");

sb.Append("<td>");

} //eof for

sb.Append("</br>");

sb.Append("</tr>");

counter++;

sb.Append("<table>");

sb.Append("DEBUG END ---" + msg);

return sb.ToString();

} //eof method

public static string DumpListDictionary(string msg , ListDictionary list)

if (list == null)

return "<p> null list passed </p>";

if (list.Count == 0)

return "<p> list.Count = 0 </p> ";

System.Text.StringBuilder sb = new System.Text.StringBuilder();

sb.Append("<p> START DUMP " + msg + " </p>");

sb.Append("<table>");

int counter = 0;

foreach (object key in list.Keys)

string strBackGround = String.Empty;

if (counter % 2 == 0)

strBackGround = " bgcolor=\"#D2D2D2\" ";

sb.Append("<tr" + strBackGround + "><td> key -   </td><td> " + key.ToString());

sb.Append("</td><td>===</td><td>value - </td><td> " + list[key] +  "</td></br></tr>");

counter++;

} //eof foreach

sb.Append("</table>");

sb.Append("<p> END DUMP " + msg + " </p>");

return sb.ToString();

} //eof method

} //eof class

} //eof namespace

where panTextHolder is the Panel I have already populated in the page of this code behind.
dsForUserDetails is my DataSet I want to debug during the call.

Points of Interest

It would be nice to implement a control, which sits on the top of the site master and has the true , false radio button to see debugging info with the according event handler so that a user entitled to see debugging info would be able to switch between regular and debugging view. I wander also if OnDataBind would be better place to implement the adding of the text to the control ... If you you have some thoughts comment bellow ... It would be nice to implement a custom log4net appender based on this approach. It would be even nicer that this would be triggered by dynamic logging settings - e.g. when the app is in production and something is really wrong the admin would empersonate the person having the problems and enable dynamically the debugging and provide the software developer maintaining the app the valuable info. If you have implemented something like this I would be glad to here what was your approach for solving the issue.

2009-06-26

how-to populate a table's column with values from the same table's another column using set based approach

-- UNDERSTANDIGN SET BASED APPROACH

--set nocount off;

--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Products'

--Declare the Table variable

DECLARE @tmpTable TABLE

Number INT IDENTITY(1,1), --Auto incrementing Identity column

CultureNameU VARCHAR(300) --The string value

--Decalre a variable to remember the position of the current delimiter

DECLARE @CurrentDelimiterPositionVar INT

--Decalre a variable to remember the number of rows in the table

DECLARE @Count INT

--Populate the TABLE variable using some logic

INSERT INTO @tmpTable SELECT CultureName FROM CultureInfo

--Initialize the looper variable

SET @CurrentDelimiterPositionVar = 1

--Determine the number of rows in the Table

SELECT @Count=max(Number) from @tmpTable

--A variable to hold the currently selected value from the table

DECLARE @CurrentValue varchar(300);

--Loop through until all row processing is done

WHILE @CurrentDelimiterPositionVar <= @Count

BEGIN

--Load current value from the Table

SELECT @CurrentValue = CultureNameU FROM @tmpTable WHERE Number = @CurrentDelimiterPositionVar

--Process the current value

declare @replaced varchar(300)

set @replaced = replace(@CurrentValue , '-' , '_')

update CultureInfo set CultureNameU=@replaced where CultureName=@CurrentValue

print @replaced

--Increment loop counter

SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;

END

2009-06-24

how-to generate class with simple static public fields out of sql server 2008 database with C#

using TriAxis.RunSharp

public void GenerateMsgs()

#region Init

string strAssemblyName = "GenApp.Generated";

string strAssemblyDirPath = @"D:\GenApp\ga_dev\ga_dev\Bin\"; //refers to the GenApp's bin folder

string strDbHost = "hostName";        //the hostName of the db

string strDbName = "dbName"; //the name of the db

string strLoginName = "dbLoginName";

string strLoginPass = "dbLoginPass";

string strTableName = "Lang_en_US_Msg";            //the table where to get the vals

string strConnectionStringTemplate = "Data Source={0:G};Initial Catalog={1:G};Persist Security Info=False;User ID={2:G};Password={3:G};MultipleActiveResultSets=True";

string connectionString = String.Format(strConnectionStringTemplate, strDbHost, strDbName,

strLoginName, strLoginPass);

#endregion Init

#region InitializeTheAssemblyGeneratingEnvironment

// Get the current application domain for the current thread

AppDomain objAppDomainCurrent = AppDomain.CurrentDomain;

// Create a dynamic assembly in the current application domain,

// and allow it to be executed and saved to disk.

AssemblyName objAssemblyName = new AssemblyName(strAssemblyName);

//Create the assembly generator

AssemblyGen objAssemblyGen = new AssemblyGen

(objAppDomainCurrent, objAssemblyName, AssemblyBuilderAccess.RunAndSave,

strAssemblyDirPath + strAssemblyName + ".dll", true);

objAssemblyGen.Public.Namespace("GenApp.Generated");

#endregion InitializeTheAssemblyGeneratingEnvironment

#region GetTheDataFromTheDatabase

DataTable objDataTableForEnum = new DataTable(strTableName);

#region FillUpTheDataTable

using (SqlConnection objSqlConnection = new SqlConnection(connectionString))

SqlCommand objSqlCommand = objSqlConnection.CreateCommand();

objSqlCommand.CommandText = string.Format("SELECT [MsgId],[MsgKey] FROM [ocms_dev].[dbo].[Lang_en_US_Msg]");

//Console.WriteLine("command.CommandText is " + objSqlCommand.CommandText);

objSqlConnection.Open();

objDataTableForEnum.Load(objSqlCommand.ExecuteReader(CommandBehavior.CloseConnection));

} //eof using

#endregion FillUpTheDataTable

#region GenerateTheClass

TypeGen objClassTypeGen = objAssemblyGen.Public.Class("ConstMsg", typeof(object));

CodeGen g = objClassTypeGen.Public.Constructor();

foreach (DataRow dr in objDataTableForEnum.Rows)

string msgKey = dr[1].ToString();

FieldGen objXvalFieldGen =

objClassTypeGen.Private.Field(typeof(string), msgKey, msgKey);

PropertyGen objPropertyGenForXVal =

objClassTypeGen.Public.SimpleProperty(objXvalFieldGen, msgKey.ToUpper());

g.Assign(objPropertyGenForXVal, msgKey);

} //eof foreach DataRow

} //eof CodeGen

}//eof TypeGen

#endregion    GenerateTheClass

#endregion GetTheDataFromTheDatabase

// Finally, save the assembly

objAssemblyGen.Save();

Console.WriteLine("\n HIT A KEY TO EXIT ");

Console.ReadLine();

} //eof method

2009-06-23

Cool programs for Windows, most of which free ( revised )

Browsing

Opera - simply the best browser on the planet

firefox the most extensible browser

Safari - Apple like quality for Windows

Google Chrome - fast , reliable and stable browser from Google

izarc - the best zipper

Process Explorer - shows resources usage per running process on Windows

Google Earth

Picasa - the best free picture viewer , organizer

Mozilla Thunderbird

Foxit Reader - much faster pdf reader

Textpad - the best text editor for Windows

smstoolpack - create CRUD for sql server

GreenShot

Open diff - tool for comparing sql server 2005 databases

Skype - the best client for Internet and Video Telephony

NSIS - the best open source Windows installer maker

And the time plugin

Gears -- edit online documents offline

Free youtube to ipod converter -- get mp4 files easily to your phone

Defragler - Disk Defragment utility

DvdDecrypter - the best DVD free ripper

ImgBurner - burn ISO images - freeware

ArsClip remembers your clibboard stuff - Hint , Ctrl + Shift + Z , 2

colorpic - Color to hex utility

Expresso - a GUI tool for building and verifying Regular Expressions

a flv file player
Python - for cool scripts and programming

STARUML - the best open source UML editor

Infrarecorder - free CD / DVD burner

Paint.Net - freeware image editor

putty

winscp

Multiline Search and Replace utility program

Google Gears - use Gmail offline

HijackThis

tortoise svn - a stable gui and command line to subversion for Windows

slik svn - the command line tools for svn for windows

Perl for Windows

Data Recovery - recover Shift + del removed programs

Windows Sysinternal utilities

globalization table in sql server 2008 - insert statement

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('af-ZA' , 'Afrikaans - South Africa' , '0x0436' , 'AFK' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('sq-AL' , 'Albanian - Albania' , '0x041C' , 'SQI' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-DZ' , 'Arabic - Algeria' , '0x1401' , 'ARG' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-BH' , 'Arabic - Bahrain' , '0x3C01' , 'ARH' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-EG' , 'Arabic - Egypt' , '0x0C01' , 'ARE' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-IQ' , 'Arabic - Iraq' , '0x0801' , 'ARI' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-JO' , 'Arabic - Jordan' , '0x2C01' , 'ARJ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-KW' , 'Arabic - Kuwait' , '0x3401' , 'ARK' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-LB' , 'Arabic - Lebanon' , '0x3001' , 'ARB' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-LY' , 'Arabic - Libya' , '0x1001' , 'ARL' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-MA' , 'Arabic - Morocco' , '0x1801' , 'ARM' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-OM' , 'Arabic - Oman' , '0x2001' , 'ARO' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-QA' , 'Arabic - Qatar' , '0x4001' , 'ARQ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-SA' , 'Arabic - Saudi Arabia' , '0x0401' , 'ARA' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-SY' , 'Arabic - Syria' , '0x2801' , 'ARS' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-TN' , 'Arabic - Tunisia' , '0x1C01' , 'ART' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-AE' , 'Arabic - United Arab Emirates' , '0x3801' , 'ARU' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ar-YE' , 'Arabic - Yemen' , '0x2401' , 'ARY' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('hy-AM' , 'Armenian - Armenia' , '0x042B' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('Cy-az-AZ' , 'Azeri (Cyrillic) - Azerbaijan' , '0x082C' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('Lt-az-AZ' , 'Azeri (Latin) - Azerbaijan' , '0x042C' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('eu-ES' , 'Basque - Basque' , '0x042D' , 'EUQ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('be-BY' , 'Belarusian - Belarus' , '0x0423' , 'BEL' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('bg-BG' , 'Bulgarian - Bulgaria' , '0x0402' , 'BGR' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ca-ES' , 'Catalan - Catalan' , '0x0403' , 'CAT' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('zh-CN' , 'Chinese - China' , '0x0804' , 'CHS' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('zh-HK' , 'Chinese - Hong Kong SAR' , '0x0C04' , 'ZHH' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('zh-MO' , 'Chinese - Macau SAR' , '0x1404' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('zh-SG' , 'Chinese - Singapore' , '0x1004' , 'ZHI' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('zh-TW' , 'Chinese - Taiwan' , '0x0404' , 'CHT' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('zh-CHS' , 'Chinese (Simplified)' , '0x0004' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('zh-CHT' , 'Chinese (Traditional)' , '0x7C04' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('hr-HR' , 'Croatian - Croatia' , '0x041A' , 'HRV' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('cs-CZ' , 'Czech - Czech Republic' , '0x0405' , 'CSY' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('da-DK' , 'Danish - Denmark' , '0x0406' , 'DAN' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('div-MV' , 'Dhivehi - Maldives' , '0x0465' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('nl-BE' , 'Dutch - Belgium' , '0x0813' , 'NLB' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('nl-NL' , 'Dutch - The Netherlands' , '0x0413' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('en-AU' , 'English - Australia' , '0x0C09' , 'ENA' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('en-BZ' , 'English - Belize' , '0x2809' , 'ENL' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('en-CA' , 'English - Canada' , '0x1009' , 'ENC' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('en-CB' , 'English - Caribbean' , '0x2409' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('en-IE' , 'English - Ireland' , '0x1809' , 'ENI' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('en-JM' , 'English - Jamaica' , '0x2009' , 'ENJ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('en-NZ' , 'English - New Zealand' , '0x1409' , 'ENZ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('en-PH' , 'English - Philippines' , '0x3409' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('en-ZA' , 'English - South Africa' , '0x1C09' , 'ENS' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('en-TT' , 'English - Trinidad and Tobago' , '0x2C09' , 'ENT' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('en-GB' , 'English - United Kingdom' , '0x0809' , 'ENG' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('en-US' , 'English - United States' , '0x0409' , 'ENU' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('en-ZW' , 'English - Zimbabwe' , '0x3009' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('et-EE' , 'Estonian - Estonia' , '0x0425' , 'ETI' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('fo-FO' , 'Faroese - Faroe Islands' , '0x0438' , 'FOS' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('fa-IR' , 'Farsi - Iran' , '0x0429' , 'FAR' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('fi-FI' , 'Finnish - Finland' , '0x040B' , 'FIN' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('fr-BE' , 'French - Belgium' , '0x080C' , 'FRB' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('fr-CA' , 'French - Canada' , '0x0C0C' , 'FRC' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('fr-FR' , 'French - France' , '0x040C' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('fr-LU' , 'French - Luxembourg' , '0x140C' , 'FRL' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('fr-MC' , 'French - Monaco' , '0x180C' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('fr-CH' , 'French - Switzerland' , '0x100C' , 'FRS' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('gl-ES' , 'Galician - Galician' , '0x0456' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ka-GE' , 'Georgian - Georgia' , '0x0437' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('de-AT' , 'German - Austria' , '0x0C07' , 'DEA' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('de-DE' , 'German - Germany' , '0x0407' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('de-LI' , 'German - Liechtenstein' , '0x1407' , 'DEC' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('de-LU' , 'German - Luxembourg' , '0x1007' , 'DEL' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('de-CH' , 'German - Switzerland' , '0x0807' , 'DES' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('el-GR' , 'Greek - Greece' , '0x0408' , 'ELL' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('gu-IN' , 'Gujarati - India' , '0x0447' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('he-IL' , 'Hebrew - Israel' , '0x040D' , 'HEB' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('hi-IN' , 'Hindi - India' , '0x0439' , 'HIN' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('hu-HU' , 'Hungarian - Hungary' , '0x040E' , 'HUN' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('is-IS' , 'Icelandic - Iceland' , '0x040F' , 'ISL' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('id-ID' , 'Indonesian - Indonesia' , '0x0421' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('it-IT' , 'Italian - Italy' , '0x0410' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('it-CH' , 'Italian - Switzerland' , '0x0810' , 'ITS' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ja-JP' , 'Japanese - Japan' , '0x0411' , 'JPN' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('kn-IN' , 'Kannada - India' , '0x044B' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('kk-KZ' , 'Kazakh - Kazakhstan' , '0x043F' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('kok-IN' , 'Konkani - India' , '0x0457' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ko-KR' , 'Korean - Korea' , '0x0412' , 'KOR' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ky-KZ' , 'Kyrgyz - Kazakhstan' , '0x0440' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('lv-LV' , 'Latvian - Latvia' , '0x0426' , 'LVI' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('lt-LT' , 'Lithuanian - Lithuania' , '0x0427' , 'LTH' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('mk-MK' , 'Macedonian (FYROM)' , '0x042F' , 'MKD' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ms-BN' , 'Malay - Brunei' , '0x083E' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ms-MY' , 'Malay - Malaysia' , '0x043E' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('mr-IN' , 'Marathi - India' , '0x044E' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('mn-MN' , 'Mongolian - Mongolia' , '0x0450' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('nb-NO' , 'Norwegian (Bokmål) - Norway' , '0x0414' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('nn-NO' , 'Norwegian (Nynorsk) - Norway' , '0x0814' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('pl-PL' , 'Polish - Poland' , '0x0415' , 'PLK' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('pt-BR' , 'Portuguese - Brazil' , '0x0416' , 'PTB' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('pt-PT' , 'Portuguese - Portugal' , '0x0816' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('pa-IN' , 'Punjabi - India' , '0x0446' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ro-RO' , 'Romanian - Romania' , '0x0418' , 'ROM' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ru-RU' , 'Russian - Russia' , '0x0419' , 'RUS' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('sa-IN' , 'Sanskrit - India' , '0x044F' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('Cy-sr-SP' , 'Serbian (Cyrillic) - Serbia' , '0x0C1A' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('Lt-sr-SP' , 'Serbian (Latin) - Serbia' , '0x081A' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('sk-SK' , 'Slovak - Slovakia' , '0x041B' , 'SKY' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('sl-SI' , 'Slovenian - Slovenia' , '0x0424' , 'SLV' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-AR' , 'Spanish - Argentina' , '0x2C0A' , 'ESS' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-BO' , 'Spanish - Bolivia' , '0x400A' , 'ESB' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-CL' , 'Spanish - Chile' , '0x340A' , 'ESL' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-CO' , 'Spanish - Colombia' , '0x240A' , 'ESO' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-CR' , 'Spanish - Costa Rica' , '0x140A' , 'ESC' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-DO' , 'Spanish - Dominican Republic' , '0x1C0A' , 'ESD' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-EC' , 'Spanish - Ecuador' , '0x300A' , 'ESF' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-SV' , 'Spanish - El Salvador' , '0x440A' , 'ESE' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-GT' , 'Spanish - Guatemala' , '0x100A' , 'ESG' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-HN' , 'Spanish - Honduras' , '0x480A' , 'ESH' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-MX' , 'Spanish - Mexico' , '0x080A' , 'ESM' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-NI' , 'Spanish - Nicaragua' , '0x4C0A' , 'ESI' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-PA' , 'Spanish - Panama' , '0x180A' , 'ESA' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-PY' , 'Spanish - Paraguay' , '0x3C0A' , 'ESZ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-PE' , 'Spanish - Peru' , '0x280A' , 'ESR' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-PR' , 'Spanish - Puerto Rico' , '0x500A' , 'ES' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-ES' , 'Spanish - Spain' , '0x0C0A' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-UY' , 'Spanish - Uruguay' , '0x380A' , 'ESY' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('es-VE' , 'Spanish - Venezuela' , '0x200A' , 'ESV' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('sw-KE' , 'Swahili - Kenya' , '0x0441' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('sv-FI' , 'Swedish - Finland' , '0x081D' , 'SVF' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('sv-SE' , 'Swedish - Sweden' , '0x041D' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('syr-SY' , 'Syriac - Syria' , '0x045A' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ta-IN' , 'Tamil - India' , '0x0449' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('tt-RU' , 'Tatar - Russia' , '0x0444' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('te-IN' , 'Telugu - India' , '0x044A' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('th-TH' , 'Thai - Thailand' , '0x041E' , 'THA' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('tr-TR' , 'Turkish - Turkey' , '0x041F' , 'TRK' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('uk-UA' , 'Ukrainian - Ukraine' , '0x0422' , 'UKR' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('ur-PK' , 'Urdu - Pakistan' , '0x0420' , 'URD' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('Cy-uz-UZ' , 'Uzbek (Cyrillic) - Uzbekistan' , '0x0843' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('Lt-uz-UZ' , 'Uzbek (Latin) - Uzbekistan' , '0x0443' , ' ' )

insert into CultureInfo (CultureName , DisplayName , ISO_639x_Value , CultureCode ) values ('vi-VN' , 'Vietnamese - Vietnam' , '0x042A' , 'VIT' )

globalization table in sql server 2008

USE [ocms_dev]
GO

/****** Object:  Table [dbo].[CultureInfo]    Script Date: 06/23/2009 21:19:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CultureInfo](
    [CultureInfoId] [int] IDENTITY(1,1) NOT NULL,
    [CultureName] [varchar](10) COLLATE Finnish_Swedish_CI_AS NOT NULL,
    [DisplayName] [varchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
    [ISO_639x_Value] [nchar](6) COLLATE Finnish_Swedish_CI_AS NULL,
    [CultureCode] [nvarchar](10) COLLATE Finnish_Swedish_CI_AS NULL,
    [CollationName] [varchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
 CONSTRAINT [PK_CultureInfo] PRIMARY KEY CLUSTERED 
(
    [CultureInfoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING ON
GO

how-to change column collation in a table on sql server 2008

source: MBOL

ALTER TABLE MyTable ALTER COLUMN CharCol
            varchar(10)COLLATE Latin1_General_CI_AS NOT NULL
GO

how-to find the table name with the primary key by a given foreign key in SqlServer 2005 / 2008

SELECT TC.TABLE_NAME as tableName 
                        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
                        JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
                        ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME 
                        WHERE column_name='Region_Code'
                        AND CONSTRAINT_TYPE='PRIMARY KEY'

                        AND KCU.ORDINAL_POSITION=1

2009-06-18

Tsql data types

bigint
numeric
bit
smallint
decimal
smallmoney
int
tinyint
money
float
real
date
datetimeoffset
datetime2
smalldatetime
datetime
time
char
varchar
text
nchar
nvarchar
ntext
binary
varbinary
image
cursor
timestamp
hierarchyid
uniqueidentifier
sql_variant
xml
table

The grep for stored procedures on Sql Server

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/*********************************************************************
* Stored procedure  sp_grep 
* SQL Server:   Microsoft SQL Server 6.0, 4.21 for Windows NT, 
*               Microsoft SQL Server 4.2 for OS/2.
* Author:       Andrew Zanevsky, AZ Databases, Inc.
* Version/Date: Version 1.1,  October 26, 1995
* Description:  Searches syscomments table in the current database
*               for occurences of a combination of strings. 
*               Correclty handles cases when a substring begins in 
*               one row of syscomments and continues in the next. 
* Parameters: - @parameter describes the search:
*               string1 {operation1 string2} {operation2 string 3} ...
*               where - stringN is a string of characters enclosed in
*                       curly brackets not longer than 80 characters. 
*                       Brackets may be omitted if stringN does not 
*                       contain spaces or characters: +,-,&;
*                     - operationN is one of the characters: +,-,&.
*               Parameter is interpreted as follows:
*               1.Compose the list of all objects where string1 occurs.
*               2.If there is no more operations in the parameter,
*                 then display the list and stop. Otherwise continue.
*               3.If the next operation is + then add to the list all 
*                   objects where the next string occurs;
*                 else if the next operation is - then delete from the 
*                   list all objects where the next string occurs;
*                 else if the next operation is & then delete from the 
*                   list all objects where the next string does not 
*                   occur (leave in the list only those objects where 
*                   the next string occurs);
*               4.Goto step 2.
*               Parameter may be up to 255 characters long, and may not 
*               contain <CarriageReturn> or <LineFeed> characters.
*               Please note that operations are applied in the order
*               they are used in the parameter string (left to right). 
*               There is no other priority of executing them. Every 
*               operation is applied to the list combined as a result 
*               of all previous operations.
*               Number of spaces between words of a string matters in a
*               search (e.g. "select *" is not equal to "select  *").
*               Short or frequently used strings (such as "select") may 
*               produce a long result set.
*
*             - @case: i = insensitive / s = sensitive (default)
*               Insensitive search is performed regardless of this parameter 
*               if SQL Server is set up with case insensitive sort order.
*
* Examples:     sp_grep employee 
*                 list all objects where string 'employee' occurs;
*               sp_grep employee, i
*                 list all objects where string 'employee' occurs in 
*                 any case (upper, lower, or mixed), such as 
*                 'EMPLOYEE', 'Employee', 'employee', etc.;
*               sp_grep 'employee&salary+department-trigger'
*                 list all objects where either both strings 'employee'
*                 and 'salary' occur or string 'department' occurs, and 
*                 string 'trigger' does not occur;
*               sp_grep '{select FirstName + LastName}'
*                 list all objects where string 
*                 "select FirstName + LastName" occurs;
*               sp_grep '{create table}-{drop table}'
*                 list all objects where tables are created and not 
*                 dropped.
*                 
**********************************************************************/

-- sp_grep   v1.0 03/16/1995, v1.1 10/26/1995
-- Author:   Andrew Zanevsky, AZ Databases, Inc. 
-- E-mail:   zanevsky@azdatabases.com
create proc [dbo].[sp_grep] @parameter varchar(255) = null, @case char(1) = 's'
as

declare @str_no          tinyint, 
        @msg_str_no      varchar(3),
        @operation       char(1), 
        @string          varchar(80), 
        @oper_pos        smallint,
        @context         varchar(255),
        @i               tinyint,
        @longest         tinyint,
        @msg             varchar(255)

if @parameter is null /* provide instructions */
begin
    print 'Execute sp_grep "{string1}operation1{string2}operation2{string3}...", [case]'
    print '- stringN is a string of characters up to 80 characters long, '
    print '  enclosed in curly brackets. Brackets may be omitted if stringN '
    print '  does not contain leading and trailing spaces or characters: +,-,&.'
    print '- operationN is one of the characters: +,-,&. Interpreted as or,minus,and.'
    print '  Operations are executed from left to right with no priorities.'
    print '- case: specify "i" for case insensitive comparison.'
    print 'E.g. sp_grep "alpha+{beta gamma}-{delta}&{+++}"'
    print '     will search for all objects that have an occurence of string "alpha"'
    print '     or string "beta gamma", do not have string "delta", '
    print '     and have string "+++".'
    return
end

/* Check for <CarriageReturn> or <LineFeed> characters */
if charindex( char(10), @parameter ) > 0 or charindex( char(13), @parameter ) > 0
begin
    print 'Parameter string may not contain <CarriageReturn> or <LineFeed> characters.'
    return
end

if lower( @case ) = 'i'
        select  @parameter = lower( ltrim( rtrim( @parameter ) ) )
else
        select  @parameter = ltrim( rtrim( @parameter ) )

create table #search ( str_no tinyint, operation char(1), string varchar(80), last_obj int )
create table #found_objects ( id int, str_no tinyint )
create table #result ( id int )

/* Parse the parameter string */
select @str_no = 0
while datalength( @parameter ) > 0
begin
  /* Get operation */
  select @str_no = @str_no + 1, @msg_str_no = rtrim( convert( char(3), @str_no + 1 ) )
  if @str_no = 1
    select  @operation = '+'
  else 
  begin
    if substring( @parameter, 1, 1 ) in ( '+', '-', '&' )
        select  @operation = substring( @parameter, 1, 1 ),
                @parameter = ltrim( right( @parameter, datalength( @parameter ) - 1 ) )
    else
    begin
        select @context = rtrim( substring( 
                        @parameter + space( 255 - datalength( @parameter) ), 1, 20 ) )
        select @msg = 'Incorrect or missing operation sign before "' + @context + '".'
        print  @msg 
        select @msg = 'Search string ' + @msg_str_no + '.'
        print  @msg 
        return
    end
  end

  /* Get string */
  if datalength( @parameter ) = 0
  begin
      print 'Missing search string at the end of the parameter.'
      select @msg = 'Search string ' + @msg_str_no + '.'
      print  @msg 
      return
  end
  if substring( @parameter, 1, 1 ) = '{'
  begin
      if charindex( '}', @parameter ) = 0
      begin
          select @context = rtrim( substring( 
                      @parameter + space( 255 - datalength( @parameter) ), 1, 200 ) )
          select @msg = 'Bracket not closed after "' + @context + '".'
          print  @msg 
          select @msg = 'Search string ' + @msg_str_no + '.'
          print  @msg 
          return
      end
      if charindex( '}', @parameter ) > 82
      begin
          select @context = rtrim( substring( 
                      @parameter + space( 255 - datalength( @parameter) ), 2, 20 ) )
          select @msg = 'Search string ' + @msg_str_no + ' is longer than 80 characters.'
          print  @msg 
          select @msg = 'String begins with "' + @context + '".'
          print  @msg 
          return
      end        
      select  @string    = substring( @parameter, 2, charindex( '}', @parameter ) - 2 ),
              @parameter = ltrim( right( @parameter, 
                              datalength( @parameter ) - charindex( '}', @parameter ) ) )
  end
  else
  begin
      /* Find the first operation sign */
      select @oper_pos = datalength( @parameter ) + 1
      if charindex( '+', @parameter ) between 1 and @oper_pos
          select @oper_pos = charindex( '+', @parameter )
      if charindex( '-', @parameter ) between 1 and @oper_pos
          select @oper_pos = charindex( '-', @parameter )
      if charindex( '&', @parameter ) between 1 and @oper_pos
          select @oper_pos = charindex( '&', @parameter )

      if @oper_pos = 1
      begin
          select @context = rtrim( substring( 
                      @parameter + space( 255 - datalength( @parameter) ), 1, 20 ) )
          select @msg = 'Search string ' + @msg_str_no + 
                        ' is missing, before "' + @context + '".'
          print  @msg 
          return
      end        
      if @oper_pos > 81
      begin
          select @context = rtrim( substring( 
                      @parameter + space( 255 - datalength( @parameter) ), 1, 20 ) )
          select @msg = 'Search string ' + @msg_str_no + ' is longer than 80 characters.'
          print  @msg 
          select @msg = 'String begins with "' + @context + '".'
          print  @msg 
          return
      end        

      select  @string    = substring( @parameter, 1, @oper_pos - 1 ),
              @parameter = ltrim( right( @parameter, 
                              datalength( @parameter ) - @oper_pos + 1 ) )
  end
  insert #search values ( @str_no, @operation, @string, 0 )

end
select @longest = max( datalength( string ) ) - 1
from   #search
/* ------------------------------------------------------------------ */
/* Search for strings */
if @case = 'i'
begin
    insert #found_objects
    select a.id, c.str_no
    from   syscomments a, #search c
    where  charindex( c.string, lower( a.text ) ) > 0

    insert #found_objects
    select a.id, c.str_no
    from   syscomments a, syscomments b, #search c
    where  a.id        = b.id
    and    a.number    = b.number
    and    a.colid + 1 = b.colid
    and    charindex( c.string, 
                lower( right( a.text, @longest ) + 
/*                     space( 255 - datalength( a.text ) ) +*/
                       substring( b.text, 1, @longest ) ) ) > 0
end
else
begin
    insert #found_objects
    select a.id, c.str_no
    from   syscomments a, #search c
    where  charindex( c.string, a.text ) > 0

    insert #found_objects
    select a.id, c.str_no
    from   syscomments a, syscomments b, #search c
    where  a.id        = b.id
    and    a.number    = b.number
    and    a.colid + 1 = b.colid
    and    charindex( c.string, 
                right( a.text, @longest ) + 
/*              space( 255 - datalength( a.text ) ) +*/
                substring( b.text, 1, @longest ) ) > 0
end
/* ------------------------------------------------------------------ */
select distinct str_no, id into #dist_objects from #found_objects
create unique clustered index obj on #dist_objects  ( str_no, id )

/* Apply one operation at a time */
select @i = 0
while @i < @str_no
begin
    select @i = @i + 1
    select @operation = operation from #search where str_no = @i

    if @operation = '+'
        insert #result
        select id
        from   #dist_objects 
        where  str_no = @i
    else if @operation = '-'
        delete #result
        from   #result a, #dist_objects b
        where  b.str_no = @i
        and    a.id = b.id
    else if @operation = '&'
        delete #result
        where  not exists 
                ( select 1
                  from   #dist_objects b
                  where  b.str_no = @i
                  and    b.id = #result.id )
end

/* Select results */
select distinct id into #dist_result from #result

/* The following select has been borrowed from the sp_help 
** system stored procedure, and modified. */
select  Name        = o.name,
        /* Remove 'convert(char(15)' in the following line 
        ** if user names on your server are longer. */
        Owner       = convert( char(15), user_name(uid) ),
        Object_type = substring(v.name + x.name, 1, 16)
from    #dist_result           d,
        sysobjects             o, 
        master.dbo.spt_values  v,
        master.dbo.spt_values  x
where   d.id = o.id
/* SQL Server version 6.x uses 15, prior versions use 7 in expression below */
and     o.sysstat & ( 7 + 8 * sign( charindex( '6.', @@version ) ) ) = v.number
and     v.type = "O"
and     x.type = "R"
and     o.userstat & -32768 = x.number
order by Object_type desc, Name asc