Mustafa EL-Masry Script, SQl server Administration

the use of “SET NOCOUNT ON;” in SQL Server?


This statement is used to stop the message that shows the count of the number of rows affected by the SQL statement written in the stored procedure or directly SQL Statement

When it is ON - the number of affected rows will not be returned 
When it is OFF - the number of affected rows will be returned

Example 


USE AdventureWorks2012;
GO
SET NOCOUNT OFF;
GO
-- Display the count message. The number of affected rows will be returned 
SELECT TOP(5)LastName
FROM Person.Person
WHERE LastName LIKE 'A%';
GO
-- Disable the Count massage Then Number of affected rows will not be returned.
SET NOCOUNT ON;
GO
SELECT TOP(5) LastName
FROM Person.Person
WHERE LastName LIKE 'A%';
GO
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

Thanks for All

Eng. Mustafa Elmasry

MSSQL DBA


			

			
					


		
			
 





	

		

					

			 Mustafa EL-Masry Script					


How to Disable or Enable All constraint  Key in Database
		

		

			

Disable all the constraint in database




EXEC sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”


Enable all the constraint in database




EXEC sp_msforeachtable “ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”

		


		
			
 





	

		

					

			 Mustafa EL-Masry Script					


Create comma-separated list
		

		

			

1- Create Table Emp


SET ANSI_NULLSON


GO


SET QUOTED_IDENTIFIERON


GO


CREATE TABLE [dbo].[Emp](


[Emp_Code] [int] NULL,


[Emp_Name] [nvarchar] (50)NULL)


ON [PRIMARY]


GO


2-  Insert Data


Insert into Emp values (1,‘Mostafa’),(1,‘Moahmed’),(2,‘Elsayed’),(2,‘Elmasry’)


GO


3- Create comma-separated list


WITH CTE AS (


SELECT DISTINCTEmp_Code FROM Emp )


SELECT Emp_Code, CommaList =STUFF((SELECT‘,’+ Emp_name FROM Emp


WHERE Emp_Code = CTE.Emp_Code ORDERBY Emp_name


FORXMLPATH(”),TYPE).value(‘.’,‘varchar(max)’),1,1,”)FROM CTE 


ORDERBY Emp_Code;


 


 


 

		


		
			
 





	

		

					

			 Mustafa EL-Masry Script					


Drop All Databases in SQL Server
		

		

			

EXEC sp_MSforeachdb ‘

IF DB_ID(”?”) > 4

BEGIN

ALTER DATABASE ? SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE ?

END’

		


		
			
 





	

		

					

			 Mustafa EL-Masry Script					


Reseting Identity Values for All Tables
		

		

			

USE DATABASE;

EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1

DBCC CHECKIDENT (''?'', RESEED, 1)'

GO


IF you want to make it Cursor Follw me on this Link


https://mostafaelmasry.wordpress.com/2011/11/30/reset-the-identity-coulmn/