Create Database Diagram Using Stored Procedure And SQL Server 2012 In Your Real Time Project

Today, I will show you the below mentioned important steps

  1. CREATE TABLES.
  2. CREATE STORED PROCEDURE USING INNER JOIN BETWEEN TWO TABLES.
  3. FIND OUT STORED PROCEDURE SYNTAX AS TEXT USING SQL QUERY.
  4. EXECUTE STORED PROCEDURE TO GET RESULTS.
  5. LIST OF TABLES USED IN A STORED PROCEDURE.
  6. THEN CREATE DATABASE DIAGRAM.

STEP 1

CREATE ONE TABLE NAMED Supplier_Type_Master.

SQL syntax

  1. USE [MyDB]  
  2. GO  
  3. SET ANSI_NULLS ON  
  4. GO  
  5.   
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8.   
  9. SET ANSI_PADDING ON  
  10. GO  
  11.   
  12. CREATE TABLE [dbo].[Supplier_Type_Master](  
  13.     [supp_type_id] [char](2) NOT NULL,  
  14.     [supp_type] [varchar](30) NOT NULL,  
  15.  CONSTRAINT [PK_Supplier_Type_Master] PRIMARY KEY CLUSTERED   
  16. (  
  17.     [supp_type_id] ASC  
  18. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  19. ON [PRIMARY]  
  20.   
  21. GO  
  22.   
  23. SET ANSI_PADDING OFF  
  24. GO  


Design Output


INSERT data output


STEP 2

  1. CREATE SECOND TABLE NAMED Supplier_Master  

Before creating this table, I have to create a constraint.

Named DF_Supplier_Master_user_delete_flag.

Note

The constraints are used on a column or a table such that wrong data can't be inserted into the tables. The constraints helps for data integrity and accuracy in the table.

SQL syntax 

  1. USE [MyDB]  
  2.   
  3. GO  
  4.   
  5. ALTER TABLE [dbo].[Supplier_Master] ADD CONSTRAINT [DF_Supplier_Master_user_delete_flag] DEFAULT ('N'FOR [user_delete_flag]  
  6.   
  7. GO  

Now, create one foreign key relationship between Supplier_master and supplier_type_master.

Note

The foreign key is a table that uniquely identifies a row of other table. The foreign key is defined in a second table but it refers to the primary key in the first table.

SQL syntax 

  1. USE [MyDB]  
  2.   
  3. GO  
  4.   
  5. ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [FK_Supplier_Master_Supplier_Type_Master] FOREIGN KEY([supp_type_id])  
  6.   
  7. REFERENCES [dbo].[Supplier_Type_Master] ([supp_type_id])  
  8.   
  9. GO  
  10.   
  11. ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [FK_Supplier_Master_Supplier_Type_Master]  
  12.   
  13. GO   

Finally, we create a table named Supplier_Master.

SQL syntax 

  1. USE [MyDB]  
  2.   
  3. GO  
  4.   
  5. SET ANSI_NULLS ON  
  6.   
  7. GO  
  8.   
  9. SET QUOTED_IDENTIFIER ON  
  10.   
  11. GO  
  12.   
  13. SET ANSI_PADDING ON  
  14.   
  15. GO  
  16.   
  17. CREATE TABLE [dbo].[Supplier_Master](  
  18.   
  19. [party_id] [varchar](40) NOT NULL,  
  20.   
  21. [supplier_name] [varchar](50) NOT NULL,  
  22.   
  23. [supplier_creation_date] [datetime] NOT NULL,  
  24.   
  25. [supp_type_id] [char](2) NOT NULL,  
  26.   
  27. [insurance_details] [varchar](4000) NOT NULL,  
  28.   
  29. [vendor_option_in] [varchar](15) NOT NULL,  
  30.   
  31. [memo] [varchar](4000) NULL,  
  32.   
  33. [file_name] [varchar](100) NULL,  
  34.   
  35. [file_path] [varchar](250) NULL,  
  36.   
  37. [time_stamp_inserted] [datetime] NOT NULL,  
  38.   
  39. [time_stamp_modified] [datetime] NULL,  
  40.   
  41. [time_stamp_deleted] [datetime] NULL,  
  42.   
  43. [user_delete_flag] [char](1) NOT NULL CONSTRAINT [DF_Supplier_Master_user_delete_flag]DEFAULT ('N'),  
  44.   
  45. [user_login_name] [varchar](100) NOT NULL,  
  46.   
  47. CONSTRAINT [PK_Supplier_Master] PRIMARY KEY CLUSTERED  
  48.   
  49. (  
  50.   
  51. [party_id] ASC  
  52.   
  53. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  54.   
  55. ON [PRIMARY]  
  56.   
  57. GO  
  58.   
  59. SET ANSI_PADDING OFF  
  60.   
  61. GO  
  62.   
  63. ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [FK_Supplier_Master_Supplier_Type_Master] FOREIGN KEY([supp_type_id])  
  64.   
  65. REFERENCES [dbo].[Supplier_Type_Master] ([supp_type_id])  
  66.   
  67. GO  
  68.   
  69. ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [FK_Supplier_Master_Supplier_Type_Master]  
  70.   
  71. GO   

Design output 


INSERT data output


Step 3

Create stored procedure using inner join between the two tables mentioned above.

  1. USE [MyDB]  
  2.   
  3. GO  
  4.   
  5. SET ANSI_NULLS ON  
  6.   
  7. GO  
  8.   
  9. SET QUOTED_IDENTIFIER ON  
  10.   
  11. GO  
  12.   
  13. ALTER PROCEDURE [dbo].[SP_DATABASE_DIAGRAM]  
  14.   
  15. AS  
  16.   
  17. BEGIN  
  18.   
  19. SET NOCOUNT OFF--SET NOCOUNT ON;  
  20.   
  21. select sm.supplier_name , stm.supp_type from Supplier_Master Sm  
  22.   
  23. inner join Supplier_Type_Master stm on sm.supp_type_id = stm.supp_type_id  
  24.   
  25. END  

STEP 4

Find out the stored procedure syntax as text, using SQL query.

SQL syntax

  1. sp_helptext SP_DATABASE_DIAGRAM  

Output


STEP 5

Execute the stored procedure to get the results.

SQL syntax

  1. exec SP_DATABASE_DIAGRA  

Output


STEP 6

Difference Between NOCOUNT ON and NOCOUNT OFF Used In Stored Procedure.

If we put NOCOUNT OFF used in stored procedure, then after the execution of the stored procedure; the message tab in SQL Server will show (1429 row(s) affected).

SQL syntax 

  1. ALTER PROCEDURE [dbo].[SP_DATABASE_DIAGRAM]  
  2.   
  3. AS  
  4.   
  5. BEGIN  
  6.   
  7. SET NOCOUNT OFF--SET NOCOUNT ON;  
  8.   
  9. select sm.supplier_name , stm.supp_type from Supplier_Master Sm  
  10.   
  11. inner join Supplier_Type_Master stm on sm.supp_type_id = stm.supp_type_id  
  12.   
  13. END  
  14.   
  15. exec SP_DATABASE_DIAGRAM   

Output


STEP 7

If we put NOCOUNT ON used In stored procedure, then after the execution of the stored procedure, the Message tab in SQL Server will show the command(s) completed successfully.

SQL syntax 

  1. ALTER PROCEDURE [dbo].[SP_DATABASE_DIAGRAM]  
  2.   
  3. AS  
  4.   
  5. BEGIN  
  6.   
  7. SET NOCOUNT ON--SET NOCOUNT OFF;  
  8.   
  9. select sm.supplier_name , stm.supp_type from Supplier_Master Sm  
  10.   
  11. inner join Supplier_Type_Master stm on sm.supp_type_id = stm.supp_type_id  
  12.   
  13. END  
  14.   
  15. exec SP_DATABASE_DIAGRAM  

Output


Step 8

List of tables used in a stored procedure.

SQL syntax

  1. SELECT  
  2.   
  3. NAME 'Table Names'  
  4.   
  5. FROM SYSOBJECTS  
  6.   
  7. WHERE ID IN ( SELECT SD.DEPID  
  8.   
  9. FROM SYSOBJECTS SO,  
  10.   
  11. SYSDEPENDS SD  
  12.   
  13. WHERE SO.NAME = 'SP_DATABASE_DIAGRAM'  
  14.   
  15. --Name Of Stored Procedure  
  16.   
  17. AND SD.ID = SO.ID  
  18.   
  19. )  

Result

It will show two table names used in this stored procedure i.e. Supplier_Master & Supplier_Type_Master.

Output


Step 9

Steps to create a database diagram.

Right click on the database diagram and new database diagram.


Add the tables used in stored procedure to know the relationship between them.


After adding these two tables, it will show the foreign key relationship between the two tables.
By clicking show relationship labels icon In SQL Server, it will show what kind of relationship is there and what is the foreign key relationship name.


Step 10

You can name your own created database diagram.

After expanding database diagrams, it will show your own created database diagram.


Summary

Create tables.

Make constraint and foreign key relationship between two tables.

Create stored procedure using inner join between two tables.

Find out stored procedure syntax as text, using SQL query.

Execute stored procedure to get the results.

Difference between NOCOUNT ON and NOCOUNT OFF

List of tables used in a stored procedure.

Create database diagram.

Find your own created database diagram.

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now