Big Data, Data Analytics and Data Science | BI-BIGDATA.com

Couple of days back I got an opportunity to migrate the existing Sybase ASE database (Adaptive Server Enterprise/15.0.2) to my favorite MS SQL Server 2012, to find out the feasibility of migration activity in near future.

Our team consists of Project Manager, Database architect and few of the senior guys we all concluded to just migrate the database objects from Sybase to SQL Server 2012, and along with the migration we need to find out dead objects in the existing database which is being carried so far. Here the dead objects means such tables, columns and views which are no more in use.

On exploring the Sybase database I found few hundreds of Tables and indexes, couple of views and approximately 1500 stored procedures. After completion of migration, now the real challenge was to find out dead objects, and I spent some couple of hours to explore various ways to indentify objects and its dependents,  I got the sp_depends but still this was not the one I was in desperate need, and also this feature will be removed in a future version of Microsoft SQL Server. After spending few more minutes I found what I was looking for, and it is no other than sys.dm_sql_referenced_entities

The above is all about the task which takes me to explore these following, to understand the following let’s get our hand dirty

Here is small scenario to explain this feature

-- Create a new database
CREATE DATABASE TEST
Go

use Test
go

CREATE TABLE DBO.MYTABLE(ID INT,MYNAME VARCHAR(20), CONTACTNUMBER INT)
GO
INSERT INTO DBO.MYTABLE VALUES(1,'BOB', 12345),(2,'MIKE', 54321),(3,'JACK', 12321)
GO
CREATE TABLE DBO.MYADDRESS(ID INT,MYCITY VARCHAR(200))
GO
INSERT INTO DBO.MYADDRESS VALUES(1,'CHICAGO'),(2,'TEXAS'),(3,'NEW YORK')
GO

-- Stored Procedure to get details of all employees
CREATE PROCEDURE DBO.SP_GETALLEMPLOYEES AS
BEGIN
SELECT MYNAME,CONTACTNUMBER FROM DBO.MYTABLE
END
GO

-- Stored Procedure to get details of all employees addresses
CREATE PROCEDURE DBO.SP_GETALLEMPLOYEEADDRESS AS
BEGIN
SELECT T1.MYNAME, T2.MYCITY FROM DBO.MYTABLE T1 INNER JOIN DBO.MYADDRESS T2 ON T1.ID=T2.ID
END
GO

-- Stored Procedure to get details of all addresses
CREATE PROCEDURE DBO.SP_GETALLADDRESS AS
BEGIN
SELECT * FROM DBO.MYADDRESS
END
GO

-- Stored Procedure to update contact number of an employee
CREATE PROCEDURE DBO.SP_UPDATECONTACTNUMBER(@ID INT,@CONTACTNUMBER INT) AS
BEGIN
UPDATE DBO.MYTABLE SET CONTACTNUMBER=@CONTACTNUMBER WHERE ID=@ID
END
GO
----
SELECT REFERENCED_SCHEMA_NAME AS SCHEMA_NAME,
REFERENCED_ENTITY_NAME AS TABLE_NAME,
REFERENCED_MINOR_NAME AS REFERENCED_COLUMN,
IS_SELECTED IS_SELECTED,
IS_UPDATED IS_UPDATED,
IS_SELECT_ALL IS_SELECT_ALL
FROM
SYS.DM_SQL_REFERENCED_ENTITIES ('DBO.SP_GETALLADDRESS', 'OBJECT')

SELECT REFERENCED_SCHEMA_NAME AS SCHEMA_NAME,
REFERENCED_ENTITY_NAME AS TABLE_NAME,
REFERENCED_MINOR_NAME AS REFERENCED_COLUMN,
IS_SELECTED IS_SELECTED,
IS_UPDATED IS_UPDATED,
IS_SELECT_ALL IS_SELECT_ALL
FROM
SYS.DM_SQL_REFERENCED_ENTITIES ('DBO.SP_GETALLEMPLOYEEADDRESS', 'OBJECT')

SELECT REFERENCED_SCHEMA_NAME AS SCHEMA_NAME,
REFERENCED_ENTITY_NAME AS TABLE_NAME,
REFERENCED_MINOR_NAME AS REFERENCED_COLUMN,
IS_SELECTED IS_SELECTED,
IS_UPDATED IS_UPDATED,
IS_SELECT_ALL IS_SELECT_ALL
FROM
SYS.DM_SQL_REFERENCED_ENTITIES ('DBO.SP_UPDATECONTACTNUMBER', 'OBJECT')

SELECT REFERENCED_SCHEMA_NAME AS SCHEMA_NAME,
REFERENCED_ENTITY_NAME AS TABLE_NAME,
REFERENCED_MINOR_NAME AS REFERENCED_COLUMN,
IS_SELECTED IS_SELECTED,
IS_UPDATED IS_UPDATED,
IS_SELECT_ALL IS_SELECT_ALL
FROM
SYS.DM_SQL_REFERENCED_ENTITIES ('DBO.SP_GETALLADDRESS', 'OBJECT')

Manish Kaushik