Madhivanan

When was my Server restarted?

2008-06-09T15:24:00Z

Temporary database tempdb is created whenever the Server is restarted. So you can know when Server was restarted by knowing the creation date of the tempdb

EXEC sp_helpdb tempdb

select crdate from master..sysdatabases
where name='tempdb'

Simulating undocumented Procedures

2008-05-13T09:59:00Z

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

Because they are undocmented, you cant always rely on them.

Alternatively you can use the following methods

1 WHILE LOOP

declare @dbname varchar(100), @database_id int,@table_name varchar(100)
select @dbname='', @database_id=1,@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

2 Concatenated SQL

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

2008-05-03T13:12:00Z

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

2008-04-04T13:45:00Z

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

2008-04-03T13:45:00Z

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

2008-03-15T10:57:00Z

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

2008-02-28T10:31:00Z

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

2008-02-19T12:49:00Z

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 to double it

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

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

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

When you use a static sql and express a value in a single quote then first and last sigle quotes specify that the value is a string. Then within those single quotes every double single quotes represent a single single quote

When you use a Dynamic sql then first and last sigle quotes specify that it is a dynamic sql. Then within those single quotes every double single quotes specify that it is a string.Then within those single quotes every four single quotes represent a single single quote

Run and see the result

EXEC('SELECT '''','''''''','''''''''''','''''''''''''''',''''''''''''''''''''')

Forcing integrity between tables and procedures

2008-01-17T14:37:00Z

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
asselect * from test_table

GO
drop table test_table



Now you would get error as test_sp is referencing the table

Beware of Implicit conversions

2008-01-16T10:51:00Z

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

2007-12-29T07:05:00Z

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

2007-12-21T09:32:00Z

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

2007-12-18T14:48:00Z

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 approach


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

Script out Procedures and Functions - Part 2

2007-12-13T14:09:00Z


 
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

2007-12-06T12:56:00Z

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