.::: Create Store Procedure & Check Dependencies :::.

A. Sample Query 1 & Store Procedure 1
1. Sample 1
-- Query 1
select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;

2. Store Procedure 1

Create Procedure Barang_pasok

as
 begin
 select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from   barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and  s.KODE_SUPLIER=p.KODE_SUPLIER;

end

3. Run Query 1 & Store Procedure 1

exec Barang_pasok;

select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;

4. Check Dependency
SELECT distinct
            @@SERVERNAME as ServerDB,
            referenced_database_name,
            referenced_schema_name,
        --    b.type,
            b.parent_obj,
            referenced_id,
            referenced_entity_name,
            referenced_minor_name,
             referenced_class_desc,
            is_caller_dependent
         FROM
            sys.dm_sql_referenced_entities ('dbo.Barang_pasok','OBJECT') as a
            Inner join sysobjects as b
            on a.referenced_id=b.parent_obj
  

         
B. Sample Query 2 & Store Procedure 2
1. Sample 2
-- Query 2
select barang.KODE_BARANG as KD_BRG,barang.NAMA_BARANG, pembelian.TANGGAL_PEMBELIAN as TGL_BELI,pembelian.JUMLAH_PEMBELIAN as JML_BELI from pembelian left outer join barang on pembelian.KODE_BARANG=barang.KODE_BARANG;

2. Store Procedure 1

Create Procedure Barang_pembelian

as
 begin
 select barang.KODE_BARANG as KD_BRG,barang.NAMA_BARANG,  pembelian.TANGGAL_PEMBELIAN as TGL_BELI,pembelian.JUMLAH_PEMBELIAN as JML_BELI from pembelian left outer join barang on pembelian.KODE_BARANG=barang.KODE_BARANG;
end

3. Run Query 1 & Store Procedure 1

exec Barang_pembelian;

select barang.KODE_BARANG as KD_BRG,barang.NAMA_BARANG, pembelian.TANGGAL_PEMBELIAN as TGL_BELI,pembelian.JUMLAH_PEMBELIAN as JML_BELI from pembelian left outer join barang on pembelian.KODE_BARANG=barang.KODE_BARANG;

4. Check Dependency
SELECT distinct
            @@SERVERNAME as ServerDB,
            referenced_database_name,
            referenced_schema_name,
        --    b.type,
            b.parent_obj,
            referenced_id,
            referenced_entity_name,
            referenced_minor_name,
             referenced_class_desc,
            is_caller_dependent
         FROM
            sys.dm_sql_referenced_entities ('dbo.Barang_pembelian','OBJECT') as a
            Inner join sysobjects as b
            on a.referenced_id=b.parent_obj

5. Check Dependency other version for next

 

exec SPBarang_pembelian

-- command 1
select distinct referenced_id,referenced_entity_name,b.type,@@servername as ServerName,db_name() as DBName,@@SERVICENAME as ServiceName FROM  sys.dm_sql_referenced_entities ('dbo.SPBarang_pembelian','OBJECT') as a
Inner join sysobjects as b on a.referenced_entity_name=b.name
go

- command 1 example sheet "dbo.SPBarang_pembelian" >> implement1
select distinct referenced_id,referenced_entity_name,b.type  FROM
        sys.dm_sql_referenced_entities ('dbo.SPBarang_pembelian','OBJECT') as a
Inner join sysobjects as b
on a.referenced_entity_name=b.name
go

-- command 2 example sheet  for "depdent_objects" >> implement2
select distinct referenced_id,referenced_entity_name,b.type,referencing_minor_id,referenced_minor_name  FROM
        sys.dm_sql_referenced_entities ('dbo.SPBarang_pembelian','OBJECT') as a
Inner join sysobjects as b
on a.referenced_entity_name=b.name
go