Search This Blog

Friday, February 3, 2012

Search a keyword in Sql2005/2008 in all tables

How to search all columns of all tables in a database for a keyword?

While browsing the SQL Server newsgroups, every once in a while, I see a request for a script that can search all the columns of all the tables in a given database for a specific keyword. I never took such posts seriously. But then recently, one of my network administrators was troubleshooting a problem with Microsoft Operations Manager (MOM). MOM uses SQL Server for storing all the computer, alert and performance related information. He narrowed the problem down to something specific, and needed a script that can search all the MOM tables for a specific string. I had no such script handy at that time, so we ended up searching manually.

That's when I really felt the need for such a script and came up with this stored procedure "SearchAllTables". It accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database. Feel free to extend this procedure to search other datatypes.

The output of this stored procedure contains two columns:

- 1) The table name and column name in which the search string was found
- 2) The actual content/value of the column (Only the first 3630 characters are displayed)

Here's a word of caution, before you go ahead and run this procedure. Though this procedure is quite quick on smaller databases, it could take hours to complete, on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (I did use the locking hint NOLOCK to reduce any locking). It is efficient to use Full-Text search feature for free text searching, but it doesn't make sense for this type of ad-hoc requirements.

Create this procedure in the required database and here is how you run it:

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'

Here is the complete stored procedure code:

CREATE PROC SearchAllTables
 @SearchStr nvarchar(100)

 CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))


 DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
 SET  @TableName = ''
 SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

  SET @ColumnName = ''
  SET @TableName = 
        ), 'IsMSShipped'
             ) = 0

  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
   SET @ColumnName =
     AND TABLE_NAME = PARSENAME(@TableName, 1)
     AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
   IF @ColumnName IS NOT NULL
    INSERT INTO #Results
     'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
     FROM ' + @TableName + ' (NOLOCK) ' +
     ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

 SELECT ColumnName, ColumnValue FROM #Results

ref:-by check this

Thursday, February 2, 2012

Interview Question and Answer on OOPs in C#

OOPs Interview Questions
A user-defined data structure that groups properties and methods. Class doesn’t occupy memory.
Instance of Class is called object. An object is created in memory using keyword “new”.
Difference between Struct and Class
  • Struct are Value type and are stored on stack, while Class are Reference type and are stored on heap.
  • Struct “do not support” inheritance, while class supports inheritance. However struct can implements interface.
  • Struct should be used when you want to use a small data structure, while Class is better choice for complex data structure.

What is the difference between instantiating structures with and without using the new keyword?
When a structure is instantiated using the new keyword, a constructor (no-argument or custom, if provided) is called which initializes the fields in the structure. When a structure is instantiated without using the new keyword, no constructor is called. Hence, one has to explicitly initialize all the fields of the structure before using it when instantiated without the new keyword.


Abstraction defines way to abstract or hide your data and members from outside world. Simply speaking Abstraction is hiding the complexities of your class or struct or in a generic term Type from outer world. This is achieved by means of access specifiers.
In short : - Hiding the complexities of your type from outside world.
How to Abstract: - By using Access Specifiers
.Net has five access specifiers:-
Public -- Accessible outside the class through object reference.
Private -- Accessible inside the class only through member functions.
Protected -- Just like private but Accessible in derived classes also through member functions.
Internal -- Visible inside the assembly. Accessible through objects.
Protected Internal -- Visible inside the assembly through objects and in derived classes outside the assembly through member functions.
Let’s try to understand by a practical example:-
Interview Tip:-
The default access specifier for a class in internal. Mind it I mean class not class’s data members.
Wrapping up of data and function into a single unit is known as Encapsulation.
Attribute of object is called properties. Eg1:- A car has color as property.
private string m_Color;;
public string Color
return m_Color;
m_Color = value;
Car Maruti = new Car();
Maruti.Color= “White”;
Isn't it better to make a field public than providing its property with both set { } and get { } block? After all the property will allow the user to both read and modify the field so why not use public field instead? Motivate your answer

not always! Properties are not just to provide access to the fields; rather, they are supposed to provide controlled access to the fields of our class. As the state of the class depends upon the values of its fields, using properties we can assure that no invalid (or unacceptable) value is assigned to the fields.
private int age;
public int Age
return age;
if(value <> 100)
//throw exception
age = value;

this Keyword
Each object has a reference “this” which points to itself.
Two uses of this keyword.
o Can be used to refer to the current object.
o It can also be used by one constructor to explicitly invoke another constructor of the same class.
class Student
private string name;
private int age;
Student(string name, int age)
{ = name;
this.age = age;
class Circle
double x,y,radius;
Circle(double x){
Circle(double x, double y){
Circle(double x, double y, double radius){
this.x = x;
this.y = y;
this.radius = radius;
  • A constructor is a special method whose task is to initialize the object of its class.
  • It is special because its name is the same as the class name.
  • They do not have return types, not even void and therefore they cannot return values.
  • They cannot be inherited, though a derived class can call the base class constructor.
  • Constructor is invoked whenever an object of its associated class is created.
  • Note: There is always at least one constructor in every class. If you do not write a constructor, C# automatically provides one for you, this is called default constructor. Eg: class A, default constructor is A().
Static Members of the class
Static members belong to the whole class rather than to individual object
Static members are accessed with the name of class rather than reference to objects.
class Test
public int rollNo;
public int mathsMarks;
public static int totalMathMarks;
class TestDemo
public static void main()
Test stud1 = new Test();
stud1.rollNo = 1;
stud1.mathsMarks = 40;
stud2.rollNo = 2;
stud2.mathsMarks = 43;
Test.totalMathsMarks = stud1.mathsMarks + stud2.mathsMarks;
Static Method of the class
Methods that you can call directly without first creating an instance of a class. Eg: Main() Method, Console.WriteLine()
You can use static fields, methods, properties and even constructors which will be called before any instance of the class is created.
As static methods may be called without any reference to object, you cannot use instance members inside static methods or properties, while you may call a static member from a non-static context. The reason for being able to call static members from non-static context is that static members belong to the class and are present irrespective of the existence of even a single object.
Static Constructor
In C# it is possible to write a static no-parameter constructor for a class. Such a class is executed once, when first object of class is created.
One reason for writing a static constructor would be if your class has some static fields or properties that need to be initialized from an external source before the class is first used.
Class MyClass
static MyClass()
//Initialization Code for static fields and properties.
Finalize() Method of Object classEach class in C# is automatically (implicitly) inherited from the Object class which contains a method Finalize(). This method is guaranteed to be called when your object is garbage collected (removed from memory). You can override this method and put here code for freeing resources that you reserved when using the object.
For example
Protected override void Finalize()
Console.WriteLine(“Destructing Object….”);
//put some code here.
A destructor is just opposite to constructor.
It has same as the class name, but with prefix ~ (tilde).
They do not have return types, not even void and therefore they cannot return values.
Destructor is invoked whenever an object is about to be garbage collected
class person
//put resource freeing code here.
What is the difference between the destructor and the Finalize() method? When does the Finalize() method get called?
Finalize() corresponds to the .Net Framework and is part of the System.Object class. Destructors are C#'s implementation of the Finalize() method. The functionality of both Finalize() and the destructor is the same, i.e., they contain code for freeing the resources when the object is about to be garbage collected. In C#, destructors are converted to the Finalize() method when the program is compiled. The Finalize() method is called by the .Net Runtime and we cannot predict when it will be called. It is guaranteed to be called when there is no reference pointing to the object and the object is about to be garbage collected.
Garbage Collection
  • Garbage collection is the mechanism that reclaims the memory resources of an object when it is no longer referenced by a variable.
  • .Net Runtime performs automatically performs garbage collection, however you can force the garbage collection to run at a certain point in your code by callingSystem.GC.Collect().
  • Advantage of Garbage collection: It prevents programming error that could otherwise occur by incorrectly deleting or failing to delete objects.

Enumeration improves code readability. It also helps in avoiding typing mistake.

Concept of Heap and Stack
The Program Instruction and Global and Static variables are stored in a region known as permanent storage area and the local variables are stored in another area called stack. The memory space located between these two regions is available for dynamic memory allocation during execution of program. This free memory region is called heap. The size of heap keeps on changing when program is executed due to creation and death of variables that are local to functions and blocks. Therefore, it is possible to encounter memory “overflow” during dynamic allocation process.

Value Type and Reference Type
A variable is value type or reference type is solely determined by its data type.
Eg: int, float, char, decimal, bool, decimal, struct, etc are value types, while object type such as class, String, Array, etc are reference type.
Value Type
As name suggest Value Type stores “value” directly.
For eg:
//I and J are both of type int
I = 20;
J = I;
int is a value type, which means that the above statements will results in two locations in memory.
For each instance of value type separate memory is allocated.
Stored in a Stack.
It Provides Quick Access, because of value located on stack.
Reference Type
As name suggest Reference Type stores “reference” to the value.
For eg:
Vector X, Y; //Object is defined. (No memory is allocated.)
X = new Vector(); //Memory is allocated to Object. //(new is responsible for allocating memory.)
X.value = 30; //Initialising value field in a vector class.
Y = X; //Both X and Y points to same memory location. //No memory is created for Y.
Console.writeline(Y.value); //displays 30, as both points to same memory
Y.value = 50;
Console.writeline(X.value); //displays 50.
Note: If a variable is reference it is possible to indicate that it does not refer to any object by setting its value to null;
Reference type are stored on Heap.
It provides comparatively slower access, as value located on heap.
ref keyword
Passing variables by value is the default. However, we can force the value parameter to be passed by reference. Note: variable “must” be initialized before it is passed into a method.
out keyword
out keyword is used for passing a variable for output purpose. It has same concept as ref keyword, but passing a ref parameter needs variable to be initialized while out parameter is passed without initialized.
It is useful when we want to return more than one value from the method.
Note: You must assigned value to out parameter in method body, otherwise the method won’t compiled.

Boxing and Un-Boxing
Boxing: means converting value-type to reference-type.
int I = 20;
string s = I.ToSting();
UnBoxing: means converting reference-type to value-type.
int I = 20;
string s = I.ToString(); //Box the int
int J = Convert.ToInt32(s); //UnBox it back to an int.
Note: Performance Overheads due to boxing and unboxing as the boxing makes a copy of value type from stack and place it inside an object of type System.Object in the heap.
The process of sub-classing a class to extend its functionality is called Inheritance.
It provides idea of reusability.
Order of Constructor execution in Inheritance
constructors are called in the order from the top to the bottom (parent to child class) in inheritance hierarchy.
Order of Destructor execution in Inheritance
The destructors are called in the reverse order, i.e., from the bottom to the top (child to parent class) in the inheritance hierarchy.
What are Sealed Classes in C#?The sealed modifier is used to prevent derivation from a class. A compile-time error occurs if a sealed class is specified as the base class of another class. (A sealed class cannot also be an abstract class)
Can you prevent your class from being inherited by another class?Yes. The keyword “sealed” will prevent the class from being inherited.
Can you allow a class to be inherited, but prevent the method from being over-ridden?Yes. Just leave the class public and make the method sealed.
Fast Facts of Inheritance
Multiple inheritance of classes is not allowed in C#.
In C# you can implements more than one interface, thus multiple inheritance is achieved through interface.
The Object class defined in the System namespace is implicitly the ultimate base class of all the classes in C# (and the .NET framework)
Structures (struct) in C# does not support inheritance, it can only implements interfaces.
Polymorphism means same operation may behave differently on different classes.
Method Overloading is an example of Compile Time Polymorphism.
Method Overriding is an example of Run Time Polymorphism
Does supports multiple inheritance?
No. A class can inherit from only one base class, however a class can implements many interface, which servers some of the same purpose without increasing complexity.
How many types of Access Modifiers.
1) Public – Allows the members to be globally accessible.
2) Private – Limits the member’s access to only the containing type.
3) Protected – Limits the member’s access to the containing type and all classes derived from the containing type.
4) Internal – Limits the member’s access to within the current project.
Method Overloading
Method with same name but with different arguments is called method overloading.
Method Overloading forms compile-time polymorphism.
class A1
void hello()
{ Console.WriteLine(“Hello”); }
void hello(string s)
{ Console.WriteLine(“Hello {0}”,s); }
Method Overriding
Method overriding occurs when child class declares a method that has the same type arguments as a method declared by one of its superclass.
Method overriding forms Run-time polymorphism.
Note: By default functions are not virtual in C# and so you need to write “virtual” explicitly. While by default in Java each function are virtual.
Class parent
virtual void hello()
{ Console.WriteLine(“Hello from Parent”); }
Class child : parent
override void hello()
{ Console.WriteLine(“Hello from Child”); }
static void main()
parent objParent = new child();
Hello from Child.
Virtual Method
By declaring base class function as virtual, we allow the function to be overridden in any of derived class.
Class parent
virtual void hello()
{ Console.WriteLine(“Hello from Parent”); }
Class child : parent
override void hello()
{ Console.WriteLine(“Hello from Child”); }
static void main()
parent objParent = new child();
Hello from Child.
Concept of Interface
What is Interface
  • An Interface is a group of constants and method declaration.
  • .Net supports multiple inheritance through Interface.
  • Interface states “what” to do, rather than “how” to do.
  • An interface defines only the members that will be made available by an implementing object. The definition of the interface states nothing about the implementation of the members, only the parameters they take and the types of values they will return. Implementation of an interface is left entirely to the implementing class. It is possible, therefore, for different objects to provide dramatically different implementations of the same members.
  • Example1, the Car object might implement the IDrivable interface (by convention, interfaces usually begin with I), which specifies the GoForward, GoBackward, and Halt methods. Other classes, such as Truck, Aircraft, Train or Boat might implement this interface and thus are able to interact with the Driver object. The Driver object is unaware of which interface implementation it is interacting with; it is only aware of the interface itself.
  • Example2, an interface named IShape, which defines a single method CalculateArea. A Circle class implementing this interface will calculate its area differently than a Square class implementing the same interface. However, an object that needs to interact with an IShape can call the CalculateArea method in either a Circle or a Square and obtain a valid result.
  • Practical Example
public interface IDrivable
void GoForward(int Speed);

public class Truck : IDrivable
public void GoForward(int Speed)
// Implementation omitted

public class Aircraft : IDrivable
public void GoForward(int Speed)
// Implementation omitted

public class Train : IDrivable
public void GoForward(int Speed)
// Implementation omitted

  • Each variable declared in interface must be assigned a constant value.
  • Every interface variable is implicitly public, static and final.
  • Every interface method is implicitly public and abstract.
  • Interfaces are allowed to extends other interfaces, but sub interface cannot define the methods declared in the super interface, as sub interface is still interface and not class.
  • If a class that implements an interface does not implements all the methods of the interface, then the class becomes an abstract class and cannot be instantiated.
  • Both classes and structures can implement interfaces, including multiple interfaces.
Making choice between Interface and Abstract Class
In which Scenario you will go for Interface or Abstract Class?
Interfaces, like classes, define a set of properties, methods, and events. But unlike classes, interfaces
do not provide implementation. They are implemented by classes, and defined as separate entities from
classes. Even though class inheritance allows your classes to inherit implementation from a base class, it
also forces you to make most of your design decisions when the class is first published.

Abstract classes are useful when creating components because they allow you specify an invariant level
of functionality in some methods, but leave the implementation of other methods until a specific
implementation of that class is needed. They also version well, because if additional functionality is
needed in derived classes, it can be added to the base class without breaking code.

Difference between Interface and Abstract Class

Abstract class
Multiple inheritance
A class may implement several interfaces.
A class may extend only one abstract class.
Default implementation
An interface cannot provide any code at all, much less default code.
An abstract class can provide complete code, default code, and/or just stubs that have to be overridden.
Static final constants only, can use them without qualification in classes that implement the interface. On the other paw, these unqualified names pollute the namespace. You can use them and it is not obvious where they are coming from since the qualification is optional.
Both instance and static constants are possible. Both static and instance initialize code are also possible to compute the constants.
Third party convenience
An interface implementation may be added to any existing third party class.
A third party class must be rewritten to extend only from the abstract class.
Is-a vs -able or can-do
Interfaces are often used to describe the peripheral abilities of a class, not its central identity, e.g. an Automobile class might implement the Recyclable interface, which could apply to many otherwise totally unrelated objects.
An abstract class defines the core identity of its descendants. If you defined a Dog abstract class then Damamation descendants are Dogs, they are not merely dogable. Implemented interfaces enumerate the general things a class can do, not the things a class is.
You can write a new replacement module for an interface that contains not one stick of code in common with the existing implementations. When you implement the interface, you start from scratch without any default implementation. You have to obtain your tools from other classes; nothing comes with the interface other than a few constants. This gives you freedom to implement a radically different internal design.
You must use the abstract class as-is for the code base, with all its attendant baggage, good or bad. The abstract class author has imposed structure on you. Depending on the cleverness of the author of the abstract class, this may be good or bad. Another issue that's important is what I call "heterogeneous vs. homogeneous." If implementors/subclasses are homogeneous, tend towards an abstract base class. If they are heterogeneous, use an interface. (Now all I have to do is come up with a good definition of hetero/homogeneous in this context.) If the various objects are all of-a-kind, and share a common state and behavior, then tend towards a common base class. If all they share is a set of method signatures, then tend towards an interface.
If all the various implementations share is the method signatures, then an interface works best.
If the various implementations are all of a kind and share a common status and behavior, usually an abstract class works best.
If your client code talks only in terms of an interface, you can easily change the concrete implementation behind it, using a factory method.
Just like an interface, if your client code talks only in terms of an abstract class, you can easily change the concrete implementation behind it, using a factory method.
Slow, requires extra indirection to find the corresponding method in the actual class. Modern JVMs are discovering ways to reduce this speed penalty.
The constant declarations in an interface are all presumed public static final, so you may leave that part out. You can't call any methods to compute the initial values of your constants. You need not declare individual methods of an interface abstract. They are all presumed so.
You can put shared code into an abstract class, where you cannot into an interface. If interfaces want to share code, you will have to write other bubblegum to arrange that. You may use methods to compute the initial values of your constants and variables, both instance and static. You must declare all the individual methods of an abstract class abstract.
Adding functionality
If you add a new method to an interface, you must track down all implementations of that interface in the universe and provide them with a concrete implementation of that method.
If you add a new method to an abstract class, you have the option of providing a default implementation of it. Then all existing code will continue to work without change.