Advertisements
Home > Denali, SQL Server > Table Attributes Dependencies in Store Procedures with SQL Server 2012

Table Attributes Dependencies in Store Procedures with SQL Server 2012

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

Advertisements
  1. Ashish Tiwari
    May 22, 2012 at 6:38 pm

    That’s something I was looking for. Very helpful feature.
    Thanks for sharing.
    Cheers.

  2. Manish Kaushik
    May 23, 2012 at 3:52 pm

    Thanks for visiting http://bi-bigdata.com and you are welcome Ashish.

    Thanks
    Manish

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: