Feeds:
Posts
Comments

Archive for the ‘Solutions’ Category

SQL SERVER – Get the first letter of each word in a String (Column)

Posted in Solutions, SQL SERVER, tagged , , , on April 12, 2013| 6 Comments »

String manipulation in a different format is one of my favorite activities. Today, I came across a question in a forum, how to get the first letter of each word in a column. Obviously, my primary search was the internet, where I found few solutions, but all of them were loop based. Hence  developed a solution without loop.

Give below is the script :

--Create a Sample
Create Table Student
(
[Student ID] int Identity(1,1),
[Student Name] varchar(50)
)
Go
Insert into Student Values ('Steve Masters Bob')
Insert into Student Values ('David Ortiz')
Insert into Student Values ('Michael Sean Ray')
Insert into Student Values ('Steven SElikoff')
Insert into Student Values ('Carole POLAND')
Insert into Student Values ('Bjorn Rettig')
Insert into Student Values ('Michiko OSAda')
Insert into Student Values ('CarOL Philips')
Insert into Student Values ('Merav Netz')
GO

--Create the procedure to get the first letter of each word.
Create Function dbo.[UDF_FIRST_LETTER_FROM_WORD]
(
@String Varchar(Max) -- Variable for string
)
RETURNS Varchar(Max)
BEGIN
Declare @Xml Xml
Declare @firstletter Varchar(Max)
Declare @delimiter Varchar(5)

SET @delimiter=' '
SET @Xml = cast(('<a>'+replace(@String,@delimiter,'</a><a>')+'</a>') AS XML)

;With CTE AS (SELECT A.value('.', 'varchar(max)') as [Column]
FROM @Xml.nodes('a') AS FN(a) )
SELECT @firstletter =Stuff((SELECT '' + LEFT([Column],1)
FROM CTE
FOR XML PATH('') ),1,0,'')

RETURN (@firstletter)
END
GO

Example 1 :
Given below script will get the first letter of each word from a column of a table.

SELECT [Student Name],
dbo.[UDF_FIRST_LETTER_FROM_WORD] ([Student Name]) as [First Letters]
FROM Student
GO

firstletterfromword1.1

Example 2 :
Given below script will get the first letter of each word from a string.

Declare @String as varchar(100)
Set @String ='My Best Friend'
SELECT @String as [String]
, dbo.[UDF_FIRST_LETTER_FROM_WORD] (@String) as [First Letters]

firstletterfromword1.2

Read Full Post »

SQL SERVER – Shorter way to convert Seconds to Days, Hours, Minutes, Seconds

Posted in Solutions, SQL SERVER, tagged , , , on April 10, 2013| 6 Comments »

Today, I came across this question in a forum – How to convert seconds to Day(s), Hour(s), Minute(s), Second(s)? For this we need to do a lot of divisions and remainders to convert Seconds to Day, Hour, Minute, Seconds. Given below is the simple script to convert it without doing any divisions and remainders, using only built-in SQL functions DateAdd, DatePart, DateDiff

--This script will work on SQL Server 2005 and above.
Declare @Seconds as int
Declare @SubtractDate as datetime
--Enter Number of Seconds here
Set @Seconds=9974501
Set @SubtractDate=DateAdd(s,@Seconds,getdate()) - Getdate()

Select Convert(varchar(10),DateDiff(day,'1900-01-01',@SubtractDate))
+ ' Day(s) ' +
Convert(varchar(10),DatePart(hh,@SubtractDate))
+ ' Hour(s) ' +
Convert(varchar(10),DatePart(mi,@SubtractDate))
+ ' Minute(s) ' +
Convert(varchar(10),DatePart(ss,@SubtractDate))
+ ' Second(s) ' AS [Result]
--OUTPUT

Result
——————————————————————————-
115 Day(s) 10 Hour(s) 41 Minute(s) 41 Second(s)

(1 row(s) affected)

Read Full Post »

SQL SERVER 2012- Shortcut – How to find all system objects related to any file table

Posted in Solutions, SQL SERVER, tagged , , , , , on April 9, 2013| 2 Comments »

File table is one of my favorite features introduced in SQL Server 2012. When I was working with file table, I came across a question how to find all system objects (primary key, default vaule, indexes etc) related to any file table.

We can achieve this by two different methods.

 Method 1:

In this method, we did self-join in the sys.objects to find all system objects related to any file table. This is general script that we normally use to find any child object related to parent object.

--This script will work in SQL Server 2012 and above.
Declare @FileTableName as varchar(50)='dbo.Databank'
-- Enter File table Name Here
Select B.[name] as [File Table Name]
,A.[name] as [Related objects]
from sys.objects A
Inner Join sys.objects B
On A.[parent_object_id] =B.[object_id]
Where B.[type] ='U'
And B.object_id=object_id(@FileTableName)
GO
--OUTPUT

filetablesystemobjects1.1

 Method 2 (Shortcut):

In this method, we used a new system view namely sys.filetable_system_defined_objects (This system view returns all the system objects related to any file table) introduced in SQL Server 2012.
Given below is the script that will give you all system objects related to file table without any self-join.

--This script will work in SQL Server 2012 and above.
Declare @FileTableName as varchar(50)='dbo.Databank'
Select object_name(parent_object_id) as [File Table Name]
,object_name(object_id) as [Related objects]
from sys.filetable_system_defined_objects
Where parent_object_id =object_id(@FileTableName)
GO
--OUTPUT

filetablesystemobjects1.2

Aware of any other shortcut ?

Read Full Post »

SQL SERVER – Finding Size of a Columnstore Index on disk

Posted in Solutions, SQL SERVER, tagged , , , , on April 6, 2013| Leave a Comment »

Few days ago, I was working on columnstore Index and I came across a problem finding the size of all columnstore Indexes on disk in a particular database.
I have given below the script which can help you find the size of a single columnstore index on disk or all columnstore indexes in a particular database by using
sys.column_store_segments, sys.column_store_dictionaries.

Use AdventureWorks2012
Go
Declare @TableName as nvarchar(Max)
--If you need columnstore index size for one table
--Change the @TableName parameter from NULL to table name
Set @TableName =NULL --'SalesOrderDetail_Sample'

;With CTE AS (
SELECT i.object_id
,i.name as [Index_Name]
,SUM(c.on_disk_size)/(1024.0*1024)
As [Columstore_Index_size_on_disk_size (In MBs)]
FROM sys.indexes AS i
JOIN sys.partitions AS p
ON i.object_id = p.object_id
JOIN sys.column_store_segments AS c
ON c.hobt_id = p.hobt_id
WHERE i.type_desc = 'NONCLUSTERED COLUMNSTORE'
AND ((1=(CASE WHEN @TableName is Null THEN 1 ELSE 0 END)
OR i.object_id = object_id(@TableName)))
GROUP BY i.object_id,i.name

UNION ALL

SELECT i.object_id
,i.name as [Index_Name]
,SUM(c.on_disk_size)/(1024.0*1024)
As [Columstore_Index_size_on_disk_size (In MBs)]
FROM sys.indexes AS i
JOIN sys.partitions AS p
ON i.object_id = p.object_id
JOIN sys.column_store_dictionaries AS c
ON c.hobt_id = p.hobt_id
WHERE i.type_desc = 'NONCLUSTERED COLUMNSTORE'
AND ((1=(CASE WHEN @TableName is Null THEN 1 ELSE 0 END)
OR i.object_id = object_id(@TableName)))
GROUP BY i.object_id,i.name )

Select object_id,object_name(object_id) as [Table_Name]
,[Index_Name]
,SUM([Columstore_Index_size_on_disk_size (In MBs)])
AS [Columstore_Index_size_on_disk_size (In MBs)] from CTE
Group By object_id,[Index_Name]

Columnstoreindexsize1.1

Reference : MSDN

Read Full Post »

SQL SERVER 2012 – How to find list of contained databases

Posted in Solutions, SQL SERVER, tagged , , , on April 4, 2013| Leave a Comment »

Contained databases are one of the favorite features introduced in SQL Server 2012. Contained databases are isolated / independent databases from the other databases and from the instance it is hosted on.

Today, we will go through a very simple script related to contained databases and in this script we need to find the list of all contained databases only. Given below is the script.

Select name as [Database Name], containment ,containment_desc
from sys.databases
Where [containment] =1
--OUTPUT

listcontained DB1.2

Read Full Post »

SQL SERVER 2012 – Get Day, Month and Year separately from Date Time Using Format Function

Posted in Solutions, SQL SERVER, tagged , , , on April 3, 2013| 2 Comments »

How to get day, month and year from Date Time? I came across this question many times in multiple blogs and there are many ways to do it in the earlier versions of SQL Server. In SQL Server 2012, you can also do it using FORMAT function (Method 3).
Given below are different methods to get day, month and year from date time which are compatible with different versions, including SQL Server 2012. Ensure that you are using correct method in your SQL Server version.

Method 1:

--This method will work on SQL SERVER 2005 and above
DECLARE @Date_Time DATETIME
SET @Date_Time = '2013-12-31 10:49:59.460'

SELECT DATEPART(DAY, @Date_Time) as [Day],
DATEPART(MONTH, @Date_Time) as [Month],
DATEPART(YEAR, @Date_Time) as [Year]
--OUTPUT

getdaymonthyear
Method 2:

--This method will work on SQL SERVER 2005 and above
DECLARE @Date_Time DATETIME
SET @Date_Time = '2013-12-31 10:49:59.460'
SELECT DAY(@Date_Time) as [Day],
MONTH(@Date_Time) as [Month],
YEAR(@Date_Time) as [Year]
--OUTPUT

getdaymonthyear1.1
Method 3:

--This method will work on SQL SERVER 2012 and above
DECLARE @Date_Time DATETIME
SET @Date_Time = '2013-12-31 10:49:59.460'
SELECT FORMAT(@Date_Time,'dd') as [Day],
FORMAT(@Date_Time,'MM') as [Month],
FORMAT(@Date_Time,'yyyy') as [Year]
--OUTPUT

getdaymonthyear1.2

Read Full Post »

SQL SERVER – How to convert Hashbytes to varchar

Posted in Solutions, tagged , , , on April 2, 2013| 3 Comments »

How to convert Hasbytes to varchar ? I came across this question recently when I was working on my previous article. Then I started with my research to develop a solution.

Given below is the script that can convert Hashbytes to Varchar :

DECLARE @varchar varchar(Max);
DECLARE @hashbytes varbinary(20)

-- Convert 'raresql' string into Hasbytes
SET @hashbytes=HASHBYTES('SHA1','raresql');
-- Select Hasbytes value
Select @hashbytes as Hasbytes

--Convert varbinary value to varchar value
Set @varchar='0x' + cast('' as xml).value
('xs:hexBinary(sql:variable("@hashbytes"))', 'varchar(max)');

--Select varchar value
Select @varchar as [Varchar]

hasbytestovarchar1.1

Read Full Post »

SQL SERVER – Multiple ways to calculate the number of days in a Year

Posted in Solutions, SQL SERVER, tagged , , , on March 27, 2013| 1 Comment »

How to calculate the number of days in a year ? Was a question I came across and began with my usual research for the solution. Given below are few methods.

Method 1 :
In this method, I calculated the no of days in a year using the days difference between 1 Jan of the current year and the next year.
Given below are the scripts.

--This method will work on SQL Server 2005 and above.
DECLARE @year AS INT
SET @year=2012
Select DATEDIFF(DAY,DATEADD(YEAR,@year-1900,0)
,DATEADD(YEAR,@year-1900+1,0)) AS [TOTAL NO OF DAYS]
GO
--This technique is same as above but using new datefromparts
--function introduced in SQL Server 2012.
--This technique will work in SQL Server 2012 and above.

DECLARE @Year INT =2012
SELECT DATEDIFF(DAY,DATEFROMPARTS(@Year,1,1)
, DATEFROMPARTS(@year+1,1,1)) AS [TOTAL NO OF DAYS]
GO

Method 2 :
In this method, I checked whether February had 28 days or 29. Obviously, if 29 days it means total no of days in a year is 366 else 365.
Given below are the scripts.

--This method will work on SQL Server 2005 and above.
DECLARE @year AS INT
SET @year=2012
SELECT (Case When Day(DATEADD(YEAR,@year-1900,0)+59)=29
Then 366 else 365 end) AS [TOTAL NO OF DAYS]
GO
--This technique is same as above but using new datefromparts and
--EOMONTH functions introduced in SQL Server 2012.
--This technique will work in SQL Server 2012 and above.
DECLARE @year AS INT
SET @year=2012
SELECT (Case When  DAY(EOMONTH(DATEFROMPARTS(@Year,2,1)))=29
Then 366 else 365 end) AS [TOTAL NO OF DAYS]

Method 3 :
In this method, I checked the day of last date of the year e.g 31st Dec 2012.

--This method will work on SQL server 2005 and above.
DECLARE @year AS INT
SET @year=2012
Select DATEPART(dy,(DATEADD(YEAR,@year-1899,0)-1))
AS [TOTAL NO OF DAYS]

--This technique is same as above but using new datefromparts
--functions introduced in SQL Server 2012.
--This technique will work in SQL Server 2012 and above.
DECLARE @year AS INT
SET @year=2012
Select DATEPART(dy,DATEFROMPARTS(@Year,12,31))
AS [TOTAL NO OF DAYS]

Conclusion :
We have multiple ways to calculate the number of days but I recommend to use the Method 3, as it has shortest way to calculate the number of days in a year.

Let me know if you have a better way to achieve it.

Read Full Post »

SQL SERVER – Find tables across the databases

Posted in Solutions, SQL SERVER, tagged , , , , , on March 25, 2013| Leave a Comment »

There are various methods to find table across the database but I was keen on a solution to find a table across the database and if the table is not available in any database then the script must mention so.

Given below is the solution designed with the help of a sys.tables(system table).
Note : This solution is compatible with SQL SERVER 2005 and above.

Create Procedure Search_Table_Across_Databases_Proc
-- Script Name : Sp_Search_Table_Across_Databases
-- Script Type : Search Tables Across all SQL Server databases.
-- Developed By  : Muhammad Imran
-- Date Created: 25 Mar 2013
@TableName nvarchar(Max)
As
BEGIN
DECLARE @SQL nvarchar(MAX)     --Declare variable to store dynamic query result
DECLARE @DB_NAME nvarchar(200) --Declare variable to store Database name

SET @SQL=''

--Create table to store the result of each database
CREATE TABLE #SearchResult
(
[DB_Name] nvarchar(max),
[Table Name] nvarchar(max),
[Status] varchar(50)
)

--Declare cusrsor to loop across all databases
DECLARE DB_Cursor CURSOR FOR
--Pick the list of all database
SELECT QUOTENAME([name]) from sys.databases order by [name]

OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor INTO @DB_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
--In this dynamic query, two select statements are built because
--one statement will check the unavailability and the
--second statement will check the availability of the
--tables in the database and insert the result in the #SearchResult table

SET @SQL= 'Insert into #SearchResult
SELECT ''' + @DB_NAME + ''' as [Database Name],'''',''Not Available''
from ' + @DB_NAME + '. sys.tables
Where [name] =''' + @TableName + ''' Having Count(*)=0
UNION ALL
SELECT ''' + @DB_NAME + ''' as [Database Name],[name],''Available''
from ' + @DB_NAME + '.sys.tables
Where [name] =''' + @TableName + ''' Group By [name]
Having Count(*)>0'

EXEC(@SQL)

FETCH NEXT FROM DB_Cursor INTO @DB_NAME
END
CLOSE DB_Cursor
DEALLOCATE DB_Cursor
--We can browse the table to view the results
Select * from #SearchResult Order by [DB_Name],[Table Name]
END
GO
--Syntax
--EXEC Search_Table_Across_Databases_Proc 'Table Name'
--Example
EXEC Search_Table_Across_Databases_Proc 'employee'
--OUTPUT

searchtables1.1-1

Read Full Post »

SQL SERVER – Multiple ways to get Date and Time separately from DateTime

Posted in Solutions, SQL SERVER, tagged , , , on March 19, 2013| 1 Comment »

How to separate date and time from DateTime ? I came across this question many times in multiple blogs.

Given below are different methods to separate date and time from datetime which are compatible with different versions. Make sure you are using correct method in your SQL Server version.

Mehtod 1 :
This method will work under SQL Server 2000 and above.

Declare @Datetime as datetime
Set @Datetime =getdate()
Select @Datetime as [Datetime]
Select Convert(varchar(10),@Datetime,120) as [Date]
Select Convert(varchar(20),@Datetime,114) as [Time]
--OUTPUT

Datetime
———————–
2013-03-18 22:02:02.960

(1 row(s) affected)

Date
———-
2013-03-18

(1 row(s) affected)

Time
——————–
22:02:02:960

(1 row(s) affected)

Mehtod 2 :
This method will work under SQL Server 2008 and above.

Declare @Datetime as datetime
Set @Datetime =getdate()
Select @Datetime as [Datetime]

Select Convert(Date,@Datetime) as [Date]
Select Convert(Time,@Datetime) as [Time]
--OUTPUT

Datetime
———————–
2013-03-18 22:04:24.930

(1 row(s) affected)

Date
———-
2013-03-18

(1 row(s) affected)

Time
—————-
22:04:24.9300000

(1 row(s) affected)

Mehtod 3 :
This method will work in SQL Server 2012 and above.

Declare @Datetime as datetime
Set @Datetime =getdate()
Select @Datetime as [Datetime]

Select Format(@Datetime,'yyyy-mm-dd') as [Date]
Select Format(@Datetime,'hh:mm:ss tt') as [Time]
--OUTPUT

Datetime
———————–
2013-03-18 22:14:23.763

(1 row(s) affected)

Date
———————–
2013-14-18

(1 row(s) affected)

Time
———————–
10:14:23 PM

(1 row(s) affected)

Conclusion :
I have mentioned 3 different methods to separate date and time from datetime but if you have SQL Server 2008 and above, then I would recommend Method 2.The reason behind this recommendation is in Method 2 we just convert datetime into a date type and time type but in other methods we first convert datetime into date type and time type and then give a style as well.

Read Full Post »

« Newer Posts - Older Posts »