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.
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.