SQL SERVER – Get Directory Structure using Extended Stored Procedure xp_dirtree

Many years ago I wrote article SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server where I demonstrated using undocumented Stored Procedure to find the drive letter in local system and available free space. I received question in email from reader asking if there any way he can list directory structure within the T-SQL. When I inquired more he suggested that he needs this because he wanted set up backup of the data in certain structure.

Well, there is one undocumented stored procedure exists which can do the same. However, please be vary to use any undocumented procedures.

xp_dirtree 'C:\Windows'

Execution of the above stored procedure will give following result. If you prefer you can insert the data in the temptable and use the same for further use.

Here is the quick script which will insert the data into the temptable and retrieve from the same.

CREATE TABLE #TempTable (Subdirectory VARCHAR(512), Depth INT);
INSERT INTO #TempTable (Subdirectory, Depth)
EXEC xp_dirtree 'C:\Windows'
SELECT Subdirectory, Depth
FROM #TempTable;
DROP TABLE #TempTable;

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Question to You – When to use Function and When to use Stored Procedure

This week has been very interesting week. I have asked few questions to users and have received remarkable participation on the subject.

Q1) SQL SERVER – Puzzle – SELECT * vs SELECT COUNT(*)

Q2) SQL SERVER – Puzzle – Statistics are not Updated but are Created Once

Keeping the same spirit up, I am asking the third question over here.

Q3) When to use User Defined Function and when to use Stored Procedure in your development?

Personally, I believe that they are both different things ‑ they cannot be compared. I can say, it will be like comparing apples and oranges. Each has its own unique use. However, they can be used interchangeably at many times and in real life (i.e., production environment). I have personally seen both of these being used interchangeably many times. This is the precise reason for asking this question.

When do you use Function and when do you use Stored Procedure? What are Pros and Cons of each of them when used instead of each other?

If you are going to answer that ‘To avoid repeating code, you use Function’ ‑ please think harder! Stored procedure can do the same. In SQL Server Denali, even the stored procedure can return the result just like Function in SELECT statement; so if you are going to answer with ‘Function can be used in SELECT, whereas Stored Procedure cannot be used’ ‑ again think harder! (link).

Now, what do you say? I will post the answers of all the three questions with due credit next week.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2012 – Executing Stored Procedure with Result Sets

Here is a normal conversation I heard when I saw that the function (UDF) was used instead of the procedure (SP).

Q: Why are you using User Defined Function instead of Stored Procedure?

A: I cannot SELECT from SP, but I can from UDF.

SQL Server’s next version ‘Denali’ is coming up with a very interesting feature called WITH RESULT SET.

Using this feature, you can run the stored procedure and rename the columns used in it. The usual procedure of creating TempTable, executing the stored procedure and inserting the data into the TempTable may be time-consuming, that is why Denali introduced the WITH RESULT SET feature. This feature enables you to select the data without using the TempTable. You can rename the columns during the run time as well.

Here is the quick script:

USE AdventureWorks2008R2
GO
CREATE PROCEDURE mySP (@ShiftID INT)
AS
SELECT
[ShiftID]
,[Name]
,[StartTime]
,[EndTime]
,[ModifiedDate]
FROM [HumanResources].[Shift]
WHERE [ShiftID] = @ShiftID
GO
-- Executing Stored Procedure
EXEC mySP @ShiftID = 2
WITH RESULT SETS
(
(
[ShiftID] TINYINT
,[Name] NVARCHAR(50)
,
[StartTime] DATETIME
,[EndTime] DATETIME
,[UpdateDate] DATETIME -- Notice Name Change
)
);

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – System Stored Procedure sys.sp_tables

I have seen people running the following script quite often, to know the list of the tables from the database:

SELECT *
FROM sys.tables
GO

The script above provides various information from create date to file stream, and many other important information. If you need all those information, that script is the one for you. However, if you do not need all those information, I suggest that you run the following script:

EXEC sys.sp_tables
GO

The script above will give all the tables in the table with schema name and qualifiers. Additionally, this will return all the system catalog views together with other views. This Stored Procedure returns all the tables first in the result set, followed by views.

Even though Stored Procedure returns more numbers of rows, it still performs better than the sys.table query.

Let us verify it with two different methods for database AdventureWorks:

1) SET STATISTICS IO ON

USE AdventureWorks
GO
SET STATISTICS IO ON
SELECT
*
FROM sys.tables
GO
EXEC sys.sp_tables
GO

(81 row(s) affected) (This is for sys.tables)
Table ‘syspalvalues’. Scan count 0, logical reads 162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘syssingleobjrefs’. Scan count 0, logical reads 324, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘sysidxstats’. Scan count 81, logical reads 168, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘syspalnames’. Scan count 0, logical reads 162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘sysschobjs’. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(456 row(s) affected) (This is for sys.sp_tables)
Table ‘sysobjrdb’. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘sysschobjs’. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

2) Execution Plan

You can see that the execution plan for sys.table has much higher cost of query batch.

Well, if you only need to know the name of the tables, I suggest that you start using SP_TABLES; at least it takes less typing to do.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Encrypted Stored Procedure and Activity Monitor

I recently had received question if any stored procedure is encrypted can we see its definition in Activity Monitor.

No.

Let us do quick test. Let us create following Stored Procedure and then launch Activity Monitor and check the text.

USE AdventureWorks
GO
-- Create Encyrpted SP
CREATE PROCEDURE uspEnc
WITH ENCRYPTION
AS
SELECT
*
FROM Sales.SalesOrderDetail
UNION
SELECT
*
FROM Sales.SalesOrderDetail
GO
-- Execute SP
EXEC uspEnc
GO

You will be not able to see the text of the encrypted Stored Procedure of the SQL Server.

What do you think? Is there any other way we can approach this?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Happy Birthday to you!

SQL SERVER – Get Numeric Value From Alpha Numeric String – Get Numbers Only

I have earlier wrote article about SQL SERVER – Get Numeric Value From Alpha Numeric String – UDF for Get Numeric Numbers Only and it was very handy tool for me. Recently blog reader and SQL Expert Christofer has left excellent improvement to this logic. Here is his contribution. He has provided Stored Procedure and the same can be easily converted to Function.

CREATE PROCEDURE [dbo].[CleanDataFromAlpha]
@alpha VARCHAR(50),
@decimal DECIMAL(14, 5) OUTPUT
AS BEGIN
SET NOCOUNT ON
;
DECLARE @ErrorMsg VARCHAR(50)
DECLARE @Pos INT
DECLARE
@CommaPos INT
DECLARE
@ZeroExists INT
DECLARE
@alphaReverse VARCHAR(50)
DECLARE @NumPos INT
DECLARE
@Len INT
-- 1 Reverse the alpha in order to get the last position of a numeric value
SET @alphaReverse = REVERSE(@alpha)
-- 2 Get the last position of a numeric figure
SET @NumPos = PATINDEX('%[0-9]%', @alphaReverse)
-- 3 Get the lenght of the string
SET @Len = LEN(@alpha)
-- 4 Add a comma after the numeric data in case it's no decimal number
SET @alpha = SUBSTRING(@alpha, 1, (@Len - @NumPos + 1))
+
','
+ SUBSTRING(@alpha, (@Len - @NumPos + 2), 50)
-- Check if there is a zero (0) in the @alpha, then we later set the @decimal to 0
-- if it's 0 after the handling, else we set @decimal to NULL
-- If 0 no match, else there is a match
SET @ZeroExists = CHARINDEX ( '0' , @alpha ,1 )
-- Find position of , (comma)
SET @CommaPos = 1
SET @CommaPos = PATINDEX('%,%', @alpha)
IF (@CommaPos = '') BEGIN
SET
@CommaPos = 20
END
SET
@Pos = PATINDEX('%[^0-9]%',@alpha)
-- Replaces any aplha with '0' since we otherwice can't keep track of where the decimal
-- should be put in. We assume the numeric number has no aplhe inside. The regular way
-- to solve this is to replace with ”, but then we miss the way to find the place to
-- put in the decimal.
WHILE (@Pos > 0) BEGIN
SET
@alpha = STUFF(@alpha, @pos, 1, '0')
SET @Pos = PATINDEX('%[^0-9]%',@alpha)
END
IF
(@alpha IS NOT NULL AND @alpha != '') BEGIN
SET
@decimal = CONVERT(DECIMAL(14, 5), SUBSTRING(@alpha, 1, (@CommaPos - 1))
+
'.'
+ SUBSTRING(@alpha, (@CommaPos + 1), 20))
END
-- Since we in this case don't want to set 0 if where is no numeric value, we set NULL to be safe
IF (@decimal = 0 AND @ZeroExists = 0) BEGIN
SET
@decimal = NULL
END
END
GO

If you run above SP as shown below it will work
DECLARE @myRetVal DECIMAL(14,5)
EXEC [CleanDataFromAlpha] 'ABC355,88ghf', @myRetVal OUTPUT
SELECT @myRetVal ReturnValue

ReturnValue
---------------------------------------
355.88000

(1 row(s) affected)

I once again want to thanks Christofer for his excellent contribution.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Check Advanced Server Configuration

I was recently asked following question:

“How I check all the advanced configuration of the SQL Server?”

EXEC sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure;

Running above script will return all the advanced server configurations.

Reference: Pinal Dave (http://blog.sqlauthority.com)