All posts by Basavaraj Biradar

How to find all dependencies of a table in Sql Server?

First of all we shouldn’t use the system stored procedure sp_depends for this as it is not reliable. For more details on sp_depends you can refer to the article sp_depends results are not reliable.

For this in Sql server 2008 new Dynamic Management Function sys.dm_sql_referencing_entities is introduced. This dynamic management function provides all the entities in the current database that refer to the specified table.

Let us understand this with an example. First create a table, then a stored procedure which references the table.

CREATE DATABASE DemoSQLHints
 GO
 USE DemoSQLHints
 GO
CREATE TABLE dbo.Employee
 (Id int IDENTITY(1,1), FirstName NVarchar(50), LastName NVarchar(50))
GO
 INSERT INTO dbo.Employee(FirstName, LastName)
 VALUES('BASAVARAJ','BIRADAR')
GO
CREATE PROCEDURE dbo.GetEmployeeDetails
 AS
 BEGIN
 SELECT * FROM dbo.Employee
 END
GO

Now we can use a script like below to find all entities in the current database that refer to the table dbo.Employee:

SELECT referencing_schema_name, referencing_entity_name, 
 referencing_id, referencing_class_desc
FROM sys.dm_sql_referencing_entities ('dbo.Employee', 'OBJECT')
GO

Result:
dm_sql_referencing_entities
Note: 1) This Dynamic Management Function is introduced as a part of Sql Server 2008. So above script works in Sql Server version 2008 and above.
2) While specifying the table name please include schema name also, otherwise result will not display the dependencies.

You may also like to read my other articles:

How to find all the objects referenced by the stored procedure in Sql Server?

Please correct me if my understanding is not correct. Comments are always welcome.

How to find referenced/dependent objects (like Table, Function etc) of a Stored Procedure/Function in Sql Server?

First of all we shouldn’t use the system stored procedure sp_depends for this as it is not reliable. For more details on sp_depends you can refer to the article sp_depends results are not reliable.

For this in Sql server 2008 new Dynamic Management Function sys.dm_sql_referenced_entities is introduced. This dynamic management function provides all the entities in the current database which are referenced by a stored procedure or function.

Let us understand this with an example. First create a table, then a stored procedure which references the table.

CREATE DATABASE DemoSQLHints
 GO
 USE DemoSQLHints
 GO
CREATE TABLE dbo.Employee
( Id int IDENTITY(1,1), 
  FirstName NVarchar(50), 
  LastName NVarchar(50)
)
GO
 INSERT INTO dbo.Employee(FirstName, LastName)
 VALUES('BASAVARAJ','BIRADAR')
GO
CREATE PROCEDURE dbo.GetEmployeeDetails
 AS
 BEGIN
 SELECT * FROM dbo.Employee
 END
GO

Now we can use a script like below to find all the entities in the current database which are referenced by the stored procedure dbo.GetEmployeeDetails

SELECT referenced_schema_name, referenced_entity_name, 
 referenced_minor_name
FROM sys.dm_sql_referenced_entities('dbo.GetEmployeeDetails', 
          'OBJECT')
GO

Result:
dm_sql_refererenced_entities
Note: 1) This Dynamic Management Function is introduced as a part of Sql Server 2008. So above script works in Sql Server version 2008 and above.
2) While specifying the stored procedure name please include schema name also, otherwise referenced objects list will not be displayed.

You may also like to read my other articles:
How to find all dependencies of a table in Sql Server?

Please correct me if my understanding is not correct. Comments are always welcome.

sp_depends results are not reliable

sp_depends System Stored procedure will give the list of referencing entities for a table/view and list of referenced entities by the stored procedure/view/function. As per MSDN this system stored procedure is going to be removed from the future versions of the Sql Server.

sp_depends results are not always reliable/accurate/correct. Good and reliable alternative for sp_depends are the DMV’s: sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities.

Let us see this with an example the incorrect results returned by the sp_depends system stored procedure:

CREATE DATABASE DemoSQLHints
GO
USE DemoSQLHints
GO
CREATE PROCEDURE dbo.GetEmployeeDetails
AS
BEGIN
	SELECT * FROM dbo.Employee
END

GO

CREATE TABLE dbo.Employee
(Id int IDENTITY(1,1), FirstName NVarchar(50), LastName  NVarchar(50))

GO
INSERT INTO dbo.Employee(FirstName, LastName)
	VALUES('BASAVARAJ','BIRADAR')

GO
-- Get the list of objects which are referring Employee table using
sp_depends [dbo.Employee]
GO
-- Get the list of objects referenced by the SP:GetEmployeeDetails
sp_depends [dbo.GetEmployeeDetails]
GO

Result

Object does not reference any object, and no objects reference it.
Object does not reference any object, and no objects reference it.

Both the sp_depends statements in the above script are not returning the referencing/referenced objects list. For example the stored procedure dbo.GetEmployeeDetails is referring the Employee table but sp_dpends is not providing this dependency information. Reason for this is: Stored procedure dbo.GetEmployeeDetails which is refereeing to this Employee table is created first and then the employee table in other words we call it as deferred resolution.

Solution to this problem is to use the DMV’s: sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities. Now let us check whether we are able to get the expected results using these DMV’s:

SPDependsAlternative

Note: These two DMV’s are introduced as a part of Sql Server 2008. So this alternate solution works for Sql Server version 2008 and above.

You may also like to read my other article: 

Please correct me if my understanding is not correct. Comments are always welcome.

How to Insert Stored Procedure result into a table in Sql Server?

Today, I was needed to dump the Stored Procedure result into a temp table.  Below example illustrates how this can be achieved:

First let us create a stored stored procedure which returns result set:

CREATE DATABASE DEMOSQLHINTS
GO
USE DEMOSQLHINTS
GO
CREATE TABLE dbo.Employee(ID INT IDENTITY(1,1),Name VARCHAR(50))

INSERT INTO dbo.Employee(Name)
VALUES('BASAVARAJ BIRADAR'),
('SHREE BIRADAR')
GO

CREATE PROCEDURE dbo.GetEmployees
AS
BEGIN
SELECT * FROM dbo.Employee WITH(NOLOCK)
END
GO
EXEC dbo.GetEmployees
GO

Result:

ID Name
———– ————————————
1 BASAVARAJ BIRADAR
2 SHREE BIRADAR

(2 row(s) affected)

Below script shows how to move the result of the SP GetEmployees to a temporary table #TempEmployees

-- First create the table to which we want to push the SP result
CREATE TABLE #TempEmployees(EmpId int,EmpName Varchar(50))
GO
-- Insert result from the SP to temp table
INSERT INTO #TempEmployees
EXEC dbo.GetEmployees
GO
--Verify the Insert records
SELECT * FROM #TempEmployees

Result:

EmpId EmpName
———– ————————————————–
1 BASAVARAJ BIRADAR
2 SHREE BIRADAR

Let us see another example where we Insert result from a Stored Procedure with Parameter into a temporary table

--First create a stored procedure with parameter
CREATE PROCEDURE GetEmployeeDetails
(@id AS INT)
AS
BEGIN
SELECT * FROM dbo.Employee (NOLOCK)
WHERE Id = @id
END
GO
--Execute SP with parameter and dump the result into a temp table
INSERT INTO #TempEmployees
EXEC GetEmployeeDetails 2

GO
-- Verify the inserted records
SELECT * FROM #TempEmployees


Result:

EmpId EmpName
———– ————————-
2 SHREE BIRADAR

Note: All the examples in this article are tested on Sql Server 2008 version

SEQUENCE IN SQL SERVER 2012

This article basically focuses towards the introduction of Sequences with extensive list of examples. To know about Sequence Limitations and Sequence Cache Management and internals you may like to visit the articles: Sequence Limitations and restrictions with Extensive List of examples and Sequence Cache Management and Internals with examples.

SEQUENCE is one of the new feature introduced in Sql Server 2012. Sequence is a user-defined object and as name suggests it generates sequence of numeric values according to the properties with which it is created. It is similar to Identity column, but there are many difference between them. Some of the major differences between them are:

  • Sequence is used to generate database-wide sequential number, but identity column is tied to a table.
  • Sequence is not associated with a table.
  • Same sequence can be used in multiple tables.
  • It can be used in insert statement to insert identity values, it can also be used in T-Sql Scripts.

[ALSO READ] Sequence Vs Identity

Now let us understand the sequence concept using below extensive list of examples:

Create a Sequence with Initial value 1 and Increment by 1

CREATE SEQUENCE [DBO].[SequenceExample] AS INT
 START WITH 1
 INCREMENT BY 1
GO

Using Sequence in an Insert Statement

CREATE TABLE dbo.Employee(ID INT,Name VARCHAR(100))
GO
--Insert records into Employee table with Sequence object
INSERT INTO dbo.Employee VALUES
(NEXT VALUE FOR DBO.SequenceExample,'BASAVARAJ BIRADAR'), 
(NEXT VALUE FOR DBO.SequenceExample,'SHREE BIRADAR'),
(NEXT VALUE FOR DBO.SequenceExample,'PRATHAM BIRADAR')
GO
-- CHECK THE RECORDS INSERTED IN THE TABLE
SELECT * FROM dbo.Employee WITH(NOLOCK)
GO

Result:
ID Name
———– —————————–
1 BASAVARAJ BIRADAR
2 SHREE BIRADAR
3 PRATHAM BIRADAR

Associate Sequence object to a table

CREATE TABLE dbo.Customer
(ID INT DEFAULT(NEXT VALUE FOR DBO.SequenceExample),
                               Name VARCHAR(100))
GO
INSERT INTO dbo.Customer(Name)
VALUES('PINKU BIRADAR'),
('MONTY BIRADAR')
GO
-- CHECK THE RECORDS INSERTED IN THE TABLE
SELECT * FROM dbo.Customer WITH(NOLOCK)
GO

Result:
ID Name
———– —————————–
4 PINKU BIRADAR
5 MONTY BIRADAR

[ALSO READ]: SEQUENCE Limitations and Restrictions

Getting Next Sequence Value in A SELECT Statement

SELECT (NEXT VALUE FOR DBO.SequenceExample) 
                            AS SequenceValue
GO 3

Note: Here GO 3 statement executes the statement above it 3 times.

Result:
Beginning execution loop
———–
6
(1 row(s) affected)

———–
7
(1 row(s) affected)

———–
8
(1 row(s) affected)
Batch execution completed 3 times.

[ALSO READ]: Sequence Cache management and Internals

Getting Sequence Next Value in a variable

DECLARE @EmpID AS INT 
        = NEXT VALUE FOR DBO.SequenceExample
SELECT @EmpID AS 'Employee Id'

Result:
Employee Id
———–
9

Re-Setting the Sequence Number

ALTER SEQUENCE DBO.SequenceExample
RESTART WITH 1 ;
GO
-- Verify whether sequence number is re-set
SELECT (NEXT VALUE FOR DBO.SequenceExample) 
                           AS SequenceValue
GO

Result:

SequenceValue
———–
1

How to get the current value of the Sequence

SELECT Current_Value 
FROM SYS.Sequences 
WHERE name='SequenceExample'
GO

Result:
Current_Value
———————————
1

Create Sequence Syntax:

CREATE SEQUENCE [schema_name . ] sequence_name
 [ AS [built_in_integer_type | user-defined_integer_type]]
 [ START WITH  ]
 [ INCREMENT BY  ]
 [ { MINVALUE [  ] } | { NO MINVALUE } ]
 [ { MAXVALUE [  ] } | { NO MAXVALUE } ]
 [ CYCLE | { NO CYCLE } ]
 [ { CACHE [  ] } | { NO CACHE } ]
 [ ; ]

Where:
Start with:             the initial value to start with sequence.
Increment by:   the step by which the values will get incremented or decremented.
Minvalue:              the minimum value of the sequence.
Maxvalue:             the maximum value of the sequence.
Cycle / No Cycle:  to recycle the sequence once it reaches to the maximum or minimum (if increment by is a negative number).
Cache / No Cache:  to pre-allocate the number of sequences specified by the given value.

[ALSO READ]:
SEQUENCE Limitations and Restrictions
Sequence Cache management and Internals