Get the object name from the fully qualified name


Howdy Everyone.

I have seen an interesting built in function of SQL Server today, It is PARSENAME.

The PARSENAME function will return the object/schema/database/server name from the given string.

For more information you can check MSDN article below

http://msdn.microsoft.com/en-us/library/ms188006.aspx

Advertisements

How to find the last day of the month


Howdy Everyone.

Most of the times I see, people getting up with new ideas to get the last day of the month…

But the simplest method we can use in SQL Server is by adding 1 to month and subtracting the number of days in the date, confusing….

Well let me explain with an example

Let us take the date 20-AUG-2011. Now if we add 1 to the month by using DATEADD function.. the result will be 20-SEP-2011. Now the day part of the resultant date is 20 and if we subtract the 20 from the resultant by using dateadd the result will be 30-AUG-2011.

The code is given below

DECLARE @input_date datetime
set @input_date = getdate() -- for testing purposes
select dateadd(dd,datepart(dd,@input_date)*-1,dateadd(mm,1,@input_date))

TSQL — Backup all your databases or selected databases


Howdy Everyone,

Today one of my friend asked me to take a look into his backup script that he is using, to take backup of  all databases in his testing environment.The script is hard coded with the database names (50 of them) and the poor fellow needs to maintain the script as well  add  / delete databases on the request of his team.

I advised my friend to change the script and use the dynamic SQL for this purpose and re-wrote the script for him.

I thought it might be helpful to some one like my dear friend. So I pasted the same in here as my new blog post.

How it Works?

There are few choices I have given to the users for this script

  1. The user can restrict the databases that are needed to be taken a backup
  2. The user should give the backup location in which the backup files needs to be placed
  3. The user can choose the backup file trailer incase if he wishes to add something there & if it is left blank current date in the format YYYYMMDD will be added to the end of the databasename.
What if my friend X above want to take a backup of only certain databases like 10 out of his 50 right now… I thought of that a wrote a lookup table for this (databases_To_be_backedUp).  Insert the database names (10 in my case) that needs to be backed up into the lookup table, so that it will take care of the backup part for you. (Follow Example 4)
All the backup files will be created with the extension of “BAK”
IF EXISTS ( SELECT 1 FROM SYSOBJECTS WHERE name = 'databases_To_be_backedUp' and xtype = 'U')
	DROP TABLE databases_To_be_backedUp
CREATE TABLE databases_To_be_backedUp
(
	ID				INT		Identity(1,1)
	,database_name	varchar(200)
)
GO

IF EXISTS ( SELECT 1 FROM SYSOBJECTS where name = 'backup_all_databases' and xtype = 'P')
	DROP PROCEDURE backup_all_databases
CREATE procedure backup_all_databases
(
	@backupName_trailer	varchar(20) = '' --- If the parameter is passed the backup names will have the trailer with this
										  -- else by default the proc will add the YYYYMMDD trailer.
	,@backupLocation	varchar(100)
)
AS
BEGIN
	declare @databaseName	Varchar(200)
			,@lookUpCount	int
			,@sql			varchar(1000)

	SELECT @lookUpCount = count(1)
	FROM databases_To_be_backedUp

	IF (@lookUpCount > 0) -- Some databases are inserted into the databases_To_be_backedUp, that means only these databases are needed to be backed up.
	BEGIN
		DECLARE database_cursor CURSOR
		FOR SELECT database_name FROM databases_To_be_backedUp ORDER BY ID
	END
	ELSE
	BEGIN
						  -- The databases_To_be_backedUp is empty so all the databases are needed to backed up.. apart from the system databases
						  -- In this script we are considering the MASTER, MODEL,TEMPDB, MSDB as system databases..
						  -- Incase if you want to change this change the not in parameter for the below select statement.
		DECLARE database_cursor CURSOR
		FOR select name from sys.databases where name not in('master','model','msdb', 'tempdb')
	END
	OPEN database_cursor
	FETCH database_cursor into @databaseName
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		Select @sql = 'BACKUP DATABASE ' + @databaseName + ' TO DISK = ' + '''' + @backupLocation + @databaseName
										+ CASE @backupName_trailer
												WHEN '' THEN convert(varchar(10),getdate(),112)
												ELSE @backupName_trailer
										  END
										+ '.bak'			-- extension  for the backup file
										+ ''''
										+ ' WITH INIT,SKIP'
		print @sql
		exec(@sql)
		FETCH database_cursor into @databaseName
	END
	CLOSE database_cursor
	DEALLOCATE database_cursor
END
GO

-- examples
-- 1 backup all your databases to d:\
exec backup_all_databases @backupLocation = 'D:\'

-- 2 backup with backup as a trailer in the file name
exec backup_all_databases @backupName_trailer = 'backup',@backupLocation = 'D:\'

-- 3 if you want to take backup of only choosen databases rather than every thing
create database a -- creates a temp database a
create database b -- creates a temp database b

insert into databases_To_be_backedUp(database_name) values ('a');
insert into databases_To_be_backedUp(database_name) values ('b');

exec backup_all_databases @backupLocation = 'D:\'

UDF to Split a delimited string and return it as a table


Howdy Every One

Each and everyday we might find out that we need a split a string and return it as a table with rows in SQL. There are many programming languages in which there are built in functions which will do this for you (javascript split). Now if you want the same functionality in SQL, the only choice left is to write your own UDF (as of SQL Server 2008 R2 version).

Below is code of the UDF that might help you out

CREATE FUNCTION dbo.Split
(
 @RowData nvarchar(2000),
 @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
 Id int identity(1,1),
 Data nvarchar(100)
)
AS
BEGIN
 Declare @Cnt int
 Set @Cnt = 1
 DECLARE @index INT
 SET @index = Charindex(@SplitOn,@RowData)
 While (@index>0)
 Begin
 Insert Into @RtnValue (data)
  Select
 Data = ltrim(rtrim(Substring(@RowData,1,@index-1)))

 Set @RowData = Substring(@RowData,@index+1,len(@RowData))
 Set @Cnt = @Cnt + 1
 SET @index = Charindex(@SplitOn,@RowData)
 End

 Insert Into @RtnValue (data)
 Select Data = ltrim(rtrim(@RowData))

 Return
END