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

Satyaprakash Samantaray
Feb 07, 2017
4.2k
0
2
Blog

Today, I will show you the below mentioned important steps

CREATE 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 RESULTS.
LIST OF TABLES USED IN A STORED PROCEDURE.
THEN CREATE DATABASE DIAGRAM.

STEP 1

CREATE ONE TABLE NAMED Supplier_Type_Master.

SQL syntax

USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Supplier_Type_Master](
[supp_type_id] [char](2) NOT NULL,
[supp_type] [varchar](30) NOT NULL,
CONSTRAINT [PK_Supplier_Type_Master] PRIMARY KEY CLUSTERED
(
[supp_type_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Design Output

INSERT data output

STEP 2

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

USE [MyDB]
GO
ALTER TABLE [dbo].[Supplier_Master] ADD CONSTRAINT [DF_Supplier_Master_user_delete_flag] DEFAULT ('N') FOR [user_delete_flag]
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

USE [MyDB]
GO
ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [FK_Supplier_Master_Supplier_Type_Master] FOREIGN KEY([supp_type_id])
REFERENCES [dbo].[Supplier_Type_Master] ([supp_type_id])
GO
ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [FK_Supplier_Master_Supplier_Type_Master]
GO

Finally, we create a table named Supplier_Master.

SQL syntax

USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Supplier_Master](
[party_id] [varchar](40) NOT NULL,
[supplier_name] [varchar](50) NOT NULL,
[supplier_creation_date] [datetime] NOT NULL,
[supp_type_id] [char](2) NOT NULL,
[insurance_details] [varchar](4000) NOT NULL,
[vendor_option_in] [varchar](15) NOT NULL,
[memo] [varchar](4000) NULL,
[file_name] [varchar](100) NULL,
[file_path] [varchar](250) NULL,
[time_stamp_inserted] [datetime] NOT NULL,
[time_stamp_modified] [datetime] NULL,
[time_stamp_deleted] [datetime] NULL,
[user_delete_flag] [char](1) NOT NULL CONSTRAINT [DF_Supplier_Master_user_delete_flag]DEFAULT ('N'),
[user_login_name] [varchar](100) NOT NULL,
CONSTRAINT [PK_Supplier_Master] PRIMARY KEY CLUSTERED
(
[party_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [FK_Supplier_Master_Supplier_Type_Master] FOREIGN KEY([supp_type_id])
REFERENCES [dbo].[Supplier_Type_Master] ([supp_type_id])
GO
ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [FK_Supplier_Master_Supplier_Type_Master]
GO

Design output

INSERT data output

Step 3

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

USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_DATABASE_DIAGRAM]
AS
BEGIN
SET NOCOUNT OFF; --SET NOCOUNT ON;
select sm.supplier_name , stm.supp_type from Supplier_Master Sm
inner join Supplier_Type_Master stm on sm.supp_type_id = stm.supp_type_id
END

STEP 4

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

SQL syntax

sp_helptext SP_DATABASE_DIAGRAM

Output

STEP 5

Execute the stored procedure to get the results.

SQL syntax

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

ALTER PROCEDURE [dbo].[SP_DATABASE_DIAGRAM]
AS
BEGIN
SET NOCOUNT OFF; --SET NOCOUNT ON;
select sm.supplier_name , stm.supp_type from Supplier_Master Sm
inner join Supplier_Type_Master stm on sm.supp_type_id = stm.supp_type_id
END
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

ALTER PROCEDURE [dbo].[SP_DATABASE_DIAGRAM]
AS
BEGIN
SET NOCOUNT ON; --SET NOCOUNT OFF;
select sm.supplier_name , stm.supp_type from Supplier_Master Sm
inner join Supplier_Type_Master stm on sm.supp_type_id = stm.supp_type_id
END
exec SP_DATABASE_DIAGRAM

Output

Step 8

List of tables used in a stored procedure.

SQL syntax

SELECT
NAME 'Table Names'
FROM SYSOBJECTS
WHERE ID IN ( SELECT SD.DEPID
FROM SYSOBJECTS SO,
SYSDEPENDS SD
WHERE SO.NAME = 'SP_DATABASE_DIAGRAM'
--Name Of Stored Procedure
AND SD.ID = SO.ID
)

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.