October 14, 2008

Date and time styles


The CONVERT function provides a variety of useful Date and Time styles. Here is the syntax for the CONVERT function syntax:
CONVERT (data_type[(length)],expression [, style])

Here's the T-SQL for date style conversions that I use on a regular basis along with the corresponding query results from running the batch at the bottom:
If the date resides in a char(8) column in MMDDYYYY format, you can do this to convert it to the datetime datatype: 
declare @tempDate char(22), @newDate datetime 
select @tempDate = substring([FileDate],5,4) + '-' + substring([FileDate],1,2) + '-' + substring([FileDate],3,2) + ' ' + '00:00:00.000' FROM [dbo].[YourTableName] 
select @newDate = (select (cast((@tempDate) as datetime))) 

October 4, 2008

sp_MSforeachdb


sp_MSforeachdb is another useful undocumented Stored Procedure provided by Microsoft.

Although it comes bundled with SQL Server, it is not documented in Books Online.  

The  sp_MSforeachdb   stored procedure resides in the master database, belongs to the sys schema and is used to process T-SQL commands against each database in the server.

Here's a T-SQL example to change the DB owner in all databases to "sa":

exec master..sp_MSForeachdb 'if "?" in ("master","model","msdb","tempdb","distribution") return use ?
print "Database = "+db_name()
EXEC sp_changedbowner "sa"'
GO

sp_MSforeachdb syntax:
exec @RETURN_VALUE=sp_MSforeachdb @command1, @replacechar, @command2, @command3, @precommand, @postcommand

sp_MSforeachdb options:  
  • @RETURN_VALUE - return value set by sp_MSforeachdb
  • @command1 - the first command that is processed against all databases. nvarchar(2000)
  • @replacechar - the character that becomes replaced by the database name. The default is ‘?’
  • @command2 - a second command that can also be processed against all database. nvarchar(2000)
  • @command3 - a third command that can also be processed against all databases. nvarchar(2000)
  • @precommand - parameter for a command to run before all database processing begins. nvarchar(2000)
  • @postcommand - parameter for a command to run after all database processing is completed. nvarchar(2000)
Execute this T-SQL code to analyze how sp_MSforeachdb works:
sp_helptext ’sp_MSforeachdb’