Madhivanan

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 table_name=''your_table'''

while exists(Select * from sys.databases where database_id>@database_id)
Begin
        select @dbname=name,@database_id=database_id from sys.databases where database_id=@database_id
        EXEC('SELECT * FROM '+@dbname+'.INFORMATION_SCHEMA.TABLES where table_name='''+@table_name+'''') 
        select @database_id=min(database_id) from sys.databases where database_id>@database_id
End

declare @sql varchar(max), @table_name varchar(100)
select @sql='', @table_name='your_table'
select  @sql=@sql+ 'SELECT table_catalog FROM '+name+'.INFORMATION_SCHEMA.TABLES where table_name='''+@table_name+'''' from sys.databases
exec(@sql)

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

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,
        dateadd(day,0,abs(checksum(newid()))%100000) as date_col,
        substring(replace(cast(newid() as varchar(36)),'-',''),1,abs(checksum(newid()))%15) as varchar_col,
        abs(checksum(newid()))%2 as bit_col 
from 
        master..spt_values 
where 
        type='p' and 
        number between 1 and 200

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 is one of the differences that is not known by newbies

When you use UNION, you cant include any column of type TEXT and NTEXT. But it is possible if you use UNION ALL

Declare @test table(i int, text_col text)
insert into @test 

select 1,'some test string'union all
select 2,'some other test string'
 
--Error
select i,text_col from @test where i=1union 
select i,text_col from @test where i=1
--No Error
 select i,text_col from @test where i=1union all
select i,text_col from @test where i=1

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)

select @s='this,is,test'

select @data=''''+replace(@s,',',''',''')+''''

exec('select '+@data)

Different ways to know structure of a table

You can use one of the following to know the structure of a table

1 Generate SQL Script option from Enterprise Manager/Management Studio

2 select * from information_schema.columns where table_name='table_name'

3 EXEC sp_help 'table_name'

4 EXEC sp_columns 'table_name'

5 In Query Analyser type the name of the table, highlight it and press Alt+F1

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'

SELECT columns from mytable where col ='Lifco''s'

The following may be helpful (Run and see the result)

SELECT '','''','''''','''''''',''''''''''

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. Tables can be dropped though there are procedures referencing them

But if you use SQL Server 2005 or higher version, it is possible with DDL trigger which is one of finest features available

CREATE TABLE test_table(code int, names varchar(100))

GO
CREATE TRIGGER alert_me
ON DATABASE 
FOR DROP_TABLE 

AS
declare @table_name varchar(100)
select @table_name= right(ddl,charindex(' ',reverse(ddl))-1) from
(
select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(100)') as DDL
) as t 


If exists
(
select * from information_schema.routines
where routine_definition like '% '+@table_name+'%'
)

begin
Raiserror('One or more procedures depend on this table and cant be dropped' , 16, 1) 
Rollback
end

 
GO
 
create procedure test_sp
as
select * from test_table

GO
drop table test_table



Now you would get error as test_sp is referencing the table

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

Suppose you want to find out percentage number of orders taken for each shipcity against total orders

select shipcity,count(*)/(select count(*) from northwind..orders)*100  as percentage
from northwind..orders
group by shipcity


The result of percentage column is 0

To avoid this, you need to convert one of the openrands by float ,decimal or multiply by 1.0

Select 3*1.0/4

Select 4*1.0/3

select shipcity,count(*)*1.0/(select count(*) from northwind..orders)*100 as percentage
from northwind..orders
group by shipcity

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=''

select @result=@result+min(substring(@string ,number,1)) from 
( 
    select number from master..spt_values where type='p' and number between 1 and len(@string )
) as t 
group by substring(@string,number,1)
order by min(number)

select @result 
GO

EXEC remove_duplicate_characters 'aasssarrrty'
 
returns
 
asrty 

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      has '

Select len(string)-len(replace(string,' ',''))+1 as no_of_words from
(
    Select replace(replace(replace(ltrim(@s),' ',' ~!@#'),'~!@# ',''),'~!@#','') as string 
) as t 


2 Using Number table approach used to Extract only numbers from a String

Declare @s varchar(100),@result varchar(100)

set @s=' See       how many                        words this      has '
set @result=''
Select @s=replace(replace(replace(ltrim(@s),' ',' ~!@#'),'~!@# ',''),'~!@#','') 

select count(*)+1 as no_of_words from
( 
    select substring(@s,number,1) as number from 
    (  
        select number from master..spt_values where type='p' and number between 1 and len(@s) 
    ) as t
) as t 
where number='' 
 

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)
set @s='as4khd0939sdf78'

set @result=''

select 
         @result=@result+case when number like '[0-9]' then number else '' end 
from
         ( 
           select substring(@s,number,1) as number from 
             ( 
                  select number from master..spt_values where type='p' and number between 1 and len(@s)
             ) as t
         ) as t 

select @result as only_numbers

Result

4093978

Script out Procedures and Functions - Part 2

 
Part 1 uses information_Schema.routines view and this is based on sp_helptext
 
declare @sps table(texts varchar(8000))

insert into @sps
select 'sp_helptext '''+name+'''' from sysobjects where xtype in ('p','fn')
order by xtype,name

 
create table scripts(sps varchar(8000))
 

declare @texts varchar(1000)


select @texts=min(texts)from @sps
while @texts>''
Begin
EXEC('insert into scripts(sps) EXEC '+ @texts)
         insert into scripts(sps) select 'GO'
         select @texts=min(texts)from @sps where texts>@texts 
End

EXEC master..xp_cmdshell 'bcp "select * from dbname..scripts" queryout "c:\scripts.txt" -c' 

drop table scripts

Squeeze Function

The Squeeze function is used to remove the multiple occurences of spaces into one occurence. In SQL Server there is no function to do the same. I needed to write this in my application to remove unwanted spaces in the string.

Run the following and see the result

declare @t table(string_col varchar(100))

insert into @t
select 'a              b   c' union all 
select 'a                   b               c' union all 
select 'a       b                                        c' union all 
select 'a             b          c' union all 
select 'a   b    c' 

select 
         string_col, 
         replace(replace(replace(string_col,' ',' ~!#$^'),'~!#$^ ',''),'~!#$^','') as new_string_col 
from 
         @t

If you use front end application (VB,.NET,jave,etc), you can also simulate the same approach there

Column length and Data length

 
When you use character datatypes(char,varchar,nchar,etc),
always make sure that column has enough length to have data. 
Otherwise sometimes you would get errors and sometimes you wont.

Consider the following example

Declare @t table(i int, test_col varchar(2))
Insert into @t select 1,'test'
Select i,test_col from @t
 
Well. You get the error

Msg 8152, Level 16, State 14, Line 4
String or binary data would be truncated.
The statement has been terminated.


Because the column length of test_col is not enough to have the value 'test'
But only when you insert data to a table, you get this error. 
In other cases you dont

Declare @v varchar(2)
set @v='test'
select @v
Now there is no error but the value is truncated and 
only first two characters are assigned to the variable. 
 
The same case is applied when you use a stored procedure with input parameter

create procedure test(@test_param varchar(2))
as
Select @test_param
Go
EXEC test1 'test'
Go
drop procedure test

Now see what happens when you dont specify the length
Declare @v varchar
set @v='test'
select @v
By default the length is 1
Consider another example
Select cast('This has more than thirty characters' as varchar)
The result is This has more than thirty char
 
When you cast a literal to character type without specifying the length, 
by default it is 30. 
 
So you need to specify the enough column length when you use character datatypes