Dynamic alter table script to make all column NULL in sql server 2005/2008

I have read question in few different forums many time that “How to make all column null in one table?” 
Answer is very small and handy dynamic script which uses “Information_Schema.columns” view to get column name and generate Alter Table script. Have a look:
select ‘alter table ‘ + ic.TABLE_SCHEMA + ‘.’ + ic.TABLE_NAME + ‘ alter column ‘ + ic.COLUMN_NAME +   + ic.DATA_TYPE 
+ case when ic.DATA_TYPE=‘varchar’ then + ‘(‘ + cast(ic.CHARACTER_MAXIMUM_LENGTH AS varchar(3)) + ‘)’ else ‘ ‘ end + ‘ ‘ + ‘null’
 from INFORMATION_SCHEMA.COLUMNS as ic
 left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as icu on ic.TABLE_NAME=icu.TABLE_NAME and ic.COLUMN_NAME=icu.COLUMN_NAME
 where ic.TABLE_NAME=‘YourTableName’ and COLUMNPROPERTY(object_id(ic.table_name),ic.column_name,‘IsIdentity’)=0
 and icu.TABLE_NAME is null

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

OUTPUT Clause in SQL Server 2008/2005

Today I was working on one Audit Trail project and thought to test OUTPUT clause. As soon as I wrote OUTPUT clause, it pops up in my mind that I have not written anything about OUTPUT clause in my blog so far. So, I felt, this is the time to write something about this.
You people might be aware with pseudo table (INSERTED and DELETED) in trigger, this is something similar concept, the difference is, you can get pseudo table inside trigger and you can use OUTPUT clause outside trigger too.
Let us see its usefulness by one small example.
–create table for demo and insert few records
create table testOutPut(tid int identity(1,1), name varchar(50))
insert into testOutPut (name)
select ‘Ritesh’ union all
select ‘Rajan’ union all
select ‘Pinal’ union all
select ‘Bihag’ union all
select ‘John’ union all
select ‘Bhaumik’ union all
select ‘Avi’ union all
select ‘James’
go
–check whether all records came with proper tid
select * from testOutPut
go
–create temp duplicate table which will store data which are deleted
create table #deleted (id int, name varchar(50))
–deleting records from testOutPut table and inserting those deleted
–records into temp table with help of OUTPUT clause and DELETED pseudo table
delete testOutPut
output DELETED.* into #deleted
where tid < 3
–checking temp table
select * from #deleted
–this will show deleted data on screen
–but won’t store anywhere like we did in previous snippet
–and stored data in #deleted (temp table)
delete testOutPut
output DELETED.*
where tid >3
select * from testOutPut
go
select * from #deleted
go
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Insert and Update image field in SQL Server 2008/2005

Few months back I have written one article which was showing how to store image and other files into SQL Server column by converting it to byte object and receive it back. That was with the help of C# but now this time I am going to show, how you can insert and update image or varbinary field from within SQL Server itself.
Before we move further, If you wish to look at my previous article with C# script, please have a look at:
Ok, now let us move ahead with our script in SQL Server itself.
–create table for demonstration
create table emps
(
      name varchar(50),
      dept varchar(10),
      empImg image
)
GO


–insert statement with single_blob to upload image to SQL Server
INSERT INTO emps ([Name],dept,empImg)
SELECT ‘Ritesh’,‘MIS’,
(select * FROM OPENROWSET(BULK ‘C:\Ritesh.JPG’, SINGLE_BLOB) AS img)
GO


–check the inserted data
select * from emps
GO

–update your table, along with image also.
update emps
set empImg=(select * FROM OPENROWSET(BULK ‘C:\Ritesh1.JPG’, SINGLE_BLOB) AS img), dept=‘IT’
where name=‘Ritesh’
GO

–check the data whether it has been updated
select * from emps
go

 
Happy SQLing!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Update UnTyped XML data column in SQL Server 2008/2005

Now a day, XML is booming, it is welcomed from every platform so why Microsoft and especially SQL Server sit back? After SQL Server 2005, Microsoft have provided so many functionality for make developer’s life easy who are using XML. Today, I would like you to show how you can update un-typed XML data from within well known UPDATE T-SQL statement.
–create table for testing purpose with XML column
–in SQL Server 2005 or in 2008
create table xmlTest
(
ColumnXML xml
)
GO
–insert data in XML column
insert into xmlTest
select

   
  A1000  
  Ritesh Shah  
  HP Notebook  
GO
–check the output
select * from xmlTest
GO
–update value (un-typed) in product tag
DECLARE @Val varchar(50) 
SELECT @Val = ‘Dell Inspiron’ 
 
UPDATE xmlTest 
SET ColumnXML.modify(‘replace value of (/Orders/Product/text())[1] with sql:variable(“@Val”)’) 
GO
–confirm changes
select * from xmlTest



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Return comma separated value with For XML Path in SQL Server 2008/2005

We many time needs to return value of one column as a comma separated value, we can use COALESCE or ISNULL for this task but it is a lengthy process. I also have written one article for same task with COALESCE function, have a look:
Today I am going to show you one of the easy methods to do same task. Let us create one dummy table and move ahead.
use Adventureworks
CREATE TABLE [dbo].[emps](
[Name] [varchar](50),
[Dept] [varchar](10),
[Company] [varchar](15)
) ON [PRIMARY]
GO
–insert records
INSERT INTO emps
SELECT ‘RITESH’,‘MIS’,‘ECHEM’ UNION ALL
SELECT ‘Rajan’,‘MIS’,‘mar’
Now this is a time to show you how to get all names with separated by comma.
select left(t.name,len(t.name)-1) as ‘allName’ from
(
select name + ‘,’  from emps for xml path()
) as t(name)

Isn’t is easy to use method rather than ISNULL and COALESCE? Yes, it is!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Get latest running job from sysJobHistory in SQL Server 2005/2008

We might have several job running in our servers and we can find its stat from its log file but it would be interesting to query job history table. Basically all details about JOBS reside in MSDB database in SQL Server and you can query to SysJobHistory to get information about jobs. You can have so many details about JOB in SysJobHistory table like Job run date, time, job name, message about whether it was successful or not etc. I often need to know what JOBs has been ran in last 24 or 48 hours, whether it was successful or not etc. Let us see that small yet useful T-SQL.
select * from msdb..sysjobhistory


If you want to look at jobs which ran in last one or two days, you can query date like this:
select *
from msdb..sysjobhistory
where (  run_date = convert(varchar(8), getdate(), 112)
     and run_time < replace(convert(varchar(10), getdate(), 8), ‘:’, )
      )
   or(  run_date = convert(varchar(8), getdate() 1, 112)
     and run_time >= replace(convert(varchar(10), getdate(), 8), ‘:’, )
      )
order by run_date, run_time
Actually date and time both are stored in separate INT column so that we have to convert it in proper format and have to compare it by using string functions.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah