Madhivanan's TSQL Blog

Search Procedure name in all databases

Often I see people asking questions in the forums about finding the database name where the particular procedure exists
You can use many methods to get a solution (cursor, undocumented procedure,dynamic sql,etc)

The simplest are

1 Use Undocumented procedure

 
EXEC sp_msforeachdb 
'if exists(select ''?'' from ?.sys.objects where name=''your_procedure_name'')
select ''?'' from ?.sys.objects where name=''your_procedure_name'''

2 Use Dynamic SQL

 
declare @sql varchar(max)

set @sql= ''

select 
	@sql=@sql+' select '''+name+''' as database_name from '+name+'.sys.objects where name =''procedure_name'' union all' from sys.databases 

select @sql=substring(@sql,1,len(@sql)-9)

exec(@sql)

Note that method 1 is not recommedted as it uses undocumented stored procedure sp_msforeachdb
which may be removed from future release of the SQL Server.

Also refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx

How to disable all jobs using a query?

When one of posters asked a question about this, someone posted the following reply

UPDATE msdb.dbo.sysjobs SET enabled = 0 

As sysjobs is a system and updating it is generally not advised, the following can be used instead

declare @sql varchar(max)

set @sql=''
select 
        @sql=@sql+' EXEC msdb..sp_update_job @job_id='''+cast(job_id as varchar(100))+''',@enabled=0;' 
from 
        msdb..sysjobs 
where 
        enabled=1

exec(@sql)

Converting Hexadcimal to decimal

Pinal Dave in his post asked about the simple ways to convert hexadecimal values to decimal values.

Here are some methods:

87235 is an integer value whose hexadecimal is 0x000154C3.

In this case make use of Implicit conversion.

declare @hexa varbinary(10)
set @hexa=0x000154C3
select 
	@hexa as hexadecimal_value,
	@hexa*1 as integer_value

If the value is decimal, make use of CAST function

declare @hexa varbinary(10)
set @hexa=0x060100019F4F0D00
select 
	@hexa as hexadecimal_value,
	cast(@hexa as decimal(12,2)) as decimal_value

So for the safer side, it is always better to make use of CAST function

routine_definition from information_schema.routines

Some people suggest usage of information_schema.routines View to view the definition of the procedure The problem is the datatype of that column is nvarchar(4000) and there is a chance that the definition gets truncated. So, the following wont give you full text of the definition

select routine_definition from information_schema.routines
where routine_name='your_procedure'

The following methods will give the full definition

--Method 1
select object_definition(object_id('your_procedure'))

--Method 2
EXEC sp_helptext 'your_procedure'

Find alternate week day

My co-worker had a task of finding minimum alternate week day as of today for a given date and day. Consider these data

• Date : 2009-12-22
• day : 5 (it denotes Thursday)

It means the event was scheduled on 2009-12-22 which should run on every alternate Thursday starting from 2009-12-24 (The first Thursday on or after the scheduled date). The task is finding the minimum of current available day Here is my solution

declare @date datetime, @day int 
select @date='2009-12-22',@day=5

select 
        min(dateadd(day,14*(number),date)) 
from 
        (
                select dateadd(day,@day-datepart(weekday,@date),@date) as date
        ) as t,master..spt_values
where type='p' and dateadd(day,14*(number),date)>=getdate()

Replace data of one table with data of other table

One of the questions asked in a forum was about replacing data of one table by the data of 
another table

The solution that the questioner used was having cursor by looping through the source table and 
replace particular words by other words from another table by matching the words

Here is my alternate solution

declare @Datatable table(Field1 nvarchar(50))

insert into @Datatable (Field1)
select 'testing for this' union all
select 'testing for this to' 
 

declare @MatchTable table(LookFor nvarchar(50),Replacewith nvarchar(50))
insert into @MatchTable (LookFor,Replacewith)
select 'for','with' union all
select 'to','no' 

declare @replace varchar(8000),@with varchar(8000),@sql varchar(8000)

select @replace='',@with ='',@sql=''

select 
        @replace =@replace +'replace(',
        @with =@with +''''+Lookfor +''','''+Replacewith+'''),' 
from 
        @MatchTable 

select @replace=@replace +'''~!@#'',',@with =SUBSTRING(@with,1,len(@with)-1)

select @sql=@sql+REPLACE('select '+@replace+@with,'~!@#',Field1)+' union all ' from @Datatable 

select @sql=SUBSTRING(@sql,1,len(@sql)-10)

select * from @Datatable 

exec(@sql)

Happy Holidays

In the Query Analyser, set the Result mode to Text (Press CTRL+T) and run the following code

set nocount on 

select
        space(17-len(replicate(char(94),no)))+ replicate(char(94),no*2-1) 
from 
        (
                select top 10 row_number() over (order by name) as no from sysobjects 
        )
as t 
union all
select space(14)+replicate(char(124),5) 
union all
select space(10)+cast(0x486170707920486F6C6964617973 as varchar(100))

Search a value in character column of all tables

Sometimes, you may need to find a value in the characters columns (char, varchar, etc) in all 
the tables

This is one of the methods to do it

declare @sql varchar(max),@search varchar(100)

set @sql=''

set @search='your search string'

select 
        @sql=@sql+'select '''+c.table_name+''' as table_name,'''+c.column_name+''' as column_name,['+c.column_name+'] as column_value from '+c.table_name+' where ['+c.column_name+'] like N''%'+@search+'%'' union all ' 
from 
        information_schema.columns as c inner join information_schema.tables as t on c.table_name=t.table_name 
where 
        t.table_type='BASE TABLE' and data_type like '%char%' 

set @sql=left(@sql,len(@sql)-10)

exec(@sql) 

The resultset would have table_name, column_name and column_value 

Removing unwanted characters - Part 2

In my previous post about Removing unwanted characters , I posted a method that used a function

Here is another method that uses Dynamic SQL

declare @data table (data varchar(100))

-- table that has source data

insert @data 
select 'tes^@&t %stri)-n!g' data union all
select '))aaer***********)' union all
select '&^&&hsdf()' union all
select 'jj&wk' union all
select ')hw*pa' union all
select 'No&@#$53*24,Mai()$n R--!oad'

-- table that has unwanted characters

declare @clean_chars table (char_id int identity(1,1),chars char(1))

insert into @clean_chars (chars)
select '^' as repl union all 
select ')' union all
select '(' union all
select '&' union all
select '*' union all
select '%' union all
select '@' union all
select '-' union all
select ',' union all
select '$' union all
select '#' union all
select '!' 

--T-sql code that removes them

declare @replace varchar(8000),@chars varchar(8000),@sql varchar(8000)

select @chars='',@replace='',@sql=''

select @replace = @replace+'replace(', @chars = @chars+''''+chars+''',''''),' from @clean_chars 

set @replace=@replace+'''~!@#'','

set @replace=@replace+substring(@chars,1,LEN(@chars)-1)

select @sql=@sql+'select '''+data+''' as old_data, '+replace(@replace,'~!@#',data)+' as new_data union all ' from @data 
set @sql=substring(@sql ,1,LEN(@sql)-10)

exec(@sql) 

Parsing a string

Now-a-days lot of questions are asked in the forum about extracting particular text from a string

Here I give some examples on how to do it

--Example 1 (Extracting tags and other informations)

declare @s varchar(100)

declare @temp table(id int identity(1,1),data varchar(100))

set @s='567dataua
testwelcome'

select @s=replace(replace(@s,'<','~!@#'),'>','~!@#')

while charindex('~!@#',@s)>0   
begin
        insert into @temp(data)
        select substring(@s,1,charindex('~!@#',@s)-1)
        set @s=substring(@s,charindex('~!@#',@s)+4,len(@s))
end 

insert into @temp(data)
select @s

--Extract the tags
select data from @temp where id%2=0

--Extract the data available outside the tags
select data from @temp where id%2=1 
--Example 2 (Extract URLs)

declare @s varchar(1000)

declare @temp table(id int identity(1,1),data varchar(1000)) 
set @s='"ramkumar</li></a>hhhmahesh<a href="/www.gmail.com"></a>hhjjkjhsdf&lt; as href="test.test.com" mce_href="test.test.com"' 

while charindex('href=',@s)>0
begin
        insert into @temp(data)
        select substring(@s,charindex('href=',@s)+6,charindex('.com',@s)-charindex('href=',@s)-2)
        set @s=substring(@s,charindex(substring(@s,charindex('href=',@s)+6,charindex('.com',@s)-charindex('href=',@s)-                2),@s)+len(substring(@s,charindex('href=',@s)+6,charindex('.com',@s)-charindex('href=',@s)-2)),len(@s))
end select data from @temp 
--Example 3 (Extract phone numbers with the format ddd-ddd-dddd where d denotes a number)

select 
        data,
        substring(data,patindex('%[0-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]%',data),12) as ph_no
from
(
        select 'kashdk hasfjh sjkfhk h123-456-8765 kjsdkfjj h093 kljsdf903354512' as data union all
        select 'khasd ksfjh 345-245-9871kljk 90234 99jkxc 902304809n kljsdkfj9034' 
) as t
where 
        patindex('%[0-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]%',data)>0

--Example 4 (Extract amount starts with $)

select fruit,substring(amount,1,patindex('%[0-9][ ]%',amount+' ')) from
(
select 
        Fruit, 
        substring(fruit,charindex('$',fruit),len(fruit)) as amount
        from 
        (
                select '9 Lemons cost 67 $99.99 on sale' as fruit union all
                select '$5.99 Apples 877 are on sale' union all
                select 'Where are the $65.99 lemons 7856' union all
                select 'Oranges costs $99.5'
        )as Pricing
) as t

Also refer this
http://beyondrelational.com/blogs/madhivanan/archive/2009/10/12/removing-tags-from-a-string.aspx

Usage of Semicolons in SQL Server

As you know, semicolon is used as a statement terminator in almost all RDBMSs. However the usage 
of semicolon is optional in SQL Server except a few cases. Here are atleast the three cases 
where usage of semicolon is a must

1 At the start of the Common Table Expression (If any statement preceeds it)

2 If the procedure name is GO ,to execute it use at the end of GO (ex EXEC GO; as shown in Fun with GO)

3 At the end of Merge statement

SQL Server 2008 features that work only in Enterprise Edition

One of the new features available in SQL Server 2008 is compressed backup which takes backup of 
a database with mimimum possible size. The syntax is

backup database db_name to disk='backup_path' 
with init, compression

But this works only in Enterprise Edition of the SQL Server 2008 and not in other editions

If you want to know the new features that only work in Enterprise Edition,refer this
http://msdn.microsoft.com/en-us/library/cc645993.aspx

Script out Procedures to seperate files

In this post script-out-procedures-and-functions-part-2, I showed how to script out the 
procedures in a single file

Here is another way to do the same but different file for each procedure. The file name will be 
the name of the procedure.

declare @sps table(proc_name varchar(100),texts varchar(8000))

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

create table scripts(sps varchar(8000))

declare @texts varchar(1000)
declare @file_name varchar(100)
declare @sql varchar(1000)

select @texts=min(texts)from @sps

while @texts>''
Begin
        select @file_name=proc_name from @sps where texts=@texts
        EXEC('insert into scripts(sps) EXEC '+ @texts) insert into scripts(sps) select 'GO'
        select @texts=min(texts)from @sps where texts>@texts 
        set @sql='bcp "select * from yourdb..scripts" queryout "c:\'+@file_name+'.txt" -c' 
        EXEC master..xp_cmdshell @sql
        truncate table scripts
End 
drop table scripts

Finding out problematic data in Bulk Insert data truncation error

Suppose you want to import data from a text file to a table, one of the options is to use Bulk 
Insert command

It allows to import data to the table, though one/more columns don't have enough size to get the 
maximum length of data from text file.It throws errors about data truncation with line numbers. 

What if you want to find out the rows from the text file that are not imported

 Here is a way

Consider the following example

--Create a testing table

create table bulk_test
(
test_no     int,
test_name varchar(10)
)

GO

--The text file has the following data

id,    name
23,    test
8,      kjhasd
87,    sdf
23,    testing column
99,    ujh
45,    this is for testing only

--Bulk import query

BULK INSERT bulk_test 
FROM 'e:\test.txt'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR = '\n',
firstrow=2
) 

--Error that is thrown

Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 5, column 2 (test_name).
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 7, column 2 (test_name).

--Finding out the data that causes the error

select 
        t1.* 
from 
        openrowset('Microsoft.Jet.OLEDB.4.0','text;HDR=YES;FMT=FixedLength;Database=e:\', test#txt) as t1 
        left join bulk_test as t2 on t1.id=t2.test_no and t1.name=t2.test_name 
where t2.test_no is null

Removing tags from a string

In http://ask.sqlteam.com, a question was asked about removing the comments from the HTML 
template

The comments have the following pattern

{/*}comment{*/}

The comments can be nested too

The task is to remove all the comments

Here is my solution

declare @string varchar(8000) ,@data varchar(8000)

select @string = 'AaaA{/*}XxxX{/*}YyyyY{*/}ZzzzZ{*/}BbbB{/*}CCC{*/}DdD'

select @string=replace(replace(@string, '{/*}','~{/*}'),'{*/}','~{*/}')

declare @t table(id int identity(1,1),data varchar(8000)) 
insert into @t(data)
select 
        substring(@string, n, charindex('~', @string + '~', n ) - n)
from 
( 
        select number as n from master..spt_values where type='p' 
) numbers 
where 
        substring( '~' + @string, n, 1 ) = '~' 

--Method 1

set @string='' select @string =@string+data from 
(
    select data from @t where id=1 
    union all
    select substring(data,5,len(data)) from @t 
    where (data like '{*/}%' and id%2=1)
) as t 
select @string 

--Method 2

select @string =''

select 
        @string =@string +substring(t2.data,5,len(t2.data)) 
from 
        @t as t1 inner join @t as t2 on t1.id+1=t2.id
where 
        t2.data like '{*/}%' and t2.id%2=1 and t2.data<>'{*/}'

select @string