Searching a table, sp etc sql server database object ?

How many times, we as a SQL developer or DBA, find ourselves shuffling through objects in Enterprise Manager, or expanding the left pane of Query Analyzer, trying to find a table or view for which we have no clue, except a nearly correct name, and the only way we would know that it is the right object is looking at its meta data or text. Well, it might not be an every day kind of thing, but it does happen from time to time (or perhaps not in an idealistic situation where all databases are well documented and all names follow a well defined naming convention with no exceptions, and most of all, the employees never quit).

A better why to find a SQL Server object, such as a table, a procedure, or a trigger, would be to query the sysobjects system table in the local database (of course, one has to be certain about which database that object is supposed be in).

For example:

Select * From sysobjects Where name like ‘ClientInvoice%’

(Script I)

Executing the above query displays all the objects in current database whose name starts with "ClientInvoice". If the type of the object to be searched is known, then the query can be changed to provide only that type of object whose name start with "ClientInvoice". This might return a much smaller and more readable resultset.

For example:

Select * From sysobjects Where xtype = ‘U’ And name like ‘ClientInvoice%’

-- ‘U’ for user table

(Script II)

The main shortcoming of above methods is that the sysobjects table is database specific. If one does not know which database contains the object, then the above query has to be run in all the databases to find the object.

Is there an easier way to write a query which searches all the databases in a single step to locate a specific object and/or of a specific object type? The answer is yes, by using the handy sp_MSforeachdb procedure.

For example:

Exec sp_MSforeachdb 'Select * From ?..sysobjects where xtype= ''U'' And name like ''ClientInvoice% '''

(Script III)

Sp_MSforeachdb is an undocumented (also means unsupported) procedure available in both SQL Server 7 and SQL Server 2000. It takes one string argument, which in our case is same as Script II, but there is one important difference, if we look carefully at Script III, it has “From ?..sysobjects” instead of simply “From sysobjects” as in Script II. 

Why quit Non-Vegetarian food?

If you are a pure non-vegetarian, you will probably raise a hue and cry at the thought of turning vegetarian! But the truth is that meat of any kind is tough on your smart cells. If Nature intended you to be a non-vegetarian, she would have equipped you with claws, and sharp pointed teeth to tear into any flesh; given you acidic saliva to digest the animal protein; a round stomach with the capacity to produce plenty of hydrochloric acid, fewer intestines to shorten the process of digesting the meat so that you could expel it before it putrefies inside; a liver that could dispel more uric acid than it does.

Instead, we have hands with moveable fingers that can delicately pluck and peel fruits; blunt teeth and molars that crush and grind; alkaline saliva to digest plant protein; a stomach that produces small quantities of hydrochloric acid; more intestines that hold on to the food and give time for your smart cells to extract the required nutrients, a liver that can expel small amounts of uric acid.

Look around at your circle of friends. If you find one of them is suffering from gout, you can be sure he is a heavy red-meat eater. You can almost bet that he eats salami or sausages for breakfast, a steak for lunch and meat for dinner. Then, one day, after a leisurely weekend of imbibing an overdose of scotch-on-the-rocks and liberal helpings of cold cuts as hors d' oeuvres, and a plate piled with Mutton Moghlai, he would have slept feeling well-dined and content. Only to be jolted awake with his big toe pounding with pain. The gout had struck!

What happened? His prolonged meat-eating habit had put an overload on his liver. The excess uric acid had no place to go in his system, so it turned into crystals and settled in the joint of his big toe - and, pow! (What does his doctor recommend? To stay off meat and eat only vegetables, at least until the inflammation recedes.)

The onset of gout is only one side of the unhealthy coin. Meat is so high in indigestible fat content that it clogs up your arteries and can lead to a heart problem. It gives you no energy because though it contains carbohydrates, its also has undigestible animal protein and a high content of fat. That's the reason you feel heavy after a meaty meal. In fact, your smart cells sweat working overtime to digest the meat and use up all the carbohydrates and energy in knocking the meat into digestible shape!

Meat also has neither fibre nor healthy plant protein. The animal protein is tough on your smart cells and turns toxic - turning your insides into mess. You get indigestion, bad breath and a heartburn. The toxies load your immune system and also make you vulnerable to disease.

Lastly, look at your mental make-up. When you see a plump goat, do you get that predatory gleam in your eye that makes you stalk and kill it? Or if you've been to a sea-food restaurant which has a large aquarium filled with lobsters and are asked to pick one for dinner haven't you turned away with a shudder? Okay, you might argue, I don't see the live specimen when eating it.

Of course, you don't. You are just habituated to eating meat. But, remember, ultimately meat is dangerous to your health. However, if you just cannot do without it, we suggest avoid red meats - beef, mutton, pork ­completely and go in for lean meats like chicken and fish. This way, at least you won't be overstraining your smart cells while satisfying your taste buds. Even a chicken cooked in an oil-less masata, however, has enough fat to knock your smart cells out and make you put on weight. So, cook your chicken curry the previous day and refrigerate it overnight. The next afternoon skim the thick layer of fat that has coagulated on the surface and then heat it before serving. This way, at least, you are reducing your fat intake, Along with your non-vegetarian dish, pile your plate with plenty of vegetables so that you don't gorge on the meat alone. And eggs? They are not really worth including in your meals, for the yolk is high in fat and cholesterol. Which means that eating only the white albumen is best. In any case, they contain enough sulphur to strain your liver and kidneys. So, if you must. have an egg only once in ten days so as not to 'eggsasperate' your taste buds!

The culture of eating cooked food is deeply embedded in us. But we cannot ignore the value of raw foods. Make sure that your thali has plenty of salad and sprouts. For, the time taken by the body to digest and absorb various foods is:

13 hours - raw vegetables, fruits, sprouts.
24 hours - cooked vegetables and pulses.
72 hours - non-vegetarian and fried food.

Next to the - raw state, steaming is best. So is boiling, provided you do it in minimum water. Deep frying damages the nutritional value and the oil makes you fat, and raises your blood cholestrol level. Finally, the word 'vegetable' springs from the Latin vegetus meaning fresh and full of life. It is what our scriptures called pranic shakti, meaning life-force, depicting strength and energy.

Do ring in these little changes in your lifestyle. The two Es - exercising and eating right - will promote more than health. When you feel good about yourself, you will enjoy healthy relationships, a richer life. Fine-tune your mental dynamics. Don't ask yourself "Will this taste good?" Rather, ask, 'Will this taste good to my body's smart cells?" In that question lies your best answer.

Why should we developers bother to Comment our Code!!

Commenting your code is like cleaning your bathroom—you never want to do it, but it really does create a more pleasant experience for you and your guests. Because I’ve recently adopted a new mantra to use comments frequently and appropriately in my code, I spent a few hours researching the literature on commenting readability, reusability, and guidelines.

It was a bit overwhelming to see the sheer amount of information and discussions on the topic. Basically, there are a lot of tough, but fundamental questions out there like what does “frequently and appropriately” mean? Because there are a million different beliefs and contradictions on the subject, I created this brief overview to present my findings.

Types of Comments

Code Commenting - This refers to writing descriptive variable names that are self
explanatory. This is a minimalist form of commenting.

Inline Commenting - Specifically, these types of comments come at the end of a line of code, but we can also use this term to refer to comments inside of a function as well.

Function Commenting - This type of commenting is found on the lines above a function, and reveals all of the necessary details about that function. This includes parameters, return values, and any logic quirks or decisions that were made.

Class / Page Commenting - Comments that refer to an entire page or top level object fall into this category. Usually these comments include a broad overview, last edit date, associated files, author, and contact information. Additionally, this may include a general footer at the bottom of every page. Kevin wrote some great templates for building these types of comments in his feature on using XHTML templates.

Here are the most powerful links which should make you appreciate the usefullness of commenting in codes.

Art of Programming

The Art of Code Documentation

10 Essential Development practices

Using Comments Effectively

Hows and whys of commenting

Comments Are More Important Than Code

Do I Need Design Patterns in Software Development?

Software professionals may be familiar with the term "Design Patterns," but many have no idea of where they come from and what they truly are. Consequently, some do not see the value and benefits design patterns bring to the software development process, especially in the areas of maintenance and code reuse. This article will bridge this gap by defining design patterns from a historical perspective. It will also summarize the salient features of a typical design pattern and arrive at a working definition so that you will know what they are and what to expect when you incorporate them into your designs. Finally, it will explicitly summarize the benefits design patterns bring to software development and why you should incorporate them into your work. Subsequent articles will present more detailed descriptions of some of the more common design patterns, and how they can be applied to software development on the .NET platform.

What Are Design Patterns and Where Do They Come From?

Design patterns are commonly defined as time-tested solutions to recurring design problems. The term refers to both the description of a solution that you can read, and an instance of that solution as used to solve a particular problem. (I like the analogy of comparing design patterns to a class and an object instance of the class. Each is a different way to represent a thing.) Design patterns have their roots in the work of Christopher Alexander, a civil engineer who wrote about his experience in solving design issues as they related to buildings and towns. It occurred to Alexander that certain design constructs, when used time and time again, lead to the desired effect. He documented and published the wisdom and experience he gained so that others could benefit. About 15 years ago, software professionals began to incorporate Alexander's principles into the creation of early design pattern documentation as a guide to novice developers. This early work led others to also write about design patterns and culminated in the publication of Design Patterns: Elements of Reusable Object-Oriented Software in 1995 by Eric Gamma, Richard Helm, Ralph Johnson, and John Vlissides. This book is considered to be the "coming out" of design patterns to the software community at large and has been influential in the evolution of design patterns since. Design Patterns described 23 patterns that were based on the experience of the authors at that time. These patterns were selected because they represented solutions to common problems in software development. Many more patterns have been documented and cataloged since the publishing of Design Patterns. However, these 23 are probably the best known and certainly the most popular.

Design patterns are represented as relationships between classes and objects with defined responsibilities that act in concert to carry out the solution. To illustrate a design pattern, consider the Adapter pattern, one of the original 23 patterns described in Design Patterns. Adapter provides a solution to the scenario in which a client and server need to interact with one another, but cannot because their interfaces are incompatible. To implement an Adapter, you create a custom class that honors the interface provided by the server and defines the server operations in terms the client expects. This is a much better solution than altering the client to match the interface of the server.

The design pattern community is growing both in membership and coverage. The pattern literature describes new patterns that solve emerging issues related to technical advancements. As a software professional, you are the beneficiary of this body of knowledge. To use these patterns, you will need to learn them and become familiar with them so you will know which pattern to pull from your toolbox when a design issue arises. Many patterns have been documented over the years. They have been classified in different ways by different authors. Take the time to learn different ways to classify design patterns because you will gain greater insight into them. As you learn more and more patterns, it would be a good idea to develop your own classification system; one reflecting the way you utilize them.

Benefits of Design Patterns

Design patterns have two major benefits. First, they provide you with a way to solve issues related to software development using a proven solution. The solution facilitates the development of highly cohesive modules with minimal coupling. They isolate the variability that may exist in the system requirements, making the overall system easier to understand and maintain. Second, design patterns make communication between designers more efficient. Software professionals can immediately picture the high-level design in their heads when they refer the name of the pattern used to solve a particular issue when discussing system design.

Here are some important links that can head start your knowledge on Design Patterns in Software Development:

CodeProject: Implementing design patterns in C#. Free source code and programming help

CodeProject: Design Your Soccer Engine, and Learn How To Apply Design Patterns (Observer, Decorator, Strategy and Builder Patterns) - Part I and II. Free source code and programming help

amazedsaint blogs: articles on .net, architecture, c#, design patterns, dsl and more

CodeProject: Design Patterns Implementation in a Storage Explorer Application. Free source code and programming help

101 Design Patterns & Tips for Developers

Bad Code Can Be Written In Any Language

There are at least 5 types of BadCode:
1. Code that does not do what it is intended to do.
2. Code that does not perform as fast as it could.
3. Code that is not easily understandable.
4. Code that does not pick the Right Tool for the Job.

Where is it specified what the code is intended to do?
1. Code with explicitly Intention Revealing Names. This requires a high level of abstraction to be useful.
2. In Unit Tests.
3. In Acceptance Tests.
4. In my head. This is not a Good Thing.
5. In a functional specification. This is not a Good Thing either, if there are no corresponding Unit Tests or Acceptance Tests.

Bad Code Can Be Written In Any Language because we code in our heads, not in our machines – Programming Is in the Mind. Therefore, bad code is caused by bad thinking, and we can think badly regardless of our tools or the means of expression. No language can actually guide the programmer's thinking to the point that only good code can be produced. At best, such a language would lose most of its generality. More likely, restricting the language to eliminate bad code is probably equivalent to (or perhaps even more difficult than) proving a program's correctness. Perhaps it is even equivalent to solving the Halting Problem. I don't know. I certainly do not expect any such capability to ever be available.

There are so many such good resources from developer/coder point of view in the following link, do have a look.

A chat with GOD

God Pinged Me ……..

This is an excellent conversation between 'God' and the unknown 'Me'.
I don't know as to who has been the composer of such an interesting and captivating conversation ; but has he/she has definitely put in good amount of thought into crafting the same.

Those who don't believe in God, may also find it worth reading once. Enjoy and Think !

God: Hello. Did you call me?
Me: Called you? No.. Who is this?

God: This is GOD. I heard your prayers. So I thought I will chat.
Me: I do pray. Just makes me feel good. I am actually busy now. I am in the midst of something.
God: What are you busy at? Ants are busy too.
Me: Don't know. But I can't find free time. Life has become hectic. It's rush hour all the time.
God: Sure. Activity gets you busy. But productivity gets you results.
Activity consumes time. Productivity frees it.
Me: I understand. But I still can't figure out. By the way, I was not expecting YOU to buzz me on instant messaging chat.
God: Well I wanted to resolve your fight for time, by giving you some clarity. In this net era, I wanted to reach you through the medium you are comfortable with.

Me: Tell me, why has life become complicated now?
God: Stop analyzing life. Just live it. Analysis is what makes it complicated.

Me: why are we then constantly unhappy?
God: Your today is the tomorrow that you worried about yesterday. You are worrying because you are analyzing. Worrying has become your habit. That's why you are not happy.

Me: But how can we not worry when there is so much uncertainty?
God: Uncertainty is inevitable, but worrying is optional.

Me: But then, there is so much pain due to uncertainty.
God: Pain is inevitable able, but suffering is optional.

Me: If suffering is optional, why do good people always suffer?
God: Diamond cannot be polished without friction. Gold cannot be purified without fire. Good people go through trials, but don't suffer. With that experience their life become better not bitter.

Me: You mean to say such experience is useful?
God: Yes. In every term, Experience is a hard teacher. She gives the test first and the lessons afterwards.

Me: But still, why should we go through such tests? Why can't we be free from problems?
God: Problems are Purposeful Roadblocks Offering Beneficial Lessons (to) Enhance Mental Strength. Inner strength comes from struggle and endurance, not when you are free from problems.

Me: Frankly in the midst of so many problems, we don't know where we are heading..
God: If you look outside you will not know where you are heading. Look inside. Looking outside, you dream. Looking inside, you awaken. Eyes provide sight. Heart provides insight.

Me: Sometimes not succeeding fast seems to hurt more than moving in the right direction. What should I do?
God: Success is a measure as decided by others. Satisfaction is a measure as decided by you. Knowing the road ahead is more satisfying than knowing you road ahead. You work with the compass. Let others work with the clock.

Me: In tough times, how do you stay motivated?
God: Always look at how far you have come rather than how far you have to go. Always count your blessing, not what you are missing.

Me: What surprises you about people?
God: When they suffer they ask, "why me?" When they prosper, they never ask "Why me". Everyone wishes to have truth on their side, but few want to be on the side of the truth.

Me: Sometimes I ask, who am I, why am I here. I can't get the answer.
God: Seek not to find who you are, but to determine who you want to be. Stop looking for a purpose as to why you are here. Create it. Life is not merely a process of discovery but a process of co-creation. You are my co-creator.

Me: How can I get the best out of life?
God: Face your past without regret. Handle your present with confidence.
Prepare for the future without fear.

Me: One last question. Sometimes I feel my prayers are not answered.
God: There are no unanswered prayers. At times the answer is NO.

Me: Thank you for this wonderful chat.
God: Well. Keep the faith and drop the fear. Don't believe your doubts and doubt your beliefs. Life is a mystery to solve not a problem to resolve. Trust me. Life is wonderful if you know how to live. "Life is not measured by the number of breaths we take but by the moments that took our breath away!

Procedure to script your data (to generate INSERT statements from the existing data)

This procedure generates INSERT statements using existing data from the given tables and views. Later, you can use these INSERT statements to generate the data. It's very useful when you have to ship or package a database application. This procedure also comes in handy when you have to send sample data to your vendor or technical support provider for troubleshooting purposes.


  • Data from both tables and views can be scripted
  • No CURSORs are used
  • Table names and column names with spaces are handled
  • All datatypes are handled except images, large text and binary columns with more than 4 bytes
  • NULLs are gracefully handled
  • Timestamp columns are handled
  • Identity columns are handled
  • Very flexible and configurable
  • Non-dbo owned tables are handled
  • Computed columns are handled
  • You can filter the rows for which you want to generate INSERTs

Here is the link:

Difference between using SET and SELECT when assigning variables in T-SQL

I came across a great article by Narayana Vyas Kondreddi that describes the difference between the SET and SELECT .

The article is well worth the read, but here are the main points:
  1. SET is the ANSI standard for variable assignment, SELECT is not.
    SET can only assign one variable at a time, SELECT can make multiple assignments at once.
  2. If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)
  3. When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from it's previous value)
  4. As far as speed differences - there are no direct differences between SET and SELECT. However SELECT's ability to make multiple assignments in one shot does give it a slight speed advantage over SET.

Take a look at the complete article to see Vyas' complete tests to get the entire picture: []