How to search specific value in character column of all tables?

Few questions are asked in forums about searching a specific value across all tables in the database stored in character columns (char, varchar, etc)

There can be several methods to make this possible. Here is one of them

Let us create this stored procedure

create procedure search_all_tables 
(
	@seach_string varchar(max)
)
as
declare @sql varchar(max)
set @sql=''

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''%'+@seach_string+'%''
	 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) 

Also create few tables

create table test1 (col1 int, col2 varchar(100))
insert into test1 
select 1,'welcome to sql server'
GO
create table test2 (col1 int, col2 varchar(max))
insert into test2 
select 10,'welcome to sql server'
GO
create table test3 
(
col1 int, 
col2 varchar(max),
col3 varchar(200)
)
insert into test3
select 10,'welcome to database programming','sql version 2012'
union all
select 11,'welcome',''

GO
create table test4 (col1 int, col2 varchar(max))
insert into test4
select 22,'Unix shell programming'

Suppose you want to find out the table name, column name and string value that contains the value ‘sql’, you can execute the procedure as shown below

EXECUTE search_all_tables 'sql'

The result is

table_name column_name column_value
---------- ----------- -------------------------
test1      col2        welcome to sql server
test2      col2        welcome to sql server
test3      col3        sql version 2012

It listed out all the column names where string value sql is part of it.

Suppose you want to find out the table name, column name and string value that contains the value ‘programming’, execute the following

EXECUTE search_all_tables 'programming'

The result is

table_name column_name column_value
---------- ----------- --------------------------------
test4      col2        Unix shell programming
test3      col2        welcome to database programming

This way you can make use of this stored procedure to search for a character values stored in character columns of any user defined tables in the entire database.

When is usage of semicolon necessary?

Semicolon is used as a statement terminator in almost all RDBMSs. However the usage of semicolon is optional in SQL Server except for few cases. Here are at least three cases where usage of semicolon is a must

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

2 If the procedure name is GO ,to execute it use at the end of GO

Let us create this stored procedure with Batch separator GO

use tempdb

GO

CREATE PROCEDURE GO
as
select 10 as number 

Now execute the procedure

GO

Nothing happens

Now execute it with semicolon

GO;

The result is 10

3 At the end of Merge statement

Let me know if it is required in any other cases.

How to import multiple result sets from Stored procedure?

One of my friends asked me if it is possible to load multiple resultsets returned from a stored procedure into a temporary table for some analysis purpose. The answer is “It depends”. If all the result sets return same number of columns then it is possible otherwise it is not possible.

Create the following stored procedure

USE tempdb 

GO

CREATE PROCEDURE proc_test
AS 
  SELECT 1003   AS number 
  SELECT 28 AS number 
  SELECT 987345  AS number 

When you execute this procedure

  EXEC proc_test

You get three different result sets

number
-----------
1003

number
-----------
28

number
-----------
987345

Now create a temporary table

  CREATE TABLE #temp 
  ( 
       number INT 
  ) 

Add data to this table by executing the stored procedure

  INSERT INTO #temp 
  ( 
	number 
  ) 
  EXEC proc_test

Now check the result from this temporary table

SELECT number FROM #temp 

The result is

number
---------
1003
28
987345

So if the table structure is similar to the result set of the stored procedure, it does not matter how many similar result sets are returned, it can be imported to the table. However Currently it is not possible to identify a specific result set from the stored procedure.

Find Nth Maximum value

“Find Nth Maximum value” is one of the very popular interview questions.

There are several methods to find out the answer. Here are some methods

Consider the following data set


use tempdb 

create table numbers (number integer)
Insert into numbers values(1288)
Insert into numbers values(8653)
Insert into numbers values(1)
Insert into numbers values(390)
Insert into numbers values(4009)
Insert into numbers values(345)
Insert into numbers values(2777)

Assume that you want to find out 3rd maximum value, use one of the following methods
(1) Use Inner Join

select
	t1.number from numbers t1 inner join numbers t2
	on t1.number<=t2.number  group by t1.number having count(t1.number)=3 

(2) Use Top Operator

 Select top 1 number from (      Select  		top 3 number  	from  		numbers  	order by number desc ) T  order by number asc  

(3) Generate Serial No based on descending order of the values

 select number from  (          Select  			( 				select  					count(*)  				from  					numbers  				where number>=T.number)as Sno ,
		number
        from numbers as T
) as temp
where Sno=3

(4) Generate Serial No based on descending order of the values in where Clause

select number from numbers as n
where
(
	select
		count(*)
	from
		numbers
	where number>=n.number
)=3

(5) Use Aggregate Function

Select min(number) from
(
	select
		top 3 number
	from
		numbers
	order by number desc
) T

(6) Use Row_number() function

select number from
(
    select
		number, row_number() over (order by number desc) as sno
	from
		numbers
) as t
where sno=3

Now you can replace 3 to the numbers that you want to find the maximum value

ODBC scalar functions – DATE , TIME and Interval functions

You know very well how to return current date and time using getdate() function. Did you know that there exists ODBC function to return the same?

Select getdate()

returns current date and time

The scalar function

select {fn now()}

also returns current date and time

You can use get month, hour, day, etc using EXTRACT interval function

select {fn extract(month from getdate())}

returns the current month

select {fn extract(day from getdate())}

returns current day value

Here are some functions to explore. These are self-explanatory

select {fn current_date()}
select {fn current_time()}
select {fn now()}
select {fn extract(hour from getdate())}
select {fn extract(minute from getdate())}
select {fn extract(second from getdate())}
select {fn extract(day from getdate())}
select {fn extract(month from getdate())}
select {fn extract(year from getdate())}
select {fn dayname(GetDate())}
select {fn monthname(GetDate())}
select {fn month(GetDate())}
select {fn year(GetDate())}

 

Question of the month April 2017 – Why does GO throw error in Dynamic SQL and not in static SQL?

Execute the following code

select 10 as number1
GO
select 11 as number2

It produces following two result sets


number1
-----------
10

(1 row(s) affected)

number2
-----------
11

(1 row(s) affected)

Now the same code executed as Dynamic SQL,

EXEC
(
'
select 10 as number1
GO
select 11 as number2
'
)

throws the following error.

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'GO'.

The Question is “Why does GO throw error in Dynamic SQL but not in static SQL?”

Column alias in SQL Server – Always precede with keyword AS

In SQL Server, a column alias can be specified in several ways. Look at the following example

select
	100number,
	100.number,
	100 number,
	100'number',
	100"number",
	100 [number]
	100              number,
	100 as number

The result is

number   number   number    number   number   number   number   number
-------- -------- --------  -------- -------  -------  -------  ------- 
100      100      100       100       100     100      100      100

Now as you see the column alias is number for all the expressions although they are all different expressions.

However you need to use AS before the alias name for better reading and standard

Now execute the following and see the result

drop table if exists #employee_master;
create table #employee_master
(
emp_id int, emp_name varchar(100), dob datetime
)
insert into #employee_master
select 1,'Sankar','19760910' union all
select 2,'Kamal','19680707' union all
select 3,'Rajesh','19661222'
Select emp_id, emp_name dob from #employee_master

The result is


emp_id      dob
----------- ------------
1           Sankar
2           Kamal
3           Rajesh

The SELECT statement lists out three columns but the output has only two columns. The data of emp_name are listed under the column name dob. It is because the missing comma between column names makes that dob becomes alias name for emp_name which is confusing and hard to detect if there are hundreds of lines in the procedure.

In my opinion, SQL Server should force preceding AS before column alias

Row_number() Function with no specific order

You know that Row_number() function is used to generate row number to rows based on the column which is ordered

What if you want to generate row number without ordering any column

Here is the method

Consider the following dataset

use tempdb 

create table temp(dates datetime)
 
insert into temp
select '20090114' union all
select '20080131 10:10:10' union all
select '20070111' union all
select '20090330' union all
select '20120229' union all
select '20100228'

Now you need to use row_number function without order dates column

Here are two methods

1 Assign dummy column with literal 0 and order it by that column

select 
       row_number() over(order by order_col) as sno ,
       dates from 
(
        select dates,0 as order_col from temp
) as t

2 Assign dummy column with literal 0 directly on the Order by clause

select 
      row_number() over(order by (select 0)) as sno ,
      dates 
from 
      temp 

Both the queries will return the following result

sno                  dates
-------------------- -----------------------
1                    2009-01-14 00:00:00.000
2                    2008-01-31 10:10:10.000
3                    2007-01-11 00:00:00.000
4                    2009-03-30 00:00:00.000
5                    2012-02-29 00:00:00.000
6                    2010-02-28 00:00:00.000

Beware of data truncation – Replicate function with Varchar(max) datatype

As you know, Replicate function is used to repeat a character expression for a specified number of times. But by default the result is converted to varchar with maximum size of 8000 when you don’t convert the expression to specific type

Consider the following example

declare @v varchar(max)
set @v=replicate('a',50000)
select len(@v),datalength(@v)

What do you think the result would be? 50000 Right? But the result is 8000 because by default the result is limited to the maximum size of 8000 for varchar/char datatype.

To get a correct result, you need to convert the expression to the type of varchar(max) as shown below

declare @v varchar(max)
set @v=replicate(convert(varchar(max),'a'),50000)
select len(@v),datalength(@v)

Now the result is 50000 as expected.

So you need to be aware of this implicit convertion when using Replicate function to assingn value to the column of varchar(max) datatype