Query to Find Duplicate Rows from TABLE - SQL SERVER

SELECT  Office_Name , DUPCOUNT FROM (
SELECT  OFFICE_NAME , ROW_NUMBER() OVER (PARTITION BY OFFICE_NAME ORDER BY OFFICE_NAME) AS DUPCOUNT FROM Mast_Office ) OBJ
WHERE OBJ.DUPCOUNT > 1

Finding tables in DataBase which does not have Primary Key SQL Server

USE MyDataBase
GO
SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)FROM SYS.INDEXES
WHERE INDEX_ID = 0 AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE]
GO

Why SET NOCOUNT ON in Stored Procedure in SQL Server

When a stored procedure executes, the database engine must keep sending messages back and forth between the server and the client. These relentless interactions create overhead on the processing and in most cases are not necessary. To avoid them, after the AS keyword, add a SET NOCOUNT ON expression before starting the body of the stored procedure. The formula to follow is:

CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
AS
SET NOCOUNT ON
Body of the Procedure

Here is an example:

CREATE PROCEDURE Registration.GetIdentification
AS
SET NOCOUNT ON
BEGIN
    SELECT FirstName, LastName, DateOfBirth, Gender
    FROM Registration.Students
END
GO

Although you can create and use a stored procedure without specifying a schema, it is recommended that you always use a schema. In fact, you should always create your own schema in your database and create your stored procedure in it.