Friday, October 24, 2008

SQL SERVER - Find Stored Procedure Related to Table in Database - Search in All Stored procedure

The below query will return the object name and as well as the object type.

CASE o.xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE o.xtype
END AS ObjectType,
ISNULL( p.Name, '[db]') AS Location
FROM syscomments c
INNER JOIN sysobjects o ON
LEFT JOIN sysobjects p ON
WHERE c.text LIKE '%code/SQL command/any text to search for%'
ORDER BY Location, ObjectName

SQL SERVER - Find Stored Procedure Related to Table in Database - Search in All Stored procedure

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.

   ----Option 1
FROM syscomments sc
INNER JOIN sysobjects so ON
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
FROM syscomments c
INNER JOIN sysobjects o ON
WHERE c.TEXT LIKE '%tablename%'

Monday, September 1, 2008

JavaScript Password Validation

Here are the validation types supported out of the box. All are optional, which means that all passwords are allowed by default.

  • Minimum and maximum length.
  • Minimum n lowercase characters (a–z).
  • Minimum n uppercase characters (A–Z).
  • Minimum n combined a–z and A–Z characters.
  • Minimum n numeric characters (0–9).
  • Minimum n special characters (characters other than a–z, A–Z, and 0–9).
  • Ban particular words (tested case-insensitively).
  • Ban n-length character sequences (e.g. "abc", "XYZ", or "789", with a sequence length of 3; does not apply to special characters).
  • Ban n-length qwerty character sequences (e.g. "qwerty" or "asdf", with a sequence length of 4; does not apply to special characters).
  • Ban sequential, identical characters (e.g. "aa" or "!!").
  • Use custom regular expressions (tested using RegExp.prototype.test) and functions (the password is provided as the first argument, and a Boolean value is expected in return).

Here's an example of how it can be used:

var password = "password";
var passed = validatePassword(password, {
length: [8, Infinity],
lower: 1,
upper: 1,
numeric: 1,
special: 1,
badWords: ["password", "steven", "levithan"],
badSequenceLength: 4
// passed: false

The above requires that password is at least eight characters long; has at least one lowercase, uppercase, numeric, and special character; doesn't include the words "password", "steven", or "levithan"; and doesn't include an alphanumeric sequence four or more characters in length (e.g. "1234").

Here's the code (there are no external library dependencies):

function validatePassword (pw, options) {
// default options (allows any password)
var o = {
lower: 0,
upper: 0,
alpha: 0, /* lower + upper */
numeric: 0,
special: 0,
length: [0, Infinity],
custom: [ /* regexes and/or functions */ ],
badWords: [],
badSequenceLength: 0,
noQwertySequences: false,
noSequential: false

for (var property in options)
o[property] = options[property];

var re = {
lower: /[a-z]/g,
upper: /[A-Z]/g,
alpha: /[A-Z]/gi,
numeric: /[0-9]/g,
special: /[\W_]/g
rule, i;

// enforce min/max length
if (pw.length <> o.length[1])
return false;

// enforce lower/upper/alpha/numeric/special rules
for (rule in re) {
if ((pw.match(re[rule]) || []).length < o[rule])
return false;

// enforce word ban (case insensitive)
for (i = 0; i < o.badWords.length; i++) {
if (pw.toLowerCase().indexOf(o.badWords[i].toLowerCase()) > -1)
return false;

// enforce the no sequential, identical characters rule
if (o.noSequential && /([\S\s])\1/.test(pw))
return false;

// enforce alphanumeric/qwerty sequence ban rules
if (o.badSequenceLength) {
var lower = "abcdefghijklmnopqrstuvwxyz",
upper = lower.toUpperCase(),
numbers = "0123456789",
qwerty = "qwertyuiopasdfghjklzxcvbnm",
start = o.badSequenceLength - 1,
seq = "_" + pw.slice(0, start);
for (i = start; i < pw.length; i++) {
seq = seq.slice(1) + pw.charAt(i);
if (
lower.indexOf(seq) > -1 ||
upper.indexOf(seq) > -1 ||
numbers.indexOf(seq) > -1 ||
(o.noQwertySequences && qwerty.indexOf(seq) > -1)
) {
return false;

// enforce custom regex/function rules
for (i = 0; i < o.custom.length; i++) {
rule = o.custom[i];
if (rule instanceof RegExp) {
if (!rule.test(pw))
return false;
} else if (rule instanceof Function) {
if (!rule(pw))
return false;

// great success!
return true;


Tuesday, August 26, 2008

Convert string to Title Case/Sentence Case in C#

The ToTitleCase method resides in the TextInfo class which is a member of the System.Globalization namespace. The ToTitleCase method is not static and needs an instance of the class.
To gain access to the TextInfo class you first have to specify cultural information which you can get from the CurrentCulture property of the thread the code is running on.


using System.Globalization;
using System.Threading;

//Create CultureInfo and TextInfo classes to use ToTitleCase method
CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;
TextInfo textInfo = cultureInfo.TextInfo;

oClient.ClientName = textInfo.ToTitleCase(txtClientName.Text);

Ps: Title Case converts every first letter of a word in a sentence to upper case, and the rest to lower case.

Adding contains(t) function to JavaScript String.prototype

The contains(t) function returns true if the string contains the value of the parameter "t" and false if it doesn’t. To extend the String.prototype with the contains(t) function I use this line of code:

String.prototype.contains = function(t) { return this.indexOf(t) >= 0 ? true : false }

This is sample code how to use the contains(t) function:

var sMyVar = new String (" testing text ");

//use it with the value of a text box
document.getElementById("txtMyTextBox").value.contains(’some text’);

Use of Row_Number function for custom paging

The Row_Number() function in SQL is used to assign sequence number for each record retrieved for a collection of records.

Hence, we can use this function to retrieve a portion of records (Custom) from the collection of records.

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [Field Used for Sorting])AS Row, * FROM ([Select Query])AS UserQuery) AS SelectionWithRowNumbers WHERE Row >[First Record]AND Row<=[Last Record]

Creating Property Class from XML : C#

If the XML structure is simple its easier to create a class file for serialisation.
This process consists of 3 steps as follows:

Go to Visual Studio 2005 -> Visual Studio Tools -> Visual Studio Command Prompt .

Let's consider our XML file name is like Menu.xml and is placed the D:/ drive,
Step 1: In the command prompt first point to the drive location where the XML file
is located for ex., in our case use the command d:
ex., C:\Program Files\Microsoft Visual Studio 8\VC>d:

Step 2: xsd Menu.xml
After this step, a file named Menu.xsd was created
Step 3: xsd Menu.xsd /classes
After this step, a file named Menu.cs was created

This Menu.cs file is the final class file contains the property classes for the XML.