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'

How to create derived Relation In MS Sqlserver

Sql Allows a subquery expression to be used in the from cluse. If we use such an expression then we must give the result relation a name, and we can rename the attributes, We do this renaming by useing the as clause. For example, consider the subquery....

 

select Employee1, qty from

(

select Employeeid ,AVG( SampleQty) from Table

group by EmployeeID

) as

 Result(Employee1, qty )

 

This subquery generates a relation consisting of the names of all employeeid and therir corresponding average sampleQty  . The subquery result is named result, with rhe attributes Employee1, qty.

How to get ms sqlserver relation name, relationship table name

SELECT




name, OBJECT_NAME(parent_object_id) 'ReferenceTO', OBJECT_NAME(referenced_object_id) 'ReferenceBy'

FROM




sys.foreign_keys


where OBJECT_NAME(referenced_object_id) like '%ABC%'


order by name

Database diagram support objects cannot be installed... access problem

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
Solution :

Well for a while I attempted few things and nothing worked. After that I carefully read the error and I realized that solution was proposed in the error only. I just have to read it carefully. Here are the steps I did to make this work.

-- Replace YourDatabaseName in following scriptALTER AUTHORIZATION ON
Syntax: DATABASE::YourDatabaseName TO sa

Implementation: if your database name is 'WorkShare'
then write the above syntax like as-

Syntax: DATABASE::WorkShareTO sa


GO

• Select your database >> Right Click >> Select Properties
• Select FILE in left side of page
• In the OWNER box, select button which has three dots (…) in it
• Now select user ‘sa’ or NT AUTHORITY\SYSTEM and click OK.

This should solve your problem.

Please note, I suggest you check your security policies before changing authorization. I did this to quickly solve my problem on my development server. If you are on production server, you may open yourself to potential security compromise.