SQL SERVER – Find any expression in the database

My colleagues asked me hundred times, how to find a particular columns, table name, stored procedure, views in the database.

Always forgot to remember one query. So for them only ūüôā

Below query you can any table, stored procedure or views who ever have that expression as a column or object name.

SELECT * FROM SYSOBJECTS WHERE ID IN (SELECT ID FROM SYSCOMMENTS WHERE TEXT LIKE ‘%PRODUC%’)

The query produced all the object names. where ever it finds expression like ‘PRODUC

If you wanted to find any table name only then

SELECT * FROM SYSOBJECTS WHERE NAME LIKE ‘%TA%’ and xtype=‘u’

result

For stored procedure¬† pass¬† xtype=‘p’ and for views xtype=‘v’

Hope next time my friends will visit from my blog to get this query ūüėČ

Rana

CLR data type ,hierarchyid

The heirarchyid is a system data type available in SQL Server 2008 to represent hierarchies. It is based on a CLR data type, but is always available, whether the CLR is enabled or not.

The hierarchyid data type is a variable length, system data type. Use hierarchyid to represent position in a hierarchy. A column of type hierarchyid does not automatically represent a tree. It is up to the application to generate and assign hierarchyid values in such a way that the desired relationship between rows is reflected in the values.

  • /
  • /1/
  • /0.3.-7/
  • /1/3/
  • /0.1/0.2/
  • for more detail

    Avoiding five Most Common SEO Mistakes

    I know this is not a good impact to copy and paste in your blog. But this was a wonderful article and I just copied and paste here to share with my all fellows/reader. My motive is to share the things. 

    Thanks to SitePoint.

    Since X, Y, and Z are so common, and the solutions to the problems are often the same, I decided to summarize the most common SEO mistakes, and their solutions, in this article. These SEO mistakes are:

    1. using a non-standard title element
    2. failing to optimize image markup
    3. using spider-illegible JavaScript where legible code would do
    4. failing to indicate the content hierarchy using header elements
    5. poor grammar and spelling

    Let’s look at each of these problems in turn.

    Using a Non-standard Title Element

    Fernando Freire B√°ez wrote an excellent article a couple of years ago about the different places in which the title element is displayed to the user, and it is still just as relevant today as it was then.

    First, let’s define what we mean by a standard title element. Once that concept is clear, serious deviations from the standard will be obvious, and you’ll find it easy to correct errors.

    A standard title element should:

    1. include one or two of a web site’s keywords
    2. include a reference to the page’s fundamental content within the first three to five words (It’s okay to include variations on one of the web site’s keywords.)

    The logic behind this definition is that spiders only really consider approximately the first 60 characters of a title element. Additionally, they’re adept at identifying cases of keyword stuffing, which we’ll look at later, and which should be avoided.

    Let’s consider some examples. For a company that manufactures baby rattles, a standard title element might look like this:

    <title>Baby Rattles by Shake It Up Baby Inc.</title>

    It’s simple. The keywords are contained within the first two words of the title. There’s a variation on the “rattles” keywords in the company’s name. In total, including spaces, there are 38 characters.

    By contrast, many web sites use non-standard title elements that look more like this:

    <title>Baby Rattles, Baby Toys, Rattles for Babies, Baby Shaker, Baby Rattle by Rattle Your Cage Inc. </title>

    There are four problems with this title. Can you spot them? The title uses obvious keyword stuffing — a practice whereby a large number of keywords are included in the title in the hope that the site will be picked up for all of those keywords. In fact, this practice could cause the search engines to apply penalties to the site, which could see the site attain a much lower ranking on the search engine results page, or worse: the site could be banned from the search engine, and drop off the results pages altogether.

    Adding so many keywords to the title is a waste of energy, since the spiders read only the first 60 characters or so.

    The company’s name, which appears at the end of the title, is probably being given little or no consideration by the spiders, which makes it difficult for people to find the site if they perform a search using the company’s name.

    Finally, it looks unprofessional and spammy. Who would trust a web site that resorts to such questionable business practices?

    The screenshot below shows a few non-standard title elements that were returned on the second page of Google’s Search Engine Results Pages (SERPs) for Yamaha keyboards. Note the relative positions of Yamaha Canada and of Yamaha Europe. They’re both equally relevant to a search for Yamaha keyboards, but one uses “Home” as a title element.


    Using correct title elements can make all the difference in your search engine ranking.

    Failing to Optimize Image Markup

    This is definitely one of my pet peeves with webmasters today. So many talented PhotoShop gurus create wonderful banners, background art, and other images … then add them to their pages in a way that means spiders will ignore them!

    An example of some non-optimized HTML for a banner image looks something like this:

    <img src"mylogo.jpg" alt="banner">

    I’ve even seen examples that look like this:

    <img src="mylogo.jpg" alt="">

    In the second case, failing to include any information is, at the very least, a wasted opportunity, but will also have serious accessibility implications for those of your visitors who are visually impaired. But the first example presents a problem, too: it isn’t sufficiently specific about the image.

    For example, if the banner image contains the phrase “Baby Rattles ‘n’ Toys by SIUBNI” (the acronym for Shake It Up Baby Now, Inc.), then the alt attribute should also read “Baby Rattles ‘n’ Toys by SIUBNI.” Simple.

    The fact is that mylogo.jpg means nothing to a search engine spider. So we need to describe the image to a spider via the use of alt attributes.

    To describe a logo image, the company’s name and the word “logo” should suffice. For a composite or mosaic image composed of several pictures, I would write “Company name logo1,” “Company name logo2,” and so on, in the alt attributes for those images.

    Of course, people might choose to exploit this suggestion: an excessively large number of images could be used in order to create lots of alt attributes — with keyword stuffing the ultimate goal. Again, these are just foolish strategies that risk earning the site the dreaded search engine ban. Even without a ban, displaying dozens of images would have a serious impact on page loading times, and would likely cause traffic to leave before the site fully appeared.

    The following site gets it right — almost. Ignore the non-semantic markup for the time being; the main point is that the images aren’t optimized with alt attributes, though there is at least a line of text under each image to describe what’s being shown.


    alt attributes with poor descriptions prevent search engines — as well as disabled users — from “seeing” your page.

    In short, use the alt attribute to describe, with keywords, the image content. Hold the stuffing, please.

    Using Spider-illegible JavaScript where Legible Code would Do

    I recently saw a “deals” web site that aggregated hot deals from around the Internet. Because it pulls is content from a variety of sources, the web site regularly has fresh content, which search engines love. However, the problem was that the site’s aggregator used JavaScript.

    As a result, all that wonderful, frequently-updated content was going completely unnoticed by search engines. The image below shows this terrific web site, and the not-so-terrific coding behind it.

    There are plenty of content aggregators out there that perform the same function — pulling information from elsewhere on the Web, or elsewhere on the same site, and displaying it on the web site’s homepage — using PHP or some other server-side language. Performing the content aggregation on the server, rather than using JavaScript, will generate spider-legible HTML.

    It’s absolutely shocking how many webmasters use JavaScript to display the content they want search engines to index. Until recently, I was one of them — my blogs used JavaScript-generated blogrolls. I’ve since replaced them with clean HTML. After all, relevant external links are important to a web site, as Google engineer Matt Cutts has written.

    The point is quite simple: keep JavaScript use to the bare minimum, using it to enhance the user experience, perhaps, but not to display content that you want the search engines to see.

    Failing to Indicate the Content Hierarchy using Header Elements

    As most SitePoint readers know, header tags (h1, h2, etc.) have a clear purpose: they establish a web site’s important sections and the content hierarchy. Yet, along with title elements and image descriptions, correct heading hierarchies are among the most underutilized SEO tools.

    Even today, with microformats taking the concept of the semantic Web to a whole new level, the number of webmasters who continue to author pages with non-semantic markup is staggering. Even well-educated developers who use CSS to position and style elements on the page ignore the importance of a good hierarchy.

    Consider this directory. If you view the page’s markup, you won’t find a single heading element in the entire document! This is not an isolated case.

    According to WebAIM, the problem with hierarchy or header tags is that “they lack subtlety: in most web browsers they make headlines look absurdly large (<h1>, <h2>) or ridiculously small (<h4>, <h5>, <h6>).” That excuse is no longer valid today, though, as CSS allows developers to modify the display of hierarchy tags. Look at any Blogger.com blog that runs a standard template, and you’ll find the sidebar’s headers are <h2>s modified to appear an appropriate size.

    My excuse for committing this mistake is that I got my SEO blog’s template from someone else, and I lack the coding skills to edit it. However, I recognize that it’s a problem and have it on my SEO to-do list.

    According to Google’s common-sense webmaster guidelines (note the page’s standard title element), webmasters should “make a site with a clear hierarchy and text links.”

    To fix this mistake, first determine the key points your web site is trying to make (hint: your keywords ought to be included in this content). Then, use CSS to establish the display of the elements in your hierarchy. Finally, enclose the key points within header elements.

    Poor Grammar and Spelling

    The Google Librarian Center is Google’s greatest strategic move in years, yet the organization has largely downplayed the project’s importance. Through its open partnership with librarians, Google is gaining allies in the battle to develop algorithms.

    As Karen G. Schneider of the Librarians Internet Index wrote in an article for the Google Librarian Center’s newsletter, “authorship” is important. “At LII we’re very skeptical of web sites with more than a couple of typographical or grammatical errors. In addition to how poorly it would reflect on us to point someone to a grammatically challenged web site, it’s a big hint that the content on the site is generally not up to snuff.” Two newsletter issues later, it appears Google has hired their first librarian, whereas previously only engineers were seen as able to solve the problems of search…

    The moral of the story? Don’t forget to spell-check! Beyond mere spell-checking, though, buy one of the many grammar guides available (for less than $10 on eBay and the like) and make sure your syntax, punctuation, and other grammatical elements are in order.

    For the moment, webmasters can still get away with bad grammar, but in the long term this will change, so start improving your grammar and spelling now and give yourself a head-start on the competition.

    In the meantime, of course, poor grammar precludes your site from gaining customers’ trust, and if there’s no trust, there’s no purchase. Investing in good grammar is really an investment in gaining customers’ trust. From personal experience, I recommend general language wiz and copywriter Bill Hilton, who I met, incidentally, here at the SitePoint Forums.

    Summary

    To conclude, there are five very common SEO mistakes in today’s web design community:

    1. using a non-standard title element
    2. failing to optimize image markup
    3. using spider-illegible JavaScript where legible code would do
    4. failing to indicate the content hierarchy using header elements
    5. poor grammar and spelling

    Luckily, these problems are easy enough to resolve, but, having read this article, you should be in position to avoid them altogether.

    Tip of the day – 19 Feb 2008

    For Microsoft Office Excel 2007
    Identify Duplicate Values in an Excel Table
    In Microsoft Office Excel 2007, you can easily highlight duplicate values with conditional formatting.
    1. Select the list in which you want to identify duplicates.
    2. Click the Home tab on the Ribbon.
    3. In the Styles section, click Conditional Formatting, point to Highlight Cells Rules, and then click Duplicate Values. Pairs of duplicates will then be highlighted, and you can choose which of each pair to delete.

    Tip of the day 16 Feb 2008,Find the Correct Format for Excel Formulas

    Find the Correct Format for Excel Formulas
    You can use Microsoft IntelliSense technology in Microsoft Office Excel 2007 to create formulas. If you want to type a formula in Excel 2007, start by typing the first few characters of the formula (for example, an equal sign and then a letter) to view a list of available formulas. Excel 2007 makes it easy to see all the available types of formulas; the enhanced tooltips in the 2007 Office system provide a detailed explanation of each formula.

    Fulltext Searching,Fulltext Indexing, Installing Microsoft Search Service

    I received some emails from several groups, people were asking about the fulltext search, so I decided to write about fulltext indexing. I had consolidated a lot of information in one article and these includes Installation of fulltext,MSSearch Services and Noisy words which should be eliminated before searching.

    I hope this would help my reader and you are always free to ask me for further questions.

    Full Text Searching is a free,optional component of MS SQL 2000. When installed, it offers a vast array of additional string querying abilities. Full Text Searching allows for string comparisons similar to internet search engines, returning both results and a matching score or weight. With regular TSQL, string matching is usually limited to an exact match, or a wildcard match with the keyword “LIKE.” Full Text Searching exceeds this by searching for phrases, groups of words, words near one another, or different tenses of words, such as run, running, and ran.

    Full Text Searching is accomplished by installing a new service (Microsoft Search), and using key words in TSQL designed specifically for text searching.

    Some steps to install these services.

    Microsoft Search

    Unlike other SQL operations, Full Text Searching is not managed by the MSSQL Server service. Instead, a new service, Microsoft Search, must be installed and started. A primary reason for this is that Full Text Searching is done against a special index contained in a Catalog.
    Catalogs that are used to support searching are not part of the regular SQL server installation; they are kept outside the MDF, and are stored in separate physical files.
    The Microsoft Search service has two primary jobs, indexing support and querying support.
    Indexing support includes the tasks of defining the Catalogs, and indexes they contain, creating them and keeping the data up to date as changes take place. When search queries are issued, the second job of the Microsoft Search service begins, determining which Catalog index meets the query requirements. Once determined, Microsoft Search returns the identity of selected rows, along with a ranking value if requested, back to the SQL Server service for query completion.
    This brings up two requirements that the Microsoft Search service imposes. First, there must be a unique index on the base table to which the Catalog refers. This is required because the Search service only reports the identity of the selected rows back to the SQL service, not the record set itself. Second, Full Text Searching is only supported on Windows Server. Either NT 4 or 2000 Server can be used, but not Workstation, Windows 98, or XP. These other clients can issue quires, but not host the Search service.

    Installing Search

    Check in the services list for “Microsoft Search.” If not listed, insert the SQL Server media, and select “Upgrade, Add or Remove Components.” Click next for “Add Components.” On the next screen, select “Server Components,” Full-Text Search, and then continue with the rest of installation.

    Creating a Catalog

    In this first example, we will be creating a Full Text Search on the pubs database. To begin, open Enterprise Manager and highlight the local server.

    In Sql Server 2005 the steps are little different but concept is same.

    1.¬† From the top menu, select Tools, Full Text Indexing.2.¬† Click next on the opening screen.3.¬† On the “Select a Database” screen, choose pubs.4.¬† For the next screen, select “titles” as the table and click next.5.¬† Keep the default unique index; there is only one on the pubs table.6.¬† In the next screen, select Title and Notes as the fields we would like indexed.

    7.¬† On the “Select a Catalog” screen, because this is the first Catalog on the server, we are prompted for a name. In this example, “demo” is used as the Catalog name.

    8.¬† This next screen “Populate Catalogs,” leave blank and click next.

    9.  Click Finish, and the following success screen should appear:

    10.¬† From Enterprise Manager, expand Full-Text Catalogs, the “demo” catalog should appear in the right window.
    11.¬† Right click and select “Start Full Population.”12.¬† The catalog is now complete.13.¬† To verify in creation, open Query Analyzer and select the pubs database.14.¬† Execute sp_help_fulltext_catalogs, the new Catalog should appear.
    15.  Issue a test query of:

    SELECT title, notes FROM titles WHERE CONTAINS (notes, ' "quick easy" ')

    This should return one record with a title of “Silicon Valley Gastronomic Treats.”

    One of the first items to notice about Full Text Searching is that it is not automatic. Even after Microsoft Search is installed, it still must be configured for each database, table, and field we want to search. If a field will need to be searched, it must first be included in a Catalog. Steps 1 through 4 and 6 accomplished this.

    Step 5 demonstrates one the Full Text Search requirements, that each table made available for searching contains a unique index. When a search query is issued, Microsoft Search will determine which records meet the query, and will report their unique index key back to the MSSQL Server service for completion of the query.

    The result of Step 7, “Select a Catalog,” is the creation of an entire file structure to support the Catalog. To find the name of structure from Enterprise Manager, expand Full-Text Search, then right click the “demo” Catalog and select properties. By default, Catalogs will be saved under the default server directory, MSSQL, FTDATA.

    For the “demo” Catalog, a new directory structure named “SQL00000500005” as shown in the “Physical catalog” field, has been created. This structure will store the indexes and data files needed by the Search service. They are not text readable. The “Item count” field shows the sum of the tables and rows indexed.

    Conclusion

    Full Text Searching brings advanced string querying support to SQL. In this first article, a new Catalog containing an index to search on two fields of the pubs database has been created. Future articles will expand this Catalog to include searching for words near each other, returning the rank or weight of the search, and indexing Microsoft Office documents. A number of housekeeping tasks will also be introduced, such as keeping the Catalog up to date when data on the base table changes, and determining whether a word should be indexed or not (Noise words). Although Full Text Searching is far from automatic, the results it provides make it worth the effort.

    Here are few Noise Words which should exclude from the search.

    “a”, “a’s”, “able”, “about”, “above”, “according”, “accordingly”, “across”, “actually”, “after”, “afterwards”, “again”, “against”, “ain’t”, “all”, “allow”, “allows”, “almost”, “alone”, “along”, “already”, “also”, “although”, “always”, “am”, “among”, “amongst”, “an”, “and”, “another”, “any”, “anybody”, “anyhow”, “anyone”, “anything”, “anyway”, “anyways”, “anywhere”, “apart”, “appear”, “appreciate”, “appropriate”, “are”, “aren’t”, “around”, “as”, “aside”, “ask”, “asking”, “associated”, “at”, “available”, “away”, “awfully”, “b”, “be”, “became”, “because”, “become”, “becomes”, “becoming”, “been”, “before”, “beforehand”, “behind”, “being”, “believe”, “below”, “beside”, “besides”, “best”, “better”, “between”, “beyond”, “both”, “brief”, “but”, “by”, “c”, “c’mon”, “c’s”, “came”, “can”, “can’t”, “cannot”, “cant”, “cause”, “causes”, “certain”, “certainly”, “changes”, “clearly”, “co”, “com”, “come”, “comes”, “concerning”, “consequently”, “consider”, “considering”, “contain”, “containing”, “contains”, “corresponding”, “could”, “couldn’t”, “course”, “currently”, “d”, “definitely”, “described”, “despite”, “did”, “didn’t”, “different”, “do”, “does”, “doesn’t”, “doing”, “don’t”, “done”, “down”, “downwards”, “during”, “e”, “each”, “edu”, “eg”, “eight”, “either”, “else”, “elsewhere”, “enough”, “entirely”, “especially”, “et”, “etc”, “even”, “ever”, “every”, “everybody”, “everyone”, “everything”, “everywhere”, “ex”, “exactly”, “example”, “except”, “f”, “far”, “few”, “fifth”, “first”, “five”, “followed”, “following”, “follows”, “for”, “former”, “formerly”, “forth”, “four”, “from”, “further”, “furthermore”, “g”, “get”, “gets”, “getting”, “given”, “gives”, “go”, “goes”, “going”, “gone”, “got”, “gotten”, “greetings”, “h”, “had”, “hadn’t”, “happens”, “hardly”, “has”, “hasn’t”, “have”, “haven’t”, “having”, “he”, “he’s”, “hello”, “help”, “hence”, “her”, “here”, “here’s”, “hereafter”, “hereby”, “herein”, “hereupon”, “hers”, “herself”, “hi”, “him”, “himself”, “his”, “hither”, “hopefully”, “how”, “howbeit”, “however”, “i”, “i’d”, “i’ll”, “i’m”, “i’ve”, “ie”, “if”, “ignored”, “immediate”, “in”, “inasmuch”, “inc”, “indeed”, “indicate”, “indicated”, “indicates”, “inner”, “insofar”, “instead”, “into”, “inward”, “is”, “isn’t”, “it”, “it’d”, “it’ll”, “it’s”, “its”, “itself”, “j”, “just”, “k”, “keep”, “keeps”, “kept”, “know”, “knows”, “known”, “l”, “last”, “lately”, “later”, “latter”, “latterly”, “least”, “less”, “lest”, “let”, “let’s”, “like”, “liked”, “likely”, “little”, “look”, “looking”, “looks”, “ltd”, “m”, “mainly”, “many”, “may”, “maybe”, “me”, “mean”, “meanwhile”, “merely”, “might”, “more”, “moreover”, “most”, “mostly”, “much”, “must”, “my”, “myself”, “n”, “name”, “namely”, “nd”, “near”, “nearly”, “necessary”, “need”, “needs”, “neither”, “never”, “nevertheless”, “new”, “next”, “nine”, “no”, “nobody”, “non”, “none”, “noone”, “nor”, “normally”, “not”, “nothing”, “novel”, “now”, “nowhere”, “o”, “obviously”, “of”, “off”, “often”, “oh”, “ok”, “okay”, “old”, “on”, “once”, “one”, “ones”, “only”, “onto”, “or”, “other”, “others”, “otherwise”, “ought”, “our”, “ours”, “ourselves”, “out”, “outside”, “over”, “overall”, “own”, “p”, “particular”, “particularly”, “per”, “perhaps”, “placed”, “please”, “plus”, “possible”, “presumably”, “probably”, “provides”, “q”, “que”, “quite”, “qv”, “r”, “rather”, “rd”, “re”, “really”, “reasonably”, “regarding”, “regardless”, “regards”, “relatively”, “respectively”, “right”, “s”, “said”, “same”, “saw”, “say”, “saying”, “says”, “second”, “secondly”, “see”, “seeing”, “seem”, “seemed”, “seeming”, “seems”, “seen”, “self”, “selves”, “sensible”, “sent”, “serious”, “seriously”, “seven”, “several”, “shall”, “she”, “should”, “shouldn’t”, “since”, “six”, “so”, “some”, “somebody”, “somehow”, “someone”, “something”, “sometime”, “sometimes”, “somewhat”, “somewhere”, “soon”, “sorry”, “specified”, “specify”, “specifying”, “still”, “sub”, “such”, “sup”, “sure”, “t”, “t’s”, “take”, “taken”, “tell”, “tends”, “th”, “than”, “thank”, “thanks”, “thanx”, “that”, “that’s”, “thats”, “the”, “their”, “theirs”, “them”, “themselves”, “then”, “thence”, “there”, “there’s”, “thereafter”, “thereby”, “therefore”, “therein”, “theres”, “thereupon”, “these”, “they”, “they’d”, “they’ll”, “they’re”, “they’ve”, “think”, “third”, “this”, “thorough”, “thoroughly”, “those”, “though”, “three”, “through”, “throughout”, “thru”, “thus”, “to”, “together”, “too”, “took”, “toward”, “towards”, “tried”, “tries”, “truly”, “try”, “trying”, “twice”, “two”, “u”, “un”, “under”, “unfortunately”, “unless”, “unlikely”, “until”, “unto”, “up”, “upon”, “us”, “use”, “used”, “useful”, “uses”, “using”, “usually”, “v”, “value”, “various”, “very”, “via”, “viz”, “vs”, “w”, “want”, “wants”, “was”, “wasn’t”, “way”, “we”, “we’d”, “we’ll”, “we’re”, “we’ve”, “welcome”, “well”, “went”, “were”, “weren’t”, “what”, “what’s”, “whatever”, “when”, “whence”, “whenever”, “where”, “where’s”, “whereafter”, “whereas”, “whereby”, “wherein”, “whereupon”, “wherever”, “whether”, “which”, “while”, “whither”, “who”, “who’s”, “whoever”, “whole”, “whom”, “whose”, “why”, “will”, “willing”, “wish”, “with”, “within”, “without”, “won’t”, “wonder”, “would”, “would”, “wouldn’t”, “x”, “y”, “yes”, “yet”, “you”, “you’d”, “you’ll”, “you’re”, “you’ve”, “your”, “yours”, “yourself”, “yourselves”, “z”, “zero”.