Browse by Tags

All Tags » T-sql (RSS)
13 May 2008 10:59

Simulating undocumented Procedures

Sometimes you may want to run a query against each database Suppose you want to find all dabase names where particular table exists. You can use undocumented procedure EXEC sp_msforeachdb 'SELECT table_catalog FROM ?.INFORMATION_SCHEMA.TABLES where...
Posted by Madhivanan | with no comments
Filed under: ,
03 May 2008 14:12

Object Catalog Views in SQL Server 2005

In addition to INFORMATION_SCHEMA VIEWS, in SQL Server 2005, we can use Object Catalog Views to know more informations about the objects Refer this http://msdn.microsoft.com/en-us/library/ms189783.aspx
Posted by Madhivanan | with no comments
Filed under: ,
04 April 2008 14:45

Populating sample data

Sometimes you may need some sample data for testing purpose The following may help you in generating some sample data of different datatypes select abs ( checksum ( newid ()))% 10000 as intcol , abs ( checksum ( newid ()))* rand ()/ 100 as float_col ...
Posted by Madhivanan | with no comments
Filed under: ,
03 April 2008 14:45

Union Vs Union All

Well. Most of you know the diffrence between the two. http://blog.sqlauthority.com/2007/03/10/sql-server-union-vs-union-all-which-is-better-for-performance/ http://www.codethinked.com/post/2007/11/UNION-versus-UNION-ALL2c-lessons-in-minutiae.aspx Here...
Posted by Madhivanan | with no comments
Filed under: , ,
15 March 2008 10:57

Splitting csv to columns

Suppose you have string in csv format and want to split into seperate columns. You can use parsename function as long as you have maximum four values. The following would work for any number of values declare @s varchar ( 2000 ), @data varchar ( 2000...
Posted by Madhivanan | 3 comment(s)
Filed under: , , ,
19 February 2008 12:49

Understanding Single quotes

This is for newbies who struggle to understand how single quotes work in SQL Server I have seen newbies worrying why the following doesnt work SELECT columns from mytable where col = 'Lifco' s ' When you specify a value which has single quote , you need...
Posted by Madhivanan | 2 comment(s)
Filed under: , ,
17 January 2008 14:37

Forcing integrity between tables and procedures

You can force integerity between tables and views with schemabinding when you create views so that tables cant be dropped until views that bind on tables are dropped. Unfortunately it is not possible to force this integrity between tables and procedures...
Posted by Madhivanan | 3 comment(s)
Filed under: , ,
16 January 2008 10:51

Beware of Implicit conversions

Sometimes you may wonder why integer divisions are not giving exact result Consider this example Select 3/4 Select 4/3 Because both numerator and denominators are INTegers, results 0.75, 1.3333 are truncated to integers resulting 0 and 1 respectively...
Posted by Madhivanan | 2 comment(s)
Filed under: , ,
29 December 2007 07:05

Remove duplicate characters from a string

Pinal Dave in his weblog posted about Remove Duplicate Chars From String Here is its alternate method with Number table approach create procedure remove_duplicate_characters ( @string varchar ( 100 )) as Declare @result varchar ( 100 ) set @result = ...
Posted by Madhivanan | with no comments
Filed under: , ,
21 December 2007 09:32

Count number of words in a string

Tony Rogerson in his weblog posted about Counting the number of words in a string Here are two more methods 1 Using the method I posted already about Squeeze Function to remove multiple spaces Declare @s varchar ( 100 ) set @s = ' See how many words this...
Posted by Madhivanan | 1 comment(s)
Filed under: , ,
18 December 2007 14:48

Extract only numbers from a String

Sometimes we may need to extract only numbers from a string. Usually I see people using while loop to check each and every character to see if it is a number and extract it Here is a different appraoch Declare @s varchar ( 100 ), @result varchar ( 100...
Posted by Madhivanan | 4 comment(s)
Filed under: , ,