Search This Blog

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 )

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

Labels

perl (41) Cheat Sheet (25) how-to (24) sql server 2008 (13) windows (13) linux (12) oracle (12) sql (12) Unix (11) cmd windows batch (10) mssql (10) cmd (9) script (9) textpad (9) netezza (8) sql server 2005 (8) cygwin (7) meta data mssql (7) metadata (7) bash (6) code generation (6) Informatica (5) cheatsheet (5) energy (5) tsql (5) utilities (5) excel (4) future (4) generic (4) html (4) perl modules (4) programs (4) settings (4) sh (4) shortcuts (4) поуки (4) принципи (4) Focus Fusion (3) Solaris (3) cool programs (3) development (3) economy (3) example (3) freeware (3) fusion (3) git cheat sheet (3) logging (3) morphus (3) mssql 2005 (3) nuclear (3) nz (3) parse (3) python (3) sftp (3) sofware development (3) source (3) sqlplus (3) table (3) vim (3) .Net (2) C# (2) China (2) GUI (2) Google (2) GoogleCL (2) Solaris Unix (2) ascii (2) awk (2) batch (2) cas (2) chrome extensions (2) code2html (2) columns (2) configuration (2) conversion (2) duplicates (2) excel shortcuts (2) export (2) file (2) free programs (2) informatica sql repository (2) mssql 2008 (2) mysql (2) next big future (2) nsis (2) nz netezza cheat sheet (2) nzsql (2) ora (2) prediction (2) publish (2) release management (2) report (2) security (2) single-click (2) sqlserver 2005 (2) sqlserver 2008 (2) src (2) ssh (2) template (2) tools (2) vba (2) video (2) xlt (2) xml (2) youtube videos (2) *nix (1) .vimrc (1) .virmrc vim settings configs (1) BSD license (1) Bulgaria (1) Dallas (1) Database role (1) Dense plasma focus (1) Deployment (1) ERP (1) ExcelToHtml (1) GD (1) GDP (1) HP-UX (1) Hosting (1) INC (1) IT general (1) ITIL management bullshit-management (1) IZarc (1) Java Web Start (1) JavaScript anchor html jquery (1) Khan Academy (1) LINUX UNIX BASH AND CYGWIN TIPS AND TRICKS (1) Linux Unix rpm cpio build install configure (1) Linux git source build .configure make (1) ListBox (1) MIT HYDROGEN VIRUS (1) OO (1) Obama (1) PowerShell (1) Run-time (1) SDL (1) SIWA (1) SOX (1) Scala (1) Services (1) Stacks (1) SubSonic (1) TED (1) abstractions (1) ansible hosts linux bash (1) ansible linux deployment how-to (1) ansible yum pip python (1) apache (1) apache 2.2 (1) application life cycle (1) architecture (1) archive (1) arguments (1) avatar (1) backup (1) bash Linux open-ssh ssh ssh_server ssh_client public-private key authentication (1) bash perl search and replace (1) bash stub (1) bin (1) biofuels (1) biology (1) books (1) browser (1) bubblesort (1) bugs (1) build (1) byte (1) cas_sql_dev (1) chennai (1) chrome (1) class (1) claut (1) cmdow (1) code generation sqlserver (1) command (1) command line (1) conf (1) confluence (1) console (1) convert (1) cool programs windows free freeware (1) copy-paste (1) csv (1) ctags (1) current local time (1) cygwin X11 port-forwarding mintty xclock Linux Unix X (1) cygwin bash how-to tips_n_tricks (1) cygwin conf how-to (1) data (1) data types (1) db2 cheat sheet (1) db2 starter ibm bash Linux (1) debt (1) diagram (1) dictionaries (1) digital (1) disk (1) disk space (1) documentation (1) dos (1) dubai (1) e-cars (1) electric cars (1) electricity (1) emulate (1) errors (1) exponents (1) export workflow (1) extract (1) fast export (1) fexp (1) file extension (1) file permissions (1) findtag (1) firewall (1) for loop (1) freaky (1) functions (1) fusion research (1) german (1) git gitlab issues handling system (1) google cli (1) google code (1) google command line interface (1) gpg (1) ha (1) head (1) helsinki (1) history (1) hop or flop (1) host-independant (1) how-to Windows cmd time date datetime (1) ibm db2 cognos installation example db deployment provisioning (1) ideas (1) image (1) informatica oracle sql (1) informatica repo sql workflows sessions file source dir (1) informatica source files etl (1) install (1) it management best practices (1) java (1) jump to (1) keyboard shortcuts (1) ksh (1) level (1) linkedin (1) linux bash ansible hosts (1) linux bash commands (1) linux bash how-to shell expansion (1) linux bash shell grep xargs (1) linux bash tips and t ricks (1) linux bash unix cygwin cheatsheet (1) linux bash user accounts password (1) linux bash xargs space (1) linux cheat sheet (1) linux cheat-sheet (1) linux cheatsheet cheat-sheet revised how-to (1) linux how-to non-root vim (1) linux ssh hosts parallel subshell bash oneliner (1) london (1) make (1) me (1) metacolumn (1) metadata functions (1) metaphonre (1) method (1) model (1) movie (1) multithreaded (1) mysql cheat sheet (1) mysql how-to table datatypes (1) n900 (1) nano (1) neteza (1) netezza bash linux nps (1) netezza nps (1) netezza nps nzsql (1) netezza nz Linux bash (1) netezza nz bash linux (1) netezza nz nzsql sql (1) netezza nzsql database db sizes (1) non-password (1) nord pol (1) nps backup nzsql schema (1) number formatting (1) nz db size (1) nz table count rows (1) nzsql date timestamp compare bigint to_date to_char now (1) on-lier (1) one-liners (1) one-to-many (1) oneliners (1) open (1) open source (1) openrowset (1) oracle PL/SQL (1) oracle Perl perl (1) oracle installation usability (1) oracle number formatting format-model ora-sql oracle (1) oracle templates create table (1) oracle trigger generic autoincrement (1) oracle vbox virtual box cheat sheet (1) oracle virtual box cheat sheet (1) outlook (1) parser (1) password (1) paths (1) perl @INC compile-time run-time (1) perl disk usage administration Linux Unix (1) perl modules configuration management (1) permissions (1) php (1) picasa (1) platform (1) postgreSQL how-to (1) powerShell cmd cygwin mintty.exe terminal (1) ppm (1) predictions (1) prices (1) principles (1) productivity (1) project (1) prompt (1) proxy account (1) public private key (1) publishing (1) putty (1) qt (1) read file (1) registry (1) relationship (1) repository (1) rm (1) scp (1) scripts (1) scsi (1) search and replace (1) sed (1) sendEmail (1) sh stub (1) shortcuts Windows sql developer Oracle (1) sidebar (1) silicon (1) smtp (1) software procurement (1) sofware (1) sort (1) sql script (1) sql_dev (1) sqlcmd (1) sqlite (1) sqlite3 (1) sshd (1) sshd cygwin (1) stackoverflow (1) stored procedure (1) stub (1) stupidity (1) subroutines (1) svn (1) sysinternals (1) tail (1) tar (1) temp table (1) templates (1) teradata (1) terminal (1) test (1) testing (1) theory (1) thorium (1) time (1) tip (1) title (1) tmux .tmux.conf configuration (1) tmux efficiency bash (1) tool (1) ui code prototyping tips and tricks (1) umask Linux Unix bash file permissions chmod (1) url (1) urls (1) user (1) utility (1) utils (1) vb (1) vim perl regex bash search for string (1) vim recursively hacks (1) vim starter (1) vim-cheat-sheet vim cheat-sheet (1) vimeo (1) visual stuio (1) warsaw (1) wiki (1) wikipedia (1) window (1) windows 7 (1) windows 8 (1) windows programs (1) windows reinstall (1) windows utility batch perl space Windows::Clipboard (1) wisdoms (1) workflow (1) worth-reading (1) wrapper (1) xp_cmdshell (1) xslt (1) youtube (1)

Blog Archive

Translate with Google Translate

My Blog List

VideoBar

This content is not yet available over encrypted connections.