Delete All Stored Procedure -from database in Sql server

 DECLARE @spname sysname;

DECLARE SPCursor CURSOR FOR
SELECT SCHEMA_NAME(schema_id) + '.' + name
FROM sys.objects
WHERE type = 'P';
OPEN SPCursor;
FETCH NEXT FROM SPCursor INTO @spname;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('DROP PROCEDURE ' + @spname);
FETCH NEXT FROM SPCursor INTO @spname;
END
CLOSE SPCursor;
DEALLOCATE SPCursor;

CREATE NEW USER AND GRANT PERMISSION for INSER/UPDATE/DELETE IN SQL SERVER

 Use YourDatabase;

GO

--- Create User

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'NewUserName')

BEGIN

    CREATE USER [NewAdminName] FOR LOGIN [NewAdminName]

    EXEC sp_addrolemember N'db_owner', N'NewUserName'

END;

GO

--database level permission

GRANT SELECT,UPDATE,INSERT,DELETE ON dbo.SampleDatabase TO user;

-- Schema level permission

GRANT SELECT,UPDATE,INSERT,DELETE ON SCHEMA::dbo TO user;

change table schema name in sql server

declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema   varchar(1000)

  set @oldschema = 'dbo'

  set @newschema = 'exe'

 while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)

  begin

      select @table = name from sys.tables 

      where object_id in(select min(object_id) from sys.tables where  schema_name(schema_id)  = @oldschema)

    set @sql = 'alter schema ' + @newschema + ' transfer ' + @oldschema + '.' + @table

   exec(@sql)

 end

Check Performance in sql server

SELECT TOP 10 st.text

               ,st.dbid

               ,st.objectid

               ,qs.total_worker_time

               ,qs.last_worker_time

               ,qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_worker_time DESC

Find Deadlock in sql server

The only other way I could suggest is digging through the information by using EXEC SP_LOCK (Soon to be deprecated), EXEC SP_WHO2 or the sys.dm_tran_locks table.

SELECT  L.request_session_id AS SPID, 

    DB_NAME(L.resource_database_id) AS DatabaseName,

    O.Name AS LockedObjectName, 

    P.object_id AS LockedObjectId, 

    L.resource_type AS LockedResource, 

    L.request_mode AS LockType,

    ST.text AS SqlStatementText,        

    ES.login_name AS LoginName,

    ES.host_name AS HostName,

    TST.is_user_transaction as IsUserTransaction,

    AT.name as TransactionName,

    CN.auth_scheme as AuthenticationMethod

FROM    sys.dm_tran_locks L

    JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id

    JOIN sys.objects O ON O.object_id = P.object_id

    JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id

    JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id

    JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id

    JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id

    CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST

WHERE   resource_database_id = db_id()

ORDER BY L.request_session_id

Get all column names from database table with specific schema in SQL Server

Many times there’s a requirement to get all columns for a particular table in database. Hence here is a SQL Query that does that.

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'Your Table Name'

ORDER BY ORDINAL_POSITION

How to Executing SQL Query in Entity Framework and Linq MVC C#

I will explain to you how we can use different types of SQL Query methods.

Introduction

 In this post, we will see how to execute native SQL queries, using DB Context.

 You can execute SQL queries using the following types of SQL Query methods. 

 1- SQL Query for a specific entity type.

 2- SQL Query for a primitive data type.

 3- SQL commands.

Let’s start.

SQL Query for a specific entity type

We can use SQLQuery() method to write SQL queries which return an entity object.

Example:

==============================================================

//DbContext  

DbPersonnesEntities db = new DbPersonnesEntities();  

var customerList = db.Customers.SqlQuery("Select * From Customers").ToList<Customers>();  

==============================================================

The code, shown above, selects all the data rows from customer's table. I would like to add that columns returned by SQLQuery must match the property of an entity type, otherwise it will throw an exception.

SQL Query for a primitive data type

In this type, the SQL Query() method will return any primitive types created using the SQL Query method.

Example:

========================================================

//DbContext  

DbPersonnesEntities db = new DbPersonnesEntities();  

int customerId = db.Database.SqlQuery<int>("Select customerId From Customers where customerName='MAHDI'").FirstOrDefault<int>(); 

========================================================

SQL commands

We can use ExcecuteSqlCommand() method to ensure the CRUD operation.

Example:

===========================================================

//DbContext  

DbPersonnesEntities db = new DbPersonnesEntities();  

int result = db.Database.ExecuteSqlCommand("delete from Customers where customerId = 100");  

===========================================================

As you can see, code displayed above will delete a customer that has 100 as customerId.