SQL SERVER : How to create cursor

What is Cursor?

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

In order to work with a cursor we need to perform some steps in the following order:

• Declare cursor
• Open cursor
• Fetch row from the cursor
• Process fetched row
• Close cursor
• Deallocate cursor

Syntax:

declare @procName varchar(500)
declare cur cursor 

for select [name] from sys.objects where  type = 'P' 
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
    print @procName
    exec('drop procedure ' + @procName)
    fetch next from cur into @procName
end
close cur
deallocate cur

SQL SERVER - Delete all sp from a database

Problem: Sometimes we need to recreate sp in database in this case we need to remove all current sp from database.
Solution:
declare @procName varchar(500)
declare cur cursor 

for select [name] from sys.objects where  type = 'P' 
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
    print @procName
    exec('drop procedure ' + @procName)
    fetch next from cur into @procName
end
close cur
deallocate cur

SQL SERVER – Disable All the Foreign Key Constraint in Database – Enable All the Foreign Key Constraint in Database

Is there any way I can disable all the Constraint temporarily and load the random data, test my system and later delete all the inserted data and enable the Constraint back?”

This is indeed a great question, I often come across this question again and again. Here is a quick script I have written in my early career which I still use it when I need to do something similar.

-- Disable all the constraint in databaseEXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Enable all the constraint in databaseEXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Remember above script when executed right away enable or disable constraints so be extremely careful to execute on production server.

There is one more thing, when you have disabled the constraint, you can delete the data from the table but if you attempt to truncate the table, it will still give you an error. If you need to truncate the table you will have to actually drop all the constraints. Do you use similar script in your environment? If yes, please leave a comment along with the script and I will post it on blog with due credit.

Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.

----Option 1SELECT DISTINCT so.nameFROM syscomments scINNER JOIN sysobjects so ON sc.id=so.idWHERE sc.TEXT LIKE ' %tablename% '----Option 2SELECT DISTINCT o.name, o.xtypeFROM syscomments cINNER JOIN sysobjects o ON c.id=o.idWHERE c.TEXT LIKE '%tablename%'

How to find out a table primary key or other key is cluster or nonclustered index

Problem:

Sometimes we need to know key of a table is cluster or noncluster.

Syntax:

-- Check IndexesSELECT OBJECT_NAME(OBJECT_ID) TableObject,[name] IndexName,[Type_Desc]FROM sys.indexesWHERE OBJECT_NAME(OBJECT_ID) =

'YourTableName' 

 

Implemenatation:

 

Suppose you table name is StudentInfo

-- Check IndexesSELECT OBJECT_NAME(OBJECT_ID) TableObject,[name] IndexName,[Type_Desc]FROM sys.indexesWHERE OBJECT_NAME(OBJECT_ID) =

'StudentInfo' 

 

 

Find Column Used in Stored Procedure –

 Solution:

Syntax:

SELECT obj.Name as SPName, sc.TEXT as  SPTextFROM sys.syscomments scINNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_IDWHERE sc.TEXT LIKE '%' + 'Name Your Column Here' + '%'AND TYPE = 'P'

Implementation:

Suppose your column name is "Year"

SELECT obj.Name as SPName, sc.TEXT as SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'Year' + '%'
AND TYPE = 'P'