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'''
Because they are undocmented, you cant always rely on them.
Alternatively you can use the following methods
1 WHILE
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+'''' fromsys.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
Union Vs Union All
Well. Most of you know the diffrence between the two.
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
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'
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
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
Select columns from (EXEC procedure_name) - Is this possible?
Well.I see many users asking this question in forums. "I have a procedure that returns single resultset. How do I filter the result returned from the procedure? There are, at least, two ways to acheive this
Let us create this procedure
create procedure get_orders
as
select* from northwind..orders
1 Create a temporary table whose structure is identical to the result of the procedure and query on this table
Create table #orders(Orderid int,..................)
Insert into #orders EXEC get_orders
Select* from #orders where orderdate>='19960101' and orderdate<'19970101'
2 Use OPENROWSET
Select
*
from
OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes;
Integrated Security=SSPI','Execute yourdb..get_orders')
Now you can easily filter the resultset
Select
employeeid,orderid,orderdate
from
OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes;
Integrated Security=SSPI','Execute yourdb..get_orders')
where
orderdate>='19960101' and orderdate<'19970101'
I prefer using method 2 as you dont need to create temp table and
also you dont need to worry about the structure of the procedure
Random Password Generator
This is one of the methods to generate 8 characters password randomly (combination of alphabets,numbers and special characters)
declare @password varchar(8)
set @password=''
select @password=@password+char(n) from
(
select top 8 number as n from master..spt_values
where type='p' and number between 48 and 122
order by newid()
) as t
select @password
Should alias names be preceded by AS?
If you ask me, I would say Yes 
When you write select statements and use alias names, always make sure alias names are preceded by the keyword AS. Oherwise you may get results which are unexpected and sometimes difficult to debug.
Consider the following example
use northwind
select employeeid,orderdate,shippeddate,shipcity from orders
Well. You see the correct resultset
Now run this
select employeeid,orderdate,shippeddate shipcity from orders
and see the resultset.Shippeddate's column name becomes shipcity because there is a missing comma after shippeddate that forces shipcity to be alias for shippeddate. If there are tens of columns and if you miss out a comma, it would become tedius to debug if the resultset is sent to the client application
Consider another example
select count(*) from orders
It returns the count as 830
Now what happens by mistake if you omit the keyword from
select count(*) orders
Now it returns count as 1 with alias name orders.
If SQL Server forces you to use AS before alias name, you would get error for the select statements that returns unexpected result in the above example. So I always ask developers to use AS before alias name which certainly makes the statements more readable and easy to debug
Simple Multiply Function
There is no direct way to multiply all the values of a column as we do summation using SUM(col). But it is possible with the following methods
create table test (i int, amount float)
insert into test(i,amount)
select
abs(cast(cast(newid() as varbinary(100)) as smallint)),
abs(cast(cast(newid() as varbinary(100)) as smallint))/1000
from
sysobjects s1 cross join sysobjects s2
Method 1
select
i,exp(sum(log(cast(amount as float)))) AS multiplied_value
from
test
where
amount<>0
group by i
order by i
Method 2
use user defined function
create function dbo.multiply(@number int)
returns float as
begin
declare @i float
set @i=1.0
select @i=@i*amount from test where i
return @i
end
select i,dbo.multiply(i) as multiplied_value from
(
select distinct i from test
) as t
order by i
Easy way to generate number table
Often you may require number table for various purposes (parsing csv, string manipulation,etc)
If you use SQL Server 2005, then it is very easy than any other method you use in lower versions
Here are two methods of generating number table with 10000 numbers
(1) Use Row_number() funtion
Declare @numbers table(number int)
insert into @numbers(number)
select number from
(
select row_number() over (order by s1.name) as number
from sysobjects s1 cross join sysobjects s2
) as numbers
where number between 1 and 10000
select number from @numbers
(2) Use CTE
Declare @numbers table(number int)
with numbers(number) as
(
select 1 as number
union all
select number+1 from numbers where number<10000
)
insert into @numbers(number)
select number from numbers option(maxrecursion 10000)
select number from @numbers
Different ways to count rows from a table
The most reliable and suggested method is to use count(*)
1 Select count(*) from table_name
But other than that you can also use one of the followings
2 select sum(1) from table_name
3 select count(1) from table_name
4 select rows from sysindexes where object_name(id)='table_name' and indid<2
5 exec sp_spaceused 'table_name'
6 DBCC CHECKTABLE('table_name')
PS
To update rows column of sysindexes table, you need to run this before executing methods 4 and 5
DBCC UPDATEUSAGE ('database_name','table_name') WITH COUNT_ROWS.
Undocumented DATE and TIME related functions
It is quite surprising to know the undocumented date and time related functions that work in both sql server 2000 and 2005
Run these and see(I think they 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())}
Write Function in SQL Server 2005
One of the functions available in SQL Server 2005 that allows you to update a character column of size max is WRITE Function. In fact we can consider this as alternate to STUFF function which wont work for columns with Max length
declare @t table(v nvarchar(max))
insert into @t select 'test is test'
update @t set v=stuff(v,1,4,'This')
select v from @t
GO
declare @t table(v nvarchar(max))
insert into @t select 'test is test'
update @t set v.write('This',0,4)
select v from @t
PS You can use STUFF function in both SELECT and UPDATE Statement but WRITE function can be used only in UPDAET statement preceded by Column name.
Behaviour Changes in SQL Server 2005
If you run the following query in both SQL Server 2000 and 2005 , you can see the difference
select random,random,random
from
(
select rand() as random
) as t
Those behaviour changes are documented here
http://msdn2.microsoft.com/en-us/library/ms143359.aspx
Generating Random Numbers - Part II
There are many methods to generate random numbers. Here are some of them
select number from
(
select top 10000 abs(checksum(newid())) as number
from sysobjects s1 cross join sysobjects s2
) as T
select number from
(
select top 1000 abs(cast(cast(newid() as varbinary(10)) as int)) as number
from sysobjects s1 cross join sysobjects s2
) as T
If you want to generate 4 digits random number, then use
select number/power(10,len(number)-4) as number from
(
select top 10000 abs(checksum(newid())) as number
from sysobjects s1 cross join sysobjects s2
) as T
If you want to generate 2 digits random number, then use
select number/power(10,len(number)-2) as number from
(
select top 10000 abs(checksum(newid())) as number
from sysobjects s1 cross join sysobjects s2
) as T
If you use SQL Server 2005, you can also apply the same approach on the following query
select row_number() over (order by s1.name) as number
from sysobjects s1 cross join sysobjects s2
ISNULL() or COALESCE()?
Both the functions are used to show/assign different value if the parameter is null.
select isnull(null,'test'),coalesce(null,'test')
But there are diiferences between the two
isnull() is sql server specific
The datatype/length of the result depends on the parameter
ex
declare @s varchar(1)
Select isnull(@s,'test')
Note that the result is t and not test
The name isnull sounds that it should return 0 or 1 like other functions isdate and isnumeric
But coalesce has advantages over isnull
It is defined by ANSI standard
It will accept more than one parameters
The result doesnt directly depend on the parameter
ex
declare @s varchar(1)
Select coalesce(@s,'test')
and note that the result is test
Although it is proved that isnull is faster than coalesce here (http://sqljunkies.com/WebLog/amachanic/archive/2004/11/30/5311.aspx), for the above reasons I prefer using coalesce
Handle ISDATE() with care
Now-a-days it becomes common practice for newbies to use varchar datatype to store dates. My first advice is to use proper DATETIME datatype. Sometimes it is preferred to use varchar when importing dates from other systems(to avoid any errors if the source dates are in different format). In that case it is required to see if values are actually dates. To check if a value is proper date, it is suggested to use ISDATE()
eg
select isdate('2007-10-12'),isdate('12-12-2006'),isdate('March 12, 2007')
Now it should be noted that the following return 1
select isdate(2007),isdate('2007')
becuase the values in a ISDATE functions are first casted to datetime
select cast(2007 as datetime)
------------------------------------------------------
1905-07-01 00:00:00.000
select cast('2007' as datetime)
------------------------------------------------------
2007-01-01 00:00:00.000
Which are valid dates
So if varchar datatype is used to stored formatted dates (ex YYYYMMDD), to check if it is valid date, you need not only to use ISDATE() function, but also use LEN() function
Consider the following set of data and see the difference
declare @dates table (dates varchar(8))
insert into @dates(dates)
Select '20071201' union all
Select '20071111' union all
Select '2007' union all
Select 2007 union all
Select '1800'
select dates from @dates
where ISDATE(dates) =1
--Result
dates
--------
20071201
20071111
2007
2007
1800
--Result
select dates from @dates
where ISDATE(dates) =1 and LEN(dates)=8
dates
--------
20071201
20071111
Find Nth Maximum value
"How do I find Nth maximum value?" is one of the most asked questions
Here are some methods
I explain how to find 5th Maximum value
Create table number (num integer)
Insert into number values(3456)
Insert into number values(846)
Insert into number values(1456)
Insert into number values(3098)
Insert into number values(34)
Insert into number values(67856)
Insert into number values(906)
Insert into number values(34656)
Insert into number values(9056)
Insert into number values(3036)
(1) Use Inner Join
select t1.num from number t1 inner join number t2 on t1.num<=t2.num
group by t1.num having count(t1.num)=5
(2) Use Top Operator
Select top 1 num from(
Select top 5 num from number order by num desc) T
order by num asc
(3) Generate Serial No based on descending order of the values
select num from (
Select (select count(*) from number where num>=T.num)as Sno ,num
from number T ) temp where Sno=5
(4) Use Aggregate Function
Select min(num) from (select top 5 num from number order by num desc) T
Now you can replace 5 to the number that you want to find the maximum value
Import/Export to Excel
Generate SQL Script
--This procedure will generate the structure of the table
Create Procedure GenerateScript (
@tableName varchar(100))
as
If exists (Select * from Information_Schema.COLUMNS where Table_Name= @tableName)
Begin
declare @sql varchar(8000)
declare @table varchar(100)
declare @cols table (datatype varchar(50))
insert into @cols values('bit')
insert into @cols values('binary')
insert into @cols values('bigint')
insert into @cols values('int')
insert into @cols values('float')
insert into @cols values('datetime')
insert into @cols values('text')
insert into @cols values('image')
insert into @cols values('uniqueidentifier')
insert into @cols values('smalldatetime')
insert into @cols values('tinyint')
insert into @cols values('smallint')
insert into @cols values('sql_variant')
set @sql=''
Select @sql=@sql+
case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type +
case when Data_Type in (Select datatype from @cols) then '' else '(' end+
case when data_type in ('real','money','decimal','numeric') then cast(isnull(numeric_precision,'') as varchar)+','+
case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar) end
when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end+
case when Data_Type in (Select datatype from @cols)then '' else ')' end+
case when Is_Nullable='No' then ' Not null,' else ' null,' end
from Information_Schema.COLUMNS where Table_Name=@tableName
select @table= 'Create table ' + table_Name from Information_Schema.COLUMNS where table_Name=@tableName
select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'
select @sql as DDL
End
Else
Select 'The table '+@tableName + ' does not exist'
Select Data from Top N Columns
Suppose you have table with many columns and often you need data from first 15 or 20 columns. You have to specify all the columns in your select statement. This procedure will select top N columns you want. All you have to do is to supply table name and number of columns you want
Here is the procedure
CREATE procedure TopNcolumns (@tableName varchar(100),@n int)
as
Declare @s varchar(2000)
set @s=''
If @n>=0
Begin
set rowcount @n
Select @s=@s+','+ column_name from information_schema.columns
where table_name=@tablename order by ordinal_position
Set rowcount 0
Set @s=substring(@s,2,len(@s)-1)
Exec('Select '+@s+' from '+@tablename)
End
else
Select 'Negative values are not allowed' as Error
If you execute TopNColumns 'Mytable',12 then first 12 columns with data from the table Mytable will be displayed
Backup Database
This query will backup the current database
declare @sql varchar(1000)
select @sql = 'BACKUP DATABASE '+(select db_name())+' TO DISK = ''E:\'+(select db_name())+
convert(varchar,GETDATE(),112)+'.bak'''
Exec(@sql)
If the database name is Test then executing this query will backup Test database and name of that backup file is Test20050817.bak. Current date with the format yyyymmdd will be suffixed to the DBName
If you schedule this query to run daily, then backup will be generated daily as a seperate file
Generate Random Numbers
This procedure will display n random numbers selected from specified range
Pass Starting Number, Ending Number and the number of numbers you want to select randomly
Create Procedure RandomNumbers (@min bigint, @max bigint, @count int)
as
Set Nocount On
Declare @t table(num bigint)
Declare @start bigint
Declare @End bigint
While @min<=@max
Begin
Insert into @t values(@min)
set @min=@min+1
End
Set Rowcount @count
Select num from @t order by newid()
Set Rowcount 0
If you run the below code
Exec RandomNumbers 100,300,10
10 random numbers between 100 and 300 will be displayed
Scripts Stored Procedures and Functions through query
This query will script the Procedures and Functions
All you have to do is replace DBname by the actual Database Name
exec master..xp_cmdshell
'bcp "Select routine_definition from DBname.information_Schema.routines order by routine_name" queryout "C:\scripts.sql" -c'
After it runs successfully, the file C:\scripts.sql will have the scripts
BCP - Export data to Text File
Here is a simple method of exporting all the data from SQL Server table to a Text File
CREATE Procedure BCP_Text_File
(
@table varchar(100),
@FileName varchar(100)
)
as
If exists(Select * from information_Schema.tables where table_name=@table)
Begin
Declare @str varchar(1000)
set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c'''
Exec(@str)
end
else
Select 'The table '+@table+' does not exist in the database'
Execute this procedure by giving Table Name and the File Name
EXEC BCP_Text_File 'Employee','C:\emp.txt'
Now all the data from Employee table will be exported to the text file which will be located at C:\emp.txt
Get IP Address
Here is a method of getting the IP Address of a system using Stored Procedure
Create Procedure Get_IPAddress
(@sysName varchar(30))
as
Set Nocount On
Create table #t (data varchar(100))
Declare @sql varchar(100)
Set @sql='
Insert into #t
Exec Master..xp_cmdShell @sql
If exists (select * from #t where data like '%Unknown%')
Select 'The system '+@sysName+' doesnt exist' as Error
else
Select Substring(data,charindex('[',data,1)+1,charindex(']',data,1)-(charindex('[',data,1)+1))
as IPAddress from #t where data like '%bytes of data%'
drop table #t
Set Nocount Off
Run this procedure as
EXEC Get_IPAddress 'systemName'
Storing Images
I have read many questions in the Forums asking "How do I store images in the table?". The simplest answer would be to store only the physical path of the images in the table and storing the images in the server's physical directory. To retrieve the image use Presentation layer's File System object. This makes easy the process of storing and retrieving the images.Otherwise AppendChunk and GetChunk methods should be used in the Front End Application.
Here are the complete explanations on how to handle images
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx
http://support.microsoft.com/default.aspx?scid=kb;en-us;317016
CSV to Multiple Columns
If the table is denormalised and has Comma Seperate Values in a
column, this code will copy it to Multiple columns of a
Normalised table
declare @DeNormalisedTable table(data varchar(8000)) insert into @DeNormalisedTable select '1,Davolio,Nancy select '2,Fuller,Andrew' union allselect '3,Leverling,Janet' union allselect '4,Peacock,Margaret' union allselect '5,Buchanan,Steven' union allselect '6,Suyama,Michael' union allselect '7,King,Robert' union allselect '8,Callahan,Laura' union allselect '9,Dodsworth,Anne' select * from @DeNormalisedTable -- Comma Seperated Valuesdeclare @s varchar(8000), @data varchar(8000)Create table #NormalisedTable (Code int, FirstName varchar(100),
LastName varchar(100)) select @s='' while exists (Select * from @DeNormalisedTable where data>@s)Begin Select @s=min(data) from @DeNormalisedTable where data>@s select @data=''''+replace(@s,',',''',''')+'''' insert into #NormalisedTable exec('select '+@data)End select * from #NormalisedTable -- Data in Normalised Table drop table #NormalisedTableEnhanced ISNUMERIC() function
It seems often users want to check whether the data has only numbers in a varchar type column. The commonly suggested one is to make use of ISNUMERIC() function. But the problem in using that function is that it will treat some alphabets, $, char(10),etc as numbers.
Cosider this example
declare @test table(strings varchar(50))
Insert into @test
Select '12d3' union all
Select '87234.45' union all
Select '$123,456.00' union all
Select ' 12 ' union all
Select char(10) union all
Select '$'
select strings,isnumeric(strings)as valid from @test
The result is
strings valid
-------------------------------------------------- -----------
12d3 1
87234.45 1
$123,456.00 1
12 1
1
$ 1
Note that only 87234.45 should be considered as numeric and not others
The following function will solve this problem
CREATE Function Is_numeric(@value varchar(25))
Returns bit
as
Begin
Return
(
case when @value not like '%[^-0-9.]%' and len(@value)-len(replace(@value,'.',''))<2
and
1=
(
case when charindex('-',@value)>0 then
case when left(@value,1)='-' and len(@value)-len(replace(@value,'-',''))<2>1 then
1
else
0
end
else
1
end
) then
1
else
0
end
)
End
Now execute this select statement
select strings, dbo.is_numeric(strings)as valid from @test
The result is
strings valid
-------------------------------------------------- -----
12d3 0
87234.45 1
$123,456.00 0
12 0
0
$ 0
SQLAnswers
This freeware tool is used to execute sql,procedures,etc and export the result to EXCEL,PDF,HTML,etc format
Try it here
http://www.sqlanswers.com/Software/
Dynamic Crosstab with multiple PIVOT Columns
Jeff Smith in his weblog showed how to generate Crosstab reports using a stored procedure. It works only for one PIVOT Column.I had a requirement to generate crosstab reports with more than one pivot column. So I used the same approach he used and modified his procedure as shown below
CREATE procedure CrossTab
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100),
@GroupBy varchar(100),
@OtherCols varchar(100) = Null
)
AS
set nocount on
set ansi_warnings off
declare @sql varchar(8000)
Select @sql = ''
Select @OtherCols= isNull(', ' + @OtherCols,'')
create table #pivot_columns (pivot_column_name varchar(100))
Select @sql='select ''' + replace( + @PivotCol,',',''' as pivot_column_name union all select ''')+''''
insert into #pivot_columns
exec(@sql)
select @sql=''
create table #pivot_columns_data (pivot_column_name varchar(100),pivot_column_data varchar(100))
Select @PivotCol=''
Select @PivotCol=min(pivot_column_name) from #pivot_columns
While @PivotCol>''
Begin
insert into #pivot_columns_data(pivot_column_name,pivot_column_data)
exec (
'select distinct ''' + @PivotCol +''' as pivot_column_name, convert(varchar(100),' + @PivotCol + ') as pivot_column_data from
('+
@select
+'
) T'
)
Select @PivotCol=min(pivot_column_name) from #pivot_columns where pivot_column_name>@PivotCol
end
select @sql = @sql + ', ' +
replace(
replace(
@Summaries,'(','(CASE WHEN ' + Pivot_Column_name + '=''' +
pivot_column_data + ''' THEN '
),
')[', ' END) as [' + pivot_column_data
)
from #pivot_columns_data
order by pivot_column_name
exec ( 'select ' + @GroupBy +@OtherCols +@sql +
' from (
'+
@select
+'
) T
GROUP BY ' + @GroupBy)
drop table #pivot_columns
drop table #pivot_columns_data
set nocount off
set ansi_warnings on
Now execute the procedure by supplying two pivot columns shipcountry and Year(Orderdate)
EXEC CrossTab
'SELECT LastName, OrderDate,shipcountry FROM northwind..Employees Employees
INNER JOIN northwind..Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'shipcountry,Year(OrderDate)',
'Count(LastName)[]',
'LastName'
Multipurpose Row_Number() Function
One of the features available in SQL Server 2005 is Row_Number() function. It is not only used to generate row number for each row but also used for other purposes as well. I breifly explain how it can be used for various purposes
Consider the following data
Declare @t table(item varchar(100), price float)
insert into @t
select 'item1', 20000 union all
select 'item1', 20000 union all
select 'item1', 20700 union all
select 'item2', 57600 union all
select 'item2', 80120 union all
select 'item3', 89760 union all
select 'item3', 87680 union all
select 'item4', 87680 union all
select 'item4', 43220 union all
select 'item4', 43220
(1)
Generate Serial No or replicate identity column
select row_number() over(order by item) as row_number, * from @t
--Result
row_number item price
-------------------- ---------------------- ------------------------------------------
1 item1 20000.0
2 item1 20000.0
3 item1 20700.0
4 item2 57600.0
5 item2 80120.0
6 item3 89760.0
7 item3 87680.0
8 item4 87680.0
9 item4 43220.0
10 item4 43220.0
(2)
Generate Serial No and reset in each group
select row_number() over(partition by item order by item) as row_number, * from @t
--Result
row_number item price
-------------------- ---------------------- ----------------------------------------
1 item1 20000.0
2 item1 20000.0
3 item1 20700.0
1 item2 57600.0
2 item2 80120.0
1 item3 89760.0
2 item3 87680.0
1 item4 87680.0
2 item4 43220.0
3 item4 43220.0
(3)
Select top N data for each group
Select * from
(
select row_number() over(partition by item order by item) as row_number, * from @t
) T
where row_number<=N
where N is a positive integer value
--Result (when N=2)
row_number item price
-------------------- ---------------------- ------------------------------------------
1 item1 20000.0
2 item1 20000.0
1 item2 57600.0
2 item2 80120.0
1 item3 89760.0
2 item3 87680.0
1 item4 87680.0
2 item4 43220.0
(4)
Pagination
Select item,price from
(
select row_number() over(order by item) as row_number, * from @t
) T
where row_number between 1 and 5
--Result
item price
------------------------- ------------------------------------------
item1 20000.0
item1 20000.0
item1 20700.0
item2 57600.0
item2 80120.0
(5)
Find Nth Maximum value
Select price from
(
select row_number() over(order by price desc) as row_number, price from(select distinct price from @t) d
) T
where row_number=N
--Result(where N=2)
price
------------------------------------------
87680.0
(6)
Delete duplicates
delete T from
(
select row_number() over(order by item) as row_number, * from @t
) T
where row_number not in
(
select min(row_number) from
(select row_number() over(order by item) as row_number, * from @t) T
group by item
)
or
delete T from
(
select row_number() over(partition by item order by item) as row_number, * from @t
) T
where row_number>1
select * from @t
--Result
item price
------------------------- ------------------------------------------
item1 20000.0
item2 57600.0
item3 89760.0
item4 87680.0
Truncate All Tables - Part II
There are many methods to truncate all the tables in a database
Part I of my post http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-i.aspx shows executing the concatenated truncate table
statements. This is other method
Set
Declare @tableName varchar(200)
set @tableName=''
While exists
(
--Find all child tables and those which have no relations
select T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name
where (TC.constraint_Type ='Foreign Key'or TC.constraint_Type is NULL) and
T.table_name not in ('dtproperties','sysconstraints','syssegments')and
Table_type='BASE TABLE' and T.table_name > @TableName)
Begin
Select @tableName=min(T.table_name) from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name
where (TC.constraint_Type ='Foreign Key'or TC.constraint_Type is NULL) and
T.table_name not in ('dtproperties','sysconstraints','syssegments') and
Table_type='BASE TABLE' and T.table_name > @TableName
--Truncate the table
Exec('Truncate table '+@tablename)
End
set @TableName=''
While exists
(
--Find all Parent tables
select T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name
where TC.constraint_Type ='Primary Key'and T.table_name <>'dtproperties' and
Table_type='BASE TABLE' and T.table_name > @TableName )
Begin
Select @tableName=min(T.table_name) from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on T.table_name=TC.table_name
where TC.constraint_Type ='Primary Key' and T.table_name <>'dtproperties' and
Table_type='BASE TABLE' and T.table_name > @TableName
--Delete the table
Exec('Delete from '+ @tableName)
--Reset identity column
If exists
(
select * from information_schema.columns
where COLUMNPROPERTY(OBJECT_ID(QUOTENAME(table_schema)+'.'+
QUOTENAME(@tableName)), column_name,'IsIdentity')=1
)
DBCC CHECKIDENT (@tableName, RESEED, 1)
End
Set NoCount Off
Truncate All Tables - Part I
This procedure will truncate all the tables in the database
To truncate all tables,I have seen the programmers using Cursors to get all tables and truncate them.This will avoid Cursors. Running this procedure will make SQL Server to slow down other tasks. So test this with test server and make sure that you are not running any other SQL Server related processes
Create Procedure Truncate_All_Tables
as
Declare @t table(query varchar(1000),tables varchar(50))
Insert into @t
select 'Truncate table ['+T.table_name+']', T.Table_Name from
INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name
where (TC.constraint_Type ='Foreign Key' or TC.constraint_Type is NULL) and
T.table_name not in ('dtproperties','sysconstraints','syssegments') and
Table_type='BASE TABLE'
Insert into @t
select 'delete table ['+T.table_name+']', T.Table_Name from
INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key' and
T.table_name <>'dtproperties'and Table_type='BASE TABLE'
Declare @sql varchar(8000)
Select @sql=IsNull(@sql+' ','')+ query from @t
Exec(@sql)
GO
Run this procedure as
EXEC Truncate_All_Tables
