Archive

Archive for the ‘MS Sql’ Category

SQL SERVER – How To Delete Duplicate Records – Based on Rows

August 11, 2015 Leave a comment

Heree is handy code I got from sqlauthority.com, for deleting the duplicate records from table.
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Where DuplicateColumn1,DuplicateColumn2 etc are the columns on which you are checking for duplicate constraints.

Categories: MS Sql, tricks

How to find a text inside SQL Server procedures / triggers?

October 8, 2014 Leave a comment

Hi,
Today I come across an change request at my recent project:
Request was to replace a table reference to new view created in all Procedures and functions.

At first glace, I just annoyed by the change, then mu colleague has suggested a way to search though all Procedures Views and functions,

I got details from Stackoverflow.com

Code for your reference:

DECLARE @Search varchar(255)
SET @Search='TheTableNameYouareSearching'

SELECT DISTINCT
o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE m.definition Like '%'+@Search+'%'
ORDER BY 2,1

You will get all listing.Really great…Saved lot of time.

SQL update query using joins

October 12, 2011 Leave a comment

Hi,
I am an Mysql programmer.
I need to update few table columns based on join condition of two SQL Server tables.
The query is as follows:
update c
set c.name = a.name,
c.category = a.class,
c.sub_category = a.schclass
from TableA as c inner join Table B as a on c.code = a.code

Just putting it for reference.

Thanx

Categories: ASP.net C#, MS Sql