Find the Dependencies of a Table in SQL Server


In this article, we will show you how to find the Dependencies of a Table or column in SQL Server using system procedure. We can find dependency using system procedure called sp_depends or we can create a script to find dependency in SQL server



Find dependency using sp_depends

       The below system procedure will help you to find depending script of your table or column. I am not suggest to use system sp to find dependency, it will not display depending script, if we used the column in dynamic query.



use DataBaseName
GO
EXEC sp_depends @objname = 'first name' ;


Script to find dependency


 Below script will help you to find dependency in entire SQL server. Create the procedure any of your database and execute with column or table name as parameter, it will output depending script from all your data base exists in  SQL server.

 EXEC  Sp_depends_sqlFeathers @objectName = 'first name'


CREATE PROC Sp_depends_sqlFeathers
(
        @objectName VARCHAR(200) = ''
)
/**********************************************************************
Createdby                   Date                        
SQLFeathers                 23-10-2009   

EXEC  Sp_depends_sqlFeathers @objectName = 'first name'

************************************************************************/
AS
BEGIN
       DECLARE @db VARCHAR(30)
       DECLARE @id INT = 1
       DECLARE @cnt INT
       DECLARE @sqlqry VARCHAR(500)
       DECLARE @tblDBs TABLE(id INT IDENTITY, db VARCHAR(30))

       IF OBJECT_ID('tempdb.dbo.#skip', 'u') is not null
              DROP TABLE #skip
       CREATE  TABLE #skip (error VARCHAR(150))


       IF OBJECT_ID('tempdb.dbo.#tbsysc', 'u') is not null
              DROP TABLE #tbsysc
       CREATE  TABLE #tbsysc
       (
              [dbname] VARCHAR(50),
              [objname] VARCHAR(200),
              [schema name] VARCHAR(50),
              [Open Query gethelp] VARCHAR(100)
       )

       INSERT INTO @tblDbs
              SELECT name FROM sys.databases
              WHERE database_id>4

       SELECT @cnt = @@rowcount


       WHILE @id<=@cnt
       BEGIN
              SELECT @db = db
              FROM @tblDBs
              WHERE id = @id

              SET  @sqlqry =  'select distinct ''' + @db
                                + ''' as DB, s.name,m.name, '''+ @db
                                +'.' +'dbo.sp_helptext ['' + m.name + '''+'.'
   +''' + s.name '+ '+'']''
                             from ' +
                               @db + '..sysobjects s with(nolock)
                             left join ' +
                               @db + '..syscomments c with(nolock)
                               on s.id = c.id
                             left join
                               sys.schemas m with(nolock)
                               on s.uid = m.schema_id
                             where text like ''%' + @objectName + '%'''

              BEGIN TRY
                      INSERT INTO #tbsysc
                      EXEC (@sqlqry)
              END TRY
              BEGIN CATCH
                      INSERT INTO #skip(error)
                      SELECT @db + ' is skipped'
              END CATCH

              SET @id += 1
       END

       SELECT * FROM #tbsysc ORDER BY 1

       SELECT * FROM #skip ORDER BY 1
END

Load data to SQL server using python



    Loading of bulk data to SQL using pyodbc is very slow, To resolve this issue there is a new package introduced in python is called bcpy .

What is it?

This package is a wrapper for Microsoft's SQL Server bcp utility. Current database drivers available in Python are not fast enough for transferring millions of records .This is the fastest way of loading data from CSV to SQL server using python

1. Install the python package called bcpy by running below command.

pip install bcpy

2. Download and install the suitable ODBC driver from Microsoft site.

https://www.microsoft.com/en-us/download/details.aspx?id=56567

3. Download and install BCP Utility from Microsoft site.

https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017#T

The following example creates a DataFrame with 100 rows and 4 columns populated with random data and then it sends it to SQL Server.

import bcpy
import numpy as np
import pandas as pd

sql_Conn = {
'server': 'Server',
'database': 'Database',
'username': 'UserName',
'password': 'Password'
}

table_name = 'SampleTable'
df = pd.DataFrame(np.random.randint(-100, 100, size=(100, 4)),columns=list('ABCD'))
bdf = bcpy.DataFrame(df)
exec_sql = bcpy.SqlTable(sql_Conn, table=table_name)
bdf.to_sql(exec_sql)

Using Json In SQL Server 2016

      JSON (JavaScript Object Notation) is a lightweight data interchange format and it is set of data with key value pairs. This is awesome feature introduced in SQL 2016. Some built in function help to handle Json data in SQL server 2016. We can use Json data instead of table type in procedures to pass set of data.

Below are the list of built in function introduced in SQL 2016.

  • ISJSON
  • JSON_VALUE
  • JSON_QUERY
  • JSON_MODIFY
  • FOR JSON
  • OPENJSON

ISJSON – This will check inputted string is Json formatted or not,If it is Json return 1 else 0.

DECLARE @JSONData AS NVARCHAR(MAX)


SET @JSONData = N'{ "FirstName":"Michael", "LastName":"Jackson" }'

SELECT ISJSON(@JSONData) AS IsJson

JSON_VALUE – This will help to extract each column value from Json String, Or we can extract Json data like a table.

DECLARE @JSONData AS NVARCHAR(MAX)
SET @JSONData = N'{ 
    "CustInfo":{ 
        "FirstName":"
Michael",
        "LastName":"
Jackson",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"}, 
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"} 
        ]
    }
}'

SELECT
JSON_VALUE(@JSONData,'$.CustInfo.FirstName') AS FirstName,
JSON_VALUE(@JSONData,'$.CustInfo.LastName') AS LastName,
JSON_VALUE(@JSONData,'$.CustInfo.CustId') AS CustomerId,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[0].AddressType') AS AddressType,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[0].City') AS City,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[0].State') AS State,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[1].AddressType') AS AddressType2,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[1].City') AS City2,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[1].State') AS State2







JSON_QUERY – This is used to extract array of Json data or array of Json string.

DECLARE @JSONData AS NVARCHAR(MAX)

SET @JSONData = N'{ 

    "CustInfo":{ 
       
"FirstName":"Michael",
        "LastName":"
Jackson", 
        "CustId":"10002569", 
        "Addresses":[ 
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"}, 
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"} 
        ] 
    } 
}' 

SELECT JSON_QUERY(@JSONData,'$.CustInfo.Addresses')

UNION ALL

SELECT JSON_QUERY(@JSONData,'$.CustInfo.Addresses[1]')








JSON_MODIFY – This help to modify,insert,update or append value to Json string.

DECLARE @JSONData AS NVARCHAR(MAX)

SET @JSONData = N'{ 
    "CustInfo":{ 
       
"FirstName":"Michael",
        "LastName":"
Jackson", 
        "CustId":"10002569", 
        "Addresses":[ 
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"}, 
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"} 
        ] 
    } 
}'


/*Inserting Json*/
SET @JSONData = JSON_MODIFY(@JSONData,'$.CustInfo.Gender', 'Male')
PRINT @JSONData

/*Updating Json*/
SET @JSONData = JSON_MODIFY(@JSONData,'$.CustInfo.Gender', '1')
PRINT @JSONData

/*Delete Json*/
SET @JSONData = JSON_MODIFY(@JSONData,'$.CustInfo.Gender', NULL)
PRINT @JSONData

/*Multiple Field Update*/
SET @JSONData = JSON_MODIFY(JSON_MODIFY(@JSONData,'$.CustInfo.FirstName', 'Jack'),'$.CustInfo.LastName','Dani')
PRINT @JSONData

/*Append Json*/
SET @JSONData = JSON_MODIFY(@JSONData,'append $.CustInfo.ContactNo',JSON_QUERY('{"Mobile" :"989500000","LandLine" :"04950008"}'))

{

    "CustInfo":{
       "FirstName":"Michael",
        "LastName":"Jackson",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
    ,"Gender":"Male"}  Inserted
}
{

    "CustInfo":{
       "FirstName":"Michael",
        "LastName":"Jackson",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
    ,"Gender":"1"} Updated
}
{

    "CustInfo":{
       "FirstName":"Michael",
        "LastName":"Jackson",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
  Deleted    
  } 
}
{

    "CustInfo":{
       "FirstName":"Jack", Multi Update
        "LastName":"Dani",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
    }
}
{

    "CustInfo":{
       "FirstName":"Jack",
        "LastName":"Dani",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
    ,"ContactNo":[{"Mobile" :"989500000","LandLine" :"04950008"}]} Appended
}
 

FOR JSON – Function FOR JSON help to export SQL table data to Json format.Its similar to FOR XML function.

DECLARE @TempData AS Table(Name VARCHAR(10),Age INT)
INSERT INTO @TempData
SELECT 'A',10
UNION ALL
SELECT 'B',25
UNION ALL
SELECT 'C',25

SELECT *
FROM @TempData
FOR JSON PATH







OPENJSON – Using open Json we can extract Json data in schema format. Below example extract the Json data and convert it to table schema as like SQL table.


DECLARE @JSONData AS NVARCHAR(MAX)
SET @JSONData = N'{ 
       
"FirstName":"Michael",
        "LastName":"
Jackson",
 
        "CustId":"10002569", 
        "Addresses":[ 
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"}, 
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"} 
        ] 
    }' 



SELECT
   CustId,FirstName, LastName, AddressType, City, State
FROM OPENJSON(@JSONData)
WITH
(
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  CustId INT,
  Addresses NVARCHAR(MAX) as json
) AS Cust
CROSS APPLY OPENJSON(Cust.Addresses)
WITH
(
  AddressType INT,
  City VARCHAR(50),
  State VARCHAR(50)
) AD