Likes (4)

Comment

Share

Steps to be Followed:

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 a database diagram

Step 1

Create a 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 

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

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 the "Show Relationship Labels" icon In SQL Server, it will show what kind of relationship is there and what the foreign key relationship name is.

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